Increase negative % or decrease positive %

[Jtucker10278]

This is more of Math question, and reporting standard question. but I will probably need some formula help with the correct answer.

if last Qtr I produce 800 Widgets and this Qtr I produce 1250 Widgets Increase = (new - original) / Original or (1250 - 800) / 800= 56% increase
but if last Qtr I produced 1250 Widgets and this Qtr I produced 800 Decrease = (Original - new) / Original or (1250 - 800) / 1250 = 36% decrease
but in both cases it was only 450 Widgets different.

Now lets say I am looking at how many Minutes it takes to crate widget.

Last qtr it took 22 min per widget and this qtr it is taking 13 min per widget it is a decrease in min but a big increase in productivity
the formula (22-13)/22 = 40% but thats really right because it was an increase in the direction we wanted the number to go.
AND the formula (22-13)/13 = 69% but I don't know if that is accurate either

I could invert the formula to show -40% or -69% (but then I'm up to like 3 if statement so i can have single formula that calculates % for all scenarios

Andy why, why are all sales and productivity reports done this way showin increasing or decreasing % instead of change %

Ie 2 | b-a | / (b+a) x 100

thanks for any thoughts or input

 

[Snakehips]

@Jtucker10278 I'm neither a mathematician nor an analyst so I could well be talking rubbish.
No one size fits all!
I think it's a question of semantics and requires that the formula is customised to ensure that positive and negative of pure mathematics reflects the required direction of 'Improvement' or 'Deterioration'.

In the example below there are subtle differences, eg > changes to <.

AutoTraining.xlsm
	A	B	C	D
1	Output Widgets Per Period
2	Original	New	Variance
3	800	1250	56% Increase
4	1250	800	36% Decrease
5
6	Production Rate Minutes Per Widget
7	Original	New	Variance Widgets/Min	Variance Min/Widget
8	22	13	41% Increase	69% Decrease
9	13	22	69% Decrease	41% Increase
10
Sheet6

Cell Formulas
Range	Formula
C3:C4	C3	=ROUND(ABS((B3-A3)/A3*100),0)&"% "&IF(B3>A3,"Increase","Decrease")
C8:C9	C8	=ROUND(ABS((B8-A8)/A8*100),0)&"% "&IF(B8<A8,"Increase","Decrease")
D8:D9	D8	=ROUND(ABS(((1/B8)-(1/A8))/(1/A8)*100),0)&"% "&IF(B8>A8,"Increase","Decrease")

HTH