Percentage Difference and Decreasing Percentage

[rprague]

New Picture (14).jpg

Hi,

I am working on a table and have a couple of questions. As this is a long list of Products with a number of products not sold in either 2013 or 2014 thus being "0'' is this the right formula to use? Also is there a way to reduce the percentage from -267% to the actual -72.7% that it should actual be?

Many thanks in advance.

[Special-K]

Percentage reduction
Try

=SIGN(E3-B3)*(100-E3/B3*100)

[Richard Buttrey]

Hi

Use instead
=IF(OR(B3=0,E3=0);"",(E3-B3)/B3)


if you used

=IFERROR((E3-B3)/B3;,"")
and e3 was blank you'd return -100% which whilst mathematically correct may not be what you want to see.

[Ace_XL]

Your denominator should reflect the base year. Hence

=IFERROR((E3-B3)/B3;"NEW")

[rprague]

Many thanks

With =IF(OR(B3=0,E3=0);"",(E3-B3)/B3) I get the following:

SOLD DIFFERENCE
-0,727272727
-0,8125
-0,714285714
-1

But can I reduce this to just 72.7% and also the ''melon'' to say NEW?

[Richard Buttrey]

You also need to format the cell as a %

[rprague]

You also need to format the cell as a %

I have formatted the cell to Percentage with 0 decimal places, but still get -72.72727273. Is there a way to limit the amount of decimals apart from in number formatting?

[rprague]

You also need to format the cell as a %

I have formatted the cell to Percentage with 0 decimal places, but still get -72.72727273. Is there a way to limit the amount of decimals apart from in number formatting?