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
640
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
236
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Forum statistics

Threads
1,136,953
Messages
5,678,749
Members
419,782
Latest member
gc75150

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
Top