Formula in cell to result in a 1-5 score via Vlookup

What I would like to do relates to column J of the ‘Media Summary Sheet’ tab, as well as the ‘Tables – Gross Profit’ tab.

Essentially, this doc examines two metrics to judge how effective our media advertising is. One is based on store manager feedback, and another is comparing the average daily sales to previous periods. We’d like to introduce a third, which examines if stores make more gross profit when we run media, as compared to when we don’t. For example, it’s safe to say that we perform more sales when we run TV or radio, but when you factor in the cost of the TV or radio, we might actually be making more gross profit not running any advertising at all.

The formula in column J of the ‘Media Summary Sheet’ tab is as follows: the amount of money we have made in the period where we ran our media (column H), minus the amount we have spent on that media (column G), minus the amount we made in a set period preceding the media. Hence, if the number is in the positive, it means we made more gross profit in the period where we ran media, as opposed to when we didn’t! This is what we’re hoping for as this suggests the media has been effective.

Where I need help is assigning a 1-5 point score depending on this gross profit difference (scale below). So because in row 2, we made more gross profit in the period where we ran media (by $3k), this would suggest the media we chose is effective and hence, it’s awarded 4 out of 5. I believe a Vlookup table is the way to do this as this is what was done with column F of the ‘Media Summary Sheet’ Tab (turning a formula result into a 1-5 score). I’ve made the Vlookup table, now I just need someone to hook it up as I don’t know how!

The Vlookup table is located in the ‘Tables – Gross Profit’ tab. Also, once this is hooked up – similar to the ‘Tables – Avg Daily Sales’ tab, can we have each score contribute to the average of the appropriate cell in the ‘Tables – Gross Profit’ tab depending on the region, medium TV/radio, and channel/station?

Thanks!

5k profit plus = 5 points
3- 5k profit plus = 4
1-3k profit = 3
0-1k loss or profit = 2.5
-1 to -2k loss = 2
-2k loss and worse = 1

Hello Jamie
You can use the hard coded Lookup function as suggested by vlady, but if you want to use the Lookup table you've created then take a look at the attached reply. I've put in the formulas as you've used them before on the 'Media Summary' sheet and the 'Tables-Gross Profit' Sheet.

Hope I've understood you correctly.
DBY

Thanks vlady. That's definitely offered up another way to do it!

DBY, I hope this site is paying you for the amazing help you're offering up to new users! Again, your help is much appreciated and thanks for going above and beyond. It really does mean a lot.

Cheers!