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

1. ## 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. ## 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. ## 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.

- 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. ## 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. ## 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. ## 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. ## 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. ## 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
---

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

#### 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