percentage difference between positive and negative numbers

wyndland

New Member
Joined
Feb 15, 2018
Messages
3
Hello,
Please refer to the table below

I have been trying to create a formula to show the percentage increase between the 'without' row and the 'with' row.
I've tried ((without - with)/with) and also included the ABS command to try and solve the negative number issue but the results are less than accurate (see bottom row)

Any help would be most gratefully received

Regards
Glen

ReadWriteRithmatic
Total
with-0.530.900.16
without
-1.58-0.08-0.72
26.27%66.54%-54.58%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

So, which is your starting value (with or without)?
What exactly is the "negative number" issue?
What does a negative represent, and how should it be handled?
What are your expected results?
 
Last edited:

wyndland

New Member
Joined
Feb 15, 2018
Messages
3
Welcome to the Board!

So, which is your starting value (with or without)?
What exactly is the "negative number" issue?
What does a negative represent, and how should it be handled?
What are your expected results?

I'm calculating the reading writing and maths scores for schools who receive music tuition and those that don't
I averaged the totals of 85 schools using the AVERAGE function (some schools were positive and some negative values) for all 3 types (reading, writing, maths). My aim is to show how better the scores are when music is involved. In all 3 instances the scores are better, BUT, some scores feature negative numbers (as in my table). I might have expected an increase of around 200% (if my maths is right!) in the reading total: from -1.58 (without) to .53 (with)

Thanks for getting back so quickly
G
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It really depends on what those numbers actually represent.

For example, you wouldn't say that 4 degrees Fahrenheit is twice as warm as 2 degrees Fahrenheit!
Just because the number is twice as big doesn't necessarily mean that the actually heat generated is twice as much.
So we need to understand exactly what these numbers represent and how they are derived for them to have any sort of true meaning.
 

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
The value being positive or negative has no affect on the logic to calculate % difference

=(A-B)/A or =(B-A)/A ... % of A difference... you could use either one one would be +, the other - to describe the direction of the difference
=(B-A)/B or =(A-B)/B ... % of B difference

which one you choose is what you are trying to describe
 

golfboy7

New Member
Joined
Feb 12, 2018
Messages
5

ADVERTISEMENT

Hi wyndland,

I hope others on the board will keep me honest.

I believe the formula should be the change between the old number and the new number, divided by the starting number.

For example, if i had 10 apples, and gained 3 more apples, the increase would be the change (+3) divided by the starting number (10) for a % change of 30% increase.

Since you have negative numbers it 'seems' more confusing than it really is. Here's what I came up with:

ABCD
1ReadWriteArithmatic
2w/out music-1.58-.08-.72
3w music-.53.9.16
4
5% change66%1225%122%

<tbody>
</tbody>


The formula I wrote for this (assuming it begins in the top left at cell A1):

=ABS((B3-B2)/B2)


That said, sometimes showing a % change is not the best way to communicate the impact. In this case, it may be better to show it as a raw increase, "Reading scores show an increase of 105 basis points when music is included..."

Other veterans please keep me honest.

gb7



Forgot to add that the starting value is 'without'

thanks Joe4

G
 

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
Arithmatic would make a great name for a calculator company in the '50s lol

you mean arithmetic ;)
 

golfboy7

New Member
Joined
Feb 12, 2018
Messages
5
No no, I mean Eurithmetric! :LOL: Which would make a great name for a band in the 70s-80s with heavy use of synthesizers.

I'll get that updated. Nice catch!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top