nested if statement returns #value error
 

I have a formula that returns a value based on the value of another
cell on the same worksheet. For example, cell D1 has a value of
97.00%. The formula is

=if(d1<.93999, 0, if(d1<.94999, .35, if(d1<.95999,.55,
if(d1<.96999,.75,if(d1=.97,.90,"")))))

The formula should return a value of .9 in the cell containing the
formula. I am getting the #Value error though and can't figure out
why. If I change the last if statement to read if(d1<=1 then the
formula returns the correct value. The syntax may be off slightly
because Excel is not correcting me but I think you have the idea. Any
ideas?

nested if statement returns #value error
 

Your formula works perfectly. I copied/pasted your formula into a
spreadesheet, 97% in D1 and formula cell reads .9 Sorry

ed

wrote:
I have a formula that returns a value based on the value of another
cell on the same worksheet. For example, cell D1 has a value of
97.00%. The formula is

=if(d1<.93999, 0, if(d1<.94999, .35, if(d1<.95999,.55,
if(d1<.96999,.75,if(d1=.97,.90,"")))))

The formula should return a value of .9 in the cell containing the
formula. I am getting the #Value error though and can't figure out
why. If I change the last if statement to read if(d1<=1 then the
formula returns the correct value. The syntax may be off slightly
because Excel is not correcting me but I think you have the idea. Any
ideas?

nested if statement returns #value error
 

Wrote:
I have a formula that returns a value based on the value of another
cell on the same worksheet. For example, cell D1 has a value of
97.00%. The formula is

=if(d1<.93999, 0, if(d1<.94999, .35, if(d1<.95999,.55,
if(d1<.96999,.75,if(d1=.97,.90,"")))))

The formula should return a value of .9 in the cell containing the
formula. I am getting the #Value error though and can't figure out
why. If I change the last if statement to read if(d1<=1 then the
formula returns the correct value. The syntax may be off slightly
because Excel is not correcting me but I think you have the idea. Any
ideas?Hi aksaunders,
Your Formula works for me with .97 in D1 it returns .9
'=IF(D1<0.93999,0,IF(D1<0.94999,0.35,IF(D1<0.95999 ,0.55,
IF(D1<0.96999,0.75,IF(D1=0.97,0.9,"")))))

The formula you have in your post has spaces in it.
I don't know what that would do, but i took them out.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=570222

nested if statement returns #value error
 

The spaces don't seem to make any differrence.

ed

Desert Piranha wrote:
Wrote:
I have a formula that returns a value based on the value of another
cell on the same worksheet. For example, cell D1 has a value of
97.00%. The formula is

=if(d1<.93999, 0, if(d1<.94999, .35, if(d1<.95999,.55,
if(d1<.96999,.75,if(d1=.97,.90,"")))))

The formula should return a value of .9 in the cell containing the
formula. I am getting the #Value error though and can't figure out
why. If I change the last if statement to read if(d1<=1 then the
formula returns the correct value. The syntax may be off slightly
because Excel is not correcting me but I think you have the idea. Any
ideas?Hi aksaunders,
Your Formula works for me with .97 in D1 it returns .9
'=IF(D1<0.93999,0,IF(D1<0.94999,0.35,IF(D1<0.95999 ,0.55,
IF(D1<0.96999,0.75,IF(D1=0.97,0.9,"")))))

The formula you have in your post has spaces in it.
I don't know what that would do, but i took them out.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=570222