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.

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

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

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

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

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

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