Conditional Average

Have you ever found yourself needing to average 1,000 lines of data on a spreadsheet with a ton of zeros across a number of ranges such as price gap ranges? Maybe you are trying to figure out volume responses based on certain price thresholds or maybe the average lift at various promotion prices.

You can do this with some new functions in Excel 2007 or by simply creating a custom function in earlier versions of Excel (I will provide the syntax later in this article). Suppose you start out with 1,000 lines of data in a spreadsheet that looks like the example in Exhibit 1:

The left column is the price gap between your brand and the Private Label and the right column is the volume you achieved at that gap for that week. We want to know on average what the volume expectations might be at various gap levels i.e. 10¢, 20¢, 30¢ etc.

You can't do a simple average but there is a way to accomplish this calculation using Excel 2007's Sumifs and Countifs. If we want to know the average sales for all instances when the price gap is between 10¢ and 20¢ the formula will look like this:

=Sumifs(Volume, Pr Gap, ">=.10",Pr Gap,"<.20")/Countifs(Pr Gap, ">=.10",Pr Gap,"<.20")
=Sumifs($d$6:$d$1006,$c$6:$c$1006,">=.10",$c$6:$c$1006,"<.20")/Contifs($c$6:$c$1006,">=.10",$c$6:$c$1006,"<.20")

Exhibit 1
This formula could be copied and you would simply adjust the bottom and top ranges of the price gap for each gap threshold i.e. 20¢ to 30¢, 30¢ to 40¢ etc.

If you are still in Excel 2003 or earlier versions you might want to try the following custom function syntax (CountBetween and SumBetween) entered in the Excel Visual Basic editor; simply cut and paste the following sentences into your Visual Basic editor:

Function COUNTBETWEEN(rng, num1, num2)
COUNTBETWEEN = Application.CountIf(rng, "<=" & num2) - Application.CountIf(rng, "<" & num1)
End Function

Function SUMBETWEEN(rng, num1, num2, rng2)
SUMBETWEEN = Application.SumIf(rng, "<=" & num2, rng2) - Application.SumIf(rng, "<" & num1, rng2)
End Function

Then you can enter the following formula in any cell in the Excel worksheet:

=sumbetween($AS$6:$AS$109,.10,.20,$AT$6:$AT$109)/countbetween($AS$6:$AS$109,.10,.20)

The resulting matrix will provide you with an understanding of potential volume responses to a range of price gaps:

Insight, Information & Consulting Services, Inc. is a consulting company dedicated to providing data visualization tools to clients in the Consumer Goods industry. Please visit our website at www.insightinformation.net or call Rick Pensa at 770-425-4243.

Comments

[tom gruca]

Thanks for this trick for the conditional averages. I have an additional question, how do you compute lift from these data?