The difference displayed as a %

[djt]

Hi there,

I have been playing about with this for a while to no avail.

Basically I have 2 numbers in 2 columns and in a 3rd I would like to display the difference as a + / - %

So for example:

Column 1 = 4.00

Column 2 = 3.00

4.00 is 25% higher than 3.00

but if they were the other way around

3.00 is 25% less than 4.00

so if 4.00 were first it would be +25%

and if 3.00 were first -25%

Any ideas????

File attached.

[teylyn]

Hi,

4.00 is 25% more than 3.00, but 3.00 is 33% less than 4.00, not 25%!!!

try

=1-(D5/C5)

format as %

[DonkeyOte]

To generate your +/- 25 then

=(1-(MIN(C5:D5)/MAX(C5:D5)))*SIGN(D5-C5)

However I would argue that 4 is 33.3% higher than 3: (4-3)/3 and 3 is 25% lower than 4: (3-4)/4 ... and that would be:

=(D5/C5)-1

[djt]

Thanks for the quick response.

I may be confused or just being confusing with my explanation, either way I have the equation I was looking for. Thanks.

[Richard Buttrey]

Hi,

That's fine if you are quite sure that the numbers are what you or others are expecting. However I would double check, particularly given your thread title.

What the others have said here is what most businesses would expect when expressing an increase from 3 to 4, or a reduction from 4 to 3. i.e. +33% and -25%.

Rgds