Value Error (nested IF)

[Median]

Hi guys,

Just wondered why I'm getting a value error in M9.

Any questions feel free to ask

Thanks

[MarvinP]

Hi Median,

I think you have too many arguments in your first IF statement. Try this instead.

=IF(L9<>"",((K9/H9)*100-100),IF(H9=K9,(((H9-0.02)/H9)*100-100),0))

Using the Formula Tab -> Evaluation tool and stepping through your formula gives "=0,0" which Excel doesn't know how to deal with, so you get a #Value.

[Median]

Appreciated Marvin, however, 1 issue is that M9 is blank when H9 = K9.

Ideally what I'm looking for: if H9 = K9, I want excel to minus 0.02, giving a result of -£0.89 (instead of being blank)

[finalazy]

try this way instead..

=IF(H9=K9,((H9-0.02)/H9)*100-100,IF(L9<>"",(K9/H9)*100-100,0))

[Median]

Ty Final, works a charm

Really appreciated guys

[finalazy]

You are welcome. And thanks for the rep!