10-08-2019 02:10 AM
10-08-2019 02:34 AM
Delta function in excel, basically tests whether two values are equal. It returns 1 if both values are equal and returns 0 otherwise.
If you want to just compare values in A1 & A3 you can use =DELTA(A3,A1) or elaborate your requirements with example in excel sheet.
10-08-2019 02:42 AM
A1 would be the current total
A2 the previous total
A3 is the change from the previous value
A4 (the percentage change) is the delta divided by the previous total.
The calculation is correct but for very small or negative prior values A2, it is just not very useful. To suppress the error you could use a formula
= IF( A2>0, A3/A2, "not available" )
10-08-2019 07:37 AM
IMHO, it depends on business logic behind. If I had -$20 loss on previous year and have $100 this one, difference is $120 and formula shows "not available" for % difference.
Another point, if both A1 and A2 are zeros, usually % is shown as zero.
Without knowing the content formula is a bit abstract.
10-08-2019 09:57 AM
The advantage of %change is that it is scale invariant; it does not matter whether the denomination is $ or $M. Your delta of $120 may or may not be significant; a change from $1,000,000 to $1,000,120 is a drop in the ocean.
On the other hand, in your example where the base value is -$20 (a loss) the percentage change is -600%. To be honest, that means very little to me and I would sooner view the actual amounts.