Create own Excel formula

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.

 

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.

Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
29,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 €

Write a comment

Comments: 0