+ Reply to Thread
Results 1 to 8 of 8

How do you change a NULL value to a Zero when using =MID function?

  1. #1
    Derek
    Guest

    How do you change a NULL value to a Zero when using =MID function?

    I am using the MID function (=MID($N2,2,1)) to split a two digit number into
    two different cells and then summing them (i.e. 10 into two cells totaling 1,
    or 23 totaling 5). There isn't always a two digit number to split into two
    cells, so I get a #VALUE instead. That response carries over into following
    formula. I would like to know if there is a way to make the #VALUE become a
    zero.

  2. #2
    Max
    Guest

    Re: How do you change a NULL value to a Zero when using =MID function?

    "Derek" wrote:
    > I am using the MID function (=MID($N2,2,1)) to split a two digit number into
    > two different cells and then summing them (i.e. 10 into two cells totaling 1,
    > or 23 totaling 5). There isn't always a two digit number to split into two
    > cells, so I get a #VALUE instead. That response carries over into following
    > formula. I would like to know if there is a way to make the #VALUE become a
    > zero.


    One angle to it ..
    assuming you're using in Q2: =SUM(O2:P2)
    where O2:P2 contains your MID formulas
    try it in Q2 as: =SUMIF(O2:P2,"<>#VALUE!")
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

  3. #3
    A V Ramana Murthy
    Guest

    Re: How do you change a NULL value to a Zero when using =MID function?

    You can check for error at the time of splitting itself, by using ISERROR
    formula.

    =IF(ISERROR(your MID formula),0,(your MID formula))

    - Murthy


    "Max" <demechanik@yahoo.com> wrote in message
    news:38D638CE-D4E6-4F7C-A2EC-5381E1FAC3A0@microsoft.com...
    > "Derek" wrote:
    >> I am using the MID function (=MID($N2,2,1)) to split a two digit number
    >> into
    >> two different cells and then summing them (i.e. 10 into two cells
    >> totaling 1,
    >> or 23 totaling 5). There isn't always a two digit number to split into
    >> two
    >> cells, so I get a #VALUE instead. That response carries over into
    >> following
    >> formula. I would like to know if there is a way to make the #VALUE
    >> become a
    >> zero.

    >
    > One angle to it ..
    > assuming you're using in Q2: =SUM(O2:P2)
    > where O2:P2 contains your MID formulas
    > try it in Q2 as: =SUMIF(O2:P2,"<>#VALUE!")
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---




  4. #4
    Derek
    Guest

    Re: How do you change a NULL value to a Zero when using =MID funct

    I attempted both ways and came up with nothing unfortunately. Here are a
    couple of numbers I'm working with, and their corresponding formulas:

    # MID Fx MID Fx Total
    10 1 0 0
    12 1 2 2
    4 4 0
    6 6

    =G2*2 =MID($N2,1,1) =MID($N2,2,1) =SUMIF(O2:P2,"<>#VALUE!",O2:P2)
    =G3*2 =MID($N3,1,1) =MID($N3,2,1) =IF(ISERROR(MID($N3,1,1)),0,(MID($N3,2,1)))
    =G9*2 =MID($N9,1,1) =MID($N9,2,1) =SUMIF(O9:P9,"<>#VALUE!",O9:P9)
    =G10*2 =MID($N10,1,1) =MID($N10,2,1) =IF(ISERROR(MID($N10,1,1)),0,(MID($N10,2,1)))


    The sum of the # in the first row is 1, and the second is 3; and the
    following two rows should be same as the original number. If I'm not clear
    on something please let me know.

    Derek

  5. #5
    Max
    Guest

    Re: How do you change a NULL value to a Zero when using =MID funct

    Sorry, think I missed a clarification on the formulas in O2:P2
    (add a zero to the MID formulas to coerce the text to a number)

    Try it as
    In O2: =MID($N2,1,1)+0
    In P2: =MID($N2,2,1)+0
    Then in Q2: =SUMIF(O2:P2,"<>#VALUE!")
    Select O2:Q2, copy down
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Derek" wrote:
    > I attempted both ways and came up with nothing unfortunately. Here are a
    > couple of numbers I'm working with, and their corresponding formulas:
    >
    > # MID Fx MID Fx Total
    > 10 1 0 0
    > 12 1 2 2
    > 4 4 0
    > 6 6
    >
    > =G2*2 =MID($N2,1,1) =MID($N2,2,1) =SUMIF(O2:P2,"<>#VALUE!",O2:P2)
    > =G3*2 =MID($N3,1,1) =MID($N3,2,1) =IF(ISERROR(MID($N3,1,1)),0,(MID($N3,2,1)))
    > =G9*2 =MID($N9,1,1) =MID($N9,2,1) =SUMIF(O9:P9,"<>#VALUE!",O9:P9)
    > =G10*2 =MID($N10,1,1) =MID($N10,2,1) =IF(ISERROR(MID($N10,1,1)),0,(MID($N10,2,1)))
    >
    >
    > The sum of the # in the first row is 1, and the second is 3; and the
    > following two rows should be same as the original number. If I'm not clear
    > on something please let me know.
    >
    > Derek


  6. #6
    Max
    Guest

    Re: How do you change a NULL value to a Zero when using =MID funct

    Sorry, think I missed a clarification on the formulas in O2:P2
    (add a zero to the MID formulas to coerce the text to a number)

    Try it as
    In O2: =MID($N2,1,1)+0
    In P2: =MID($N2,2,1)+0
    Then in Q2: =SUMIF(O2:P2,"<>#VALUE!")
    Select O2:Q2, copy down
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Derek" wrote:
    > I attempted both ways and came up with nothing unfortunately. Here are a
    > couple of numbers I'm working with, and their corresponding formulas:
    >
    > # MID Fx MID Fx Total
    > 10 1 0 0
    > 12 1 2 2
    > 4 4 0
    > 6 6
    >
    > =G2*2 =MID($N2,1,1) =MID($N2,2,1) =SUMIF(O2:P2,"<>#VALUE!",O2:P2)
    > =G3*2 =MID($N3,1,1) =MID($N3,2,1) =IF(ISERROR(MID($N3,1,1)),0,(MID($N3,2,1)))
    > =G9*2 =MID($N9,1,1) =MID($N9,2,1) =SUMIF(O9:P9,"<>#VALUE!",O9:P9)
    > =G10*2 =MID($N10,1,1) =MID($N10,2,1) =IF(ISERROR(MID($N10,1,1)),0,(MID($N10,2,1)))
    >
    >
    > The sum of the # in the first row is 1, and the second is 3; and the
    > following two rows should be same as the original number. If I'm not clear
    > on something please let me know.
    >
    > Derek


  7. #7
    Derek
    Guest

    Re: How do you change a NULL value to a Zero when using =MID funct

    THANK YOU!!! Now it works correctly.

    "Max" wrote:

    > Sorry, think I missed a clarification on the formulas in O2:P2
    > (add a zero to the MID formulas to coerce the text to a number)
    >
    > Try it as
    > In O2: =MID($N2,1,1)+0
    > In P2: =MID($N2,2,1)+0
    > Then in Q2: =SUMIF(O2:P2,"<>#VALUE!")
    > Select O2:Q2, copy down
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Derek" wrote:
    > > I attempted both ways and came up with nothing unfortunately. Here are a
    > > couple of numbers I'm working with, and their corresponding formulas:
    > >
    > > # MID Fx MID Fx Total
    > > 10 1 0 0
    > > 12 1 2 2
    > > 4 4 0
    > > 6 6
    > >
    > > =G2*2 =MID($N2,1,1) =MID($N2,2,1) =SUMIF(O2:P2,"<>#VALUE!",O2:P2)
    > > =G3*2 =MID($N3,1,1) =MID($N3,2,1) =IF(ISERROR(MID($N3,1,1)),0,(MID($N3,2,1)))
    > > =G9*2 =MID($N9,1,1) =MID($N9,2,1) =SUMIF(O9:P9,"<>#VALUE!",O9:P9)
    > > =G10*2 =MID($N10,1,1) =MID($N10,2,1) =IF(ISERROR(MID($N10,1,1)),0,(MID($N10,2,1)))
    > >
    > >
    > > The sum of the # in the first row is 1, and the second is 3; and the
    > > following two rows should be same as the original number. If I'm not clear
    > > on something please let me know.
    > >
    > > Derek


  8. #8
    Max
    Guest

    Re: How do you change a NULL value to a Zero when using =MID funct

    "Derek" wrote:
    > THANK YOU!!! Now it works correctly.


    Glad that nailed it for you
    Thanks for the callback ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1