SUM or IF? Performance and Readability
I'm interested to see which is the better performer and also, subjectively, which function is more 'readable': SUM or IF, in the following scenario...
Consider that you are building a model for an international retailer. This retailer is setting up stores in different countries. When setting up a store in the first month, set up costs will be incurred. These are one-off costs that apply to one month only, and vary on the country.
You want to give users the ability to tweak the setup costs and setup month. This could be achieved quite simply using the following:
- A dashboard containing;
- Input assumptions (setup period and cost)
- P&L output
Dashboard showing inputs and P&L
Here is a view of the IF formula*:
And here is a view of the sum formula:
*The IF formula should be reversed as the most common scenario (and therefore earliest exit) is that the current period is not the setup period.
Note that the two formulae are producing the same result. I am mostly interested in which formula is more efficient, and also which formula people think is more understandable.
Off the top of my head some here are some arguments in favour of each:
- Gives the ability to add multiple conditions in the future.
- Technically follows the 'natural language' of the process, being if it's the first month the store attracts setup costs.
- Formula shows the what is pulling through before anything else (Setup Input.Setup Cost).
- No fiddling with early exit on IFs.
I'm keen to see the opinion of others on this topic!