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

[RockandGrohl]

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:

Product	Features	Ratio
Apple	100	0.56
Banana	26	0.40
Grape	4	2.50
Watermelon	250	0.40
Orange	100	1.10
Tomato	20	0.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:

Product	Features	Ratio	Points
Apple	100	0.56	24
Banana	26	0.40	25
Grape	4	2.50	20
Watermelon	250	0.40	28
Orange	100	1.10	50
Tomato (lol)	20	0.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!

 

[JamesCanale]

Banana's don't look as good as you have them.

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G	H	I	J
1					Weight:	100%
2	Product	Features	Ratio	Points		Log Features %ile	Ratio %ile	blended with weight	Overall %ile	Points
3	Apple	100	0.56	24		78%	20%	33%	73%	23
4	Banana	26	0.4	25		45%	14%	20%	25%	-25
5	Grape	4	2.5	20		0%	100%	33%	75%	25
6	Watermelon	250	0.4	28		100%	14%	38%	92%	42
7	Orange	100	1.1	50		78%	43%	40%	100%	50
8	Tomato (lol)	20	0.06	-25		39%	0%	13%	0%	-50
Sheet31

Cell Formulas
Range	Formula
F3:F8	F3	=(LOG10(B3)-LOG10(MIN(B$3:B$8)))/(LOG10(MAX(B$3:B$8))-LOG10(MIN(B$3:B$8)))
G3:G8	G3	=(C3-MIN(C$3:C$8))/(MAX(C$3:C$8)-MIN(C$3:C$8))
H3:H8	H3	=(F3*$F$1+G3)/(2+$F$1)
I3:I8	I3	=(H3-MIN($H$3:$H$8))/(MAX($H$3:$H$8)-MIN($H$3:$H$8))
J3:J8	J3	=-50+100*I3