Getting a #VALUE! error---Need help
 

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

Getting a #VALUE! error---Need help
 

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

"mozingob" wrote:

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

Getting a #VALUE! error---Need help
 

opps, use this

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


"Eduardo" wrote:

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

"mozingob" wrote:

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

Getting a #VALUE! error---Need help
 

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

???

--
Biff
Microsoft Excel MVP


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

Getting a #VALUE! error---Need help
 

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)


--
Biff
Microsoft Excel MVP


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

???

--
Biff
Microsoft Excel MVP


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

Getting a #VALUE! error---Need help
 

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.
--
David Biddulph

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

"mozingob" wrote:

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