Theoretical Mathematical quandary - Determining and allocating points based on a relationship between two KPI's - sounds like fun? Let's go.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi all,


I currently have a system I built that determines suitability of products to advertise based on a shedload of different KPI's, but there's an important relationship between two KPI's that I would like to rank.

Those are "Passengers per feature" and "number of features"

For instance, if a product, Apple, has been featured 100 times and received 56 total sales of passengers (pax) it would have a pax/feat ratio of 0.56

If we look at the following table you can see some example numbers I've pulled off the top of my head:

ProductFeaturesRatio
Apple1000.56
Banana260.40
Grape42.50
Watermelon2500.40
Orange1001.10
Tomato200.06


So looking about, a clear winner here is Orange - 1.1 ratio with just 100 features. Tomato has had 20 features yet only secured 0.06 ratio, against Banana which had a few more features but a significantly higher ratio.


This is the kind of logic I need transposed into a mathematical equation. That's what I'm struggling with - an equation for the relationship between features and ratio to determine "success" or not.


Realistically, I'd distribute the scoring point something like this:


ProductFeaturesRatioPoints
Apple1000.5624
Banana260.4025
Grape42.5020
Watermelon2500.4028
Orange1001.1050
Tomato (lol)200.06-25

So let's consider a few things here:


  • Apple, Banana and Watermelon have similar point scores. The logic for this is that they are all tested (they've got more than a handful of features)
  • Grape has by far the best ratio, but it's only been featured 4 times, therefore its score is lower.
  • Tomato has been featured enough times but with a really low ratio it actually deserves a lot less points.
  • And orange gets the most points because it has a really high ratio AND a really high amount of features.


I hope you understand what I'm getting at?

Additionally, I don't want any points to exceed a lower and upper boundary of -50 to 50.


What I need is some kind of formula I can enter that examines the relationship between features and pax/feature and outputs points based on this relationship. Am I barking up the wrong tree here? Cheers!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Banana's don't look as good as you have them.
MrExcelPlayground2.xlsm
ABCDEFGHIJ
1Weight:100%
2ProductFeaturesRatioPointsLog Features %ileRatio %ileblended with weightOverall %ilePoints
3Apple1000.562478%20%33%73%23
4Banana260.42545%14%20%25%-25
5Grape42.5200%100%33%75%25
6Watermelon2500.428100%14%38%92%42
7Orange1001.15078%43%40%100%50
8Tomato (lol)200.06-2539%0%13%0%-50
Sheet31
Cell Formulas
RangeFormula
F3:F8F3=(LOG10(B3)-LOG10(MIN(B$3:B$8)))/(LOG10(MAX(B$3:B$8))-LOG10(MIN(B$3:B$8)))
G3:G8G3=(C3-MIN(C$3:C$8))/(MAX(C$3:C$8)-MIN(C$3:C$8))
H3:H8H3=(F3*$F$1+G3)/(2+$F$1)
I3:I8I3=(H3-MIN($H$3:$H$8))/(MAX($H$3:$H$8)-MIN($H$3:$H$8))
J3:J8J3=-50+100*I3
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top