Getting a #VALUE! error---Need help

[mozingob]

I get a #VALUE! error in L1 using this formula if H1 is blank. Any
suggestions?
=(J1*0.01*(IF(K1="",TODAY(),K1)-IF(H1="",E1,H1))/365+G1)

E F G H I J K L
1/28/2009 210.00 1.17 8/20/2009 40.00 250.00 10/1/2009 1.46


Bonnie Mozingo

 

[Eduardo]

Hi,
=if(H1="","",=(J1*0.01*(IF(K1="",TODAY(),K1)-IF(H1="",E1,H1))/365+G1))

 

[Eduardo]

opps, use this

=if(H1="","",(J1*0.01*(IF(K1="",TODAY(),K1)-IF(H1="",E1,H1))/365+G1))

 

[T. Valko]

I get a #VALUE! error in L1 using this formula if H1 is blank.

If H1 is an empty cell I get a result of 2.85 (rounded).

???

 

[T. Valko]

P.S. Could be that you may think H1 is "blank" but there may be some kind of
unseen whitespace character in there.

Try this:

=(J1*0.01*(IF(K1="",TODAY(),K1)-IF(COUNT(H1),H1,E1))/365+G1)

 

[David Biddulph]

Perhaps without the second = sign, Eduardo?
But it shouldn't be using H1 as a value if it's blank, as the only place
where H1 is used in the original formula was IF(H1="",E1,H1), so I'm not
convinced that your extra H1="" test will help, and in any case it wouldn't
give the output which the OP was presumably looking for, using E1 instead of
H1.

My guess would be that H1 isn't blank, but perhaps contains something like a
space.
Perhaps then change
=(J1*0.01*(IF(K1="",TODAY(),K1)-IF(H1="",E1,H1))/365+G1)
to
=(J1*0.01*(IF(K1="",TODAY(),K1)-IF(ISNUMBER(H1),H1,E1))/365+G1)
and maybe a similar change if K1 might be non-numeric.