Explanatory Formulas

[Steffen Thomsen]

Requirements:

- Basic knowledge of vba
- Use of tables/named ranges

When working with workbooks on and off, sometimes months between revisits to the workbook it can be hard to remember the data structure and formula set up.
This is how I work around this problem and ensure that I within a few minutes will have an overview of the calculations done.
In this example I will show how a formula for calculating bonuses can be simplified.

Formula appearance without table or UDF

=IF(SUM(C2/D2)<=1;0;IF(SUM(C2/D2)>=3;SUM((C2-D2)*0,03);IF(AND(SUM(C2/D2)>1;SUM(C2/D2)<3)=TRUE;SUM((C2-D2)*0,02);1)))

The logic in this formula can be hard to extract at first glance.

Formula appearance with table and no UDF

=IF(SUM([@[Yearly sales]]/[@Salary])<=1;0;IF(SUM([@[Yearly sales]]/[@Salary])>=3;SUM(([@[Yearly sales]]-[@Salary])*0,03);IF(AND(SUM([@[Yearly sales]]/[@Salary])>1;SUM([@[Yearly sales]]/[@Salary])<3)=TRUE;SUM(([@[Yearly sales]]-[@Salary])*0,02);1)))

With tables it just got a bit easier to read as you are informed of the cells used in the calculation

Formula appearance with table and UDF

User defined function inserted in a module:

' I will rather have long and descriptive function titles than short and undescriptive titles
Function calculate_salary_to_sale_ratio_and_return_bonus(yearlySales As Double, salary As Double) As Double

Dim salary_to_sale_ratio As Double
Dim bonus_factor As Double
Dim return_bonus As Double

salary_to_sale_ratio = yearlySales / salary

Select Case salary_to_sale_ratio
    Case 1 To 3
        bonus_factor = 0.02
    Case Is > 3
        bonus_factor = 0.03
    Case Else
        bonus_factor = 0#
End Select

return_bonus = (yearlySales - salary) * bonus_factor

calculate_salary_to_sale_ratio_and_return_bonus = return_bonus

End Function

Use of the function

=calculate_salary_to_sale_ratio_and_return_bonus([@[Yearly sales]];[@Salary])

This tells you exactly what is happening at a first glance, and you can allways go deeper into the code to read the specifics.

Attached is a file demonstrating all three scenarios