In Excel you can quickly create very complex formulas. If you want to simplify your formula, you can also write your own function in VBA. Here is an example for calculating the percentage variance from the previous year.
1 2 3 4 5
Option Explicit Public Function prozent(source As Double, target As Double) prozent = Application.WorksheetFunction.IfError(IIf(target < 0,(source - target) / -target, (source - target) / target), 0) End Function
As you can see, a very simple formula. For this purpose the sample as an Excel formula.
=IFERROR(IF(targetCell < 0,(sourceCell - targetCell) / - targetCell,(sourceCell - targetCell / targetCell), 0)
= (ACT) 215 - (PY) 204 / (PY) 204 = 0,055 = 5,5 %
As you can see the own formula is more readable and can be easily combined with another formula.
These Posts might also be interesting:
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
Write a comment