Excel is king. It’s probably used by a lot of the stakeholders in your company. It’s commonly used for quick analysis. It has a rather low barrier of entry when compared to coding. With so many people using it with so many reports, you’re almost certain to stumble across a bug.
I am going to cover two common errors and a lesser occurring example. This article can either help you avoid these situations or can help you offer guidance to those using reports that have numbers that don’t check out with a source of truth.
You throw on those gloves that allow you to move through the footage to identify the source of the problem. You have all of your monitors full of evidence. You’re on a mission!
Someone has pointed out that they’re coming up with different values for ‘average_$_per_cust’ than what they’re seeing in other places.
This report has taken a column that has already calculated the ‘average_$_per_customer’ by store by month and it’s now taking the average for each month. Averaging averages is a no-no! Note the difference in the pivot table above when using the sum of the ‘sales’ / sum of the ‘cust’, a created formula.
This might not look like a huge difference, but when it comes to some important decisions that are made, this could be the end of a program that is actually doing just well enough to survive.
Speaking of averages, the tricky part about them is that the value of the denominator can make a pretty sizable difference in results. In this example, Store 10 had not opened up last year, so no sales were associated with it. In our left column, we put a 0 for sales. In our right column, we don’t have a value. Using a 0 will add a 1 to the denominator for each observed instance.
$905 (we’ll say that’s in thousands) split between 10 stores is very different compared to the 9 stores actually needed for the calculation.
We’re going to change the scenery and bring in our old friend: the V-Lookup. This scenario is sort of an oddball. I’m wanting to show it just in case you run into something similar. First, take a look at the two columns near the middle of our output.
In this scenario, we’re using our V-Lookup to bring in the value for ‘cust’, far right, to match up alongside ‘sales’. We don’t have a unique identifier to join on, so one was created. The column…
Continue reading: https://towardsdatascience.com/excel-2-common-bugs-1-rare-1cbe2dc5faef?source=rss—-7f60cf5620c9—4