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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### JamesCanale

##### Active Member
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

Replies
1
Views
219

1,141,062
Messages
5,704,057
Members
421,325
Latest member
tapete86

### 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.

### Which adblocker are you using?

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

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