Mar 19, 2013

In Search Of The Formula

No doubt, one of the most appealing features in Excel is the formulas. Clever usage of it could achieve excellent results in performing complex calculations, processing data, extracting and displaying information  and much more. Тhe majority of Excel users have not stepped beyond some simple operations. Мost people approach formulas as a daunting and complex task. Users get fixated on searching the formula and keep trying to find it while the real power to solve the problem with their own logical thinking. Here's my two cents to help you think of formulas as easy and fun.

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.

No comments:

Post a Comment