Formula-Percentage Error

[ecrolley]

I am having an issue with the following cells returning inccorect percentages. Can someone help me figure out what I'm missing? It works in some examples, but not all.
H23: =IF(F22=0,"",(F23-F22)/F22)
H24: =IF(F23=0,"",(F24-F23)/F23)

I have attached the spreadsheet in question. Examples are on separate tabs.

Thanks!

[martindwilson]

f22 does not = 0 it is 0.20

[Richard Buttrey]

Hi,

H23 is working correctly.

I think you've missed the point that the divisor F22 has a value of 0.2 and not zero as you probably think since the formatting is set to zero decimal places.

[TheExcelphile]

Hi!

In your sample sheet, even though F22 is showing as zero, it isn't really zero. Its value is 0.20, rounded to zero.
Use the formula =IF(ROUND(F22,0)=0,"",(F23-F22)/F22), and you won't face this problem.

Don't forget to click the little star to the left of this post if you feel I helped!

[Pete_UK]

I suggest you change the formula in F22 to this:

=ROUND(D22*G22,0)

then copy it down, to ensure that values showing as zero really are zero (and not 0.2, as in the case you presented us with).

Hope this helps.

Pete

[Pete_UK]

Wow !! 4 posts, all saying the same thing within 3 minutes of each other.

Pete

[ecrolley]

Blonde moment....thank you all!!!