Some formulas are complex due to a complex question. Other formulas are complex due to inefficient data layout.

Formula Auditing Maestro

Auditing formulas is an essential skill to have for Data Analysts but with some practice you’ll become a Maestro!  In this video I audit this formula:

=VLOOKUP(B1,INDIRECT(VLOOKUP(A1,Data1!A6:B9,2,FALSE)),2,FALSE)

It is an unusual formula to say the least but it’s good practice as there are a lot of strange formulas floating around out there.

 

Watch My YouTube Video

 

Optimal Data Layout = Easier Formula

Auditing this formula involves focusing on the various parts of the formula. However, the more important thing to focus on is the data layout. If we invest the time to re-structure the data from several mini ranges into one normalized table then we’ll save a lot of time and frustration later on.

It’s always easier to analyze data that is laid out properly. What does this mean? Well, the mini ranges that rank the most popular sports per country are really part of the same data set and should be listed together in a single table.

 

See The Big Picture First

The next time that you are about to write formulas look at your data layout and see if there’s anything that you can do (i.e. normalize, combine, etc.) to make life easier on yourself. Learn to see the big picture (data layout) and then the syntax details of individual formulas.

 

Post a comment