Excel percentage calculation using negative figure

[RachieD]

Hi - I am working out the year-on-year increase/decrease in a set of figures and I am a bit stupid!! The following calculation is giving me a problem because it involves a negative:

A1 B1 C1
Actual FY05 Actual FY06 =b1/a1*100
-7,192 29,850

C1 = -415% when this should be a positive figure

Can anyone help?

Thanks!

 

[GlennUK]

Your figures appear to be movements year on year, and not actuals. Is that the case?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

=if(A1<0,((B1+abs(A1))/abs(A1))*100,(b1/a1)*100)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

I'm with Glen. I bet -7192 is the difference between '05 & '04.

Also, if you are working out percentages, consider not using '*100', but rather formatting the cell as a percentage.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

Are you sure of your TERMS and FIGURES?

A percentage is relaive to a BASE AMOUNT.

Lets assume that your FIGURES are correct, that FY05 has a negative value.

The PERCENTAGE will either be relative to FY05 or FY06.

If its calculated, relative to FY05 then the percent INCREASE is
[tt]
=(B1-A1)/ABS(A1)
[/tt]
If its calculated, relative to FY06 then the percent INCREASE is
[tt]
=(B1-A1)/ABS(B1)
[/tt]
In either case it's a percent increase, althought in the latter case, the rate is smaller.

However, If the -7,192 is a calculated DIFFERENCE, your formula is ABSOLUTELY INCORRECT. Rather it would be
[tt]
=A1/Abs(B1)
[/tt]
and it would be a DECREASE or negative.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[TonyJollans]

Never mind Excel for a minute.

It makes no numerical sense at all to talk about a percentage when one value is negative and the other positive. It doesn't matter in the slightest what the numbers themselves mean (whether they are year-on-year differences or actual profits and losses or something else).

You can talk in terms of a turnaround or something like that but a gain is not a percentage of a loss and no formula, nor any amount of jiggery-pokery, will change that.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

I would have to disagree there Tony

I work in an industry where we have lots of business units. Sometimes those business units incur exceptional charges and as such can have negative profitability 1 year followed by positive results the next so having a -ve and a +ve to compare is something I do often

To me, what you are trying to show is how much change there has been between last year and this year. In this case, the amount of change is 29850 + 7192. The "percentage change" then shows how large that change is compared with the base amount - whether or not the base amount is negative makes no difference - the % result is used to show that either a large change has been made or a small change has happened.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

I used to work with a bunch of business analysts, and had this argument with them many times. They wanted to calculate things the same way as you do, but found that figures could be hard to interpret that way, as anything that can easily be negative can easily be close to zero and give enormous %ages.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RachieD]

Thanks very much everyone - your help was invaluable...!!
Both xlbo and skipvought came up with the answer I needed - Skip it was calculated relative to FY05.

Thanks again!!

Cheers
RachieD

 

[xlbo]

Glenn - accept the point but that's why we always provide the actual value for comparison as well ;-)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Yep Geoff, providing the actual is one way to make sure people don't jump to the wrong conclusions

( whooo hooo, 51000% rise showing on my reports right now .... darn it, went from 10p to 51 quid now that I look at the figures )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

LOL - I can just imagine some of our field sales guys doing exactly that !!!

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

This might be why I don't work in finance

I maintain it doesn't make numerical sense whatever financiers do.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

<snigger>I work with accountants a lot of the time. Nothing makes numerical sense !

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question