Simplify! Many of us forget this simple advice that we apply successfully in all our other activities. So stop searching for the formula and start thinking! What would you do if there is no Excel? No matter how complex the formula, there are simple steps in it. Follow these simple steps:
- Start with a clear statement what is the required outcome of the formula. It helps not to stare at the spreadsheet while you do that.
- Break the calculation in simple steps. You can use words, e.g. "find customer ID for customer John", "calculate the sum of sales for him", "divide the sales to the total sales figure". You can do that in cells or keep in mind.
- Write the simple formulas in different cells. You could put everything together if you feel confident of course
- Test different cases to see the output and fix the formulas
- Combine the formulas from the cells into one cell if this is what you want
Most of the complex formulas are a combination of simple and well-known formulas for look-up, sum, average, if, etc. I know it is simple and that is why I am surprised when people forget that.
Some Excel spreadsheets and models require really complex calculations involving long many steps. Dealing with that is similar to the steps described above. Our main tool is our brain and Excel is just helping us. If the calculation process well thought and clear then writing the proper formula is just a small and easy to deal with detail.