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.

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

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" wrote in message
...
"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
---

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

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

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

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