I need to examine the relationship between two parameters and allocate points to them.

[RockandGrohl]

Hi, a repost with slightly better phrasing after thinking about it over the weekend.

I have a list of products and there are two parameters - features and passenger/features (sales) meaning if we have featured a product 100 times and it has received 32 sales, it has a pax/feature of 0.32

Now that the basics are out of the way, I want to categorize each tour into one of ten hypothetical buckets and apply points to them.

Let's assume that if a product is featured 1-19 times it has low advertisement. 20-49 is medium and 50+ is high advertisement.
Let's also assume that 0.2 pax/feat is average performance, therefore 0.15 and lower is low performance and 0.25 and above is high performance.

Finally, I have a system set up that allocates advertisements based on point-scoring and this is one part of the overall formula that determines suitability. For this purpose, I'd like to limit all points to within a -50 to +50 lower and upper boundary. To be clear, the greater points a product has, the greater chance of being selected for advertisement.

Category	Features	Pax/Feature Ratio	Description	Result	Points (representative examples)
0 Ads	0	0	This product has the potential to sell well	Cautiously allocate a few points	10
Low Advertisement, low performance	5	0.07	Situation could improve with future advertisements, but not looking good.	Don't really add points	0
Low Advertisement, medium performance	7	0.15	Situation could improve with future advertisements, but not so far so good.	Give some points to encourage selection	20
Low Advertisement, high performance	4	1.50	Be cautious as this could be a fluke due to low sample size, however allocate more points	Give a decent amount of points	25
Medium Advertisement, low performance	26	0.05	Product unlikely to improve in the future	Subtract a small amount of points to discourage selection	-20
Medium Advertisement, medium performance	44	0.24	A good choice when better options have been exhausted	Go towards higher end of points allocation	35
Medium Advertisement, high performance	23	0.61	High priority for selection. Clearly a winner	Allocate maximum points	50
High Advertisement, low performance	320	0.04	No more chances. This product does not do well	Allocate minimum points	-50
High Advertisement, medium performance	100	0.22	A good choice when better options have been exhausted. Unlikely to improve.	Allocate average-good points	30
High Advertisement, high performance	430	0.35	A great choice.	Allocate maximum points	50

I hope that makes the idea of what I want to do really clear - I'm trying to encourage products that have good potential or an existing track record of success to get the most exposure, while limiting bad products, and being cautious about products that can go one way or the other (or haven't been tested yet)


The key here, and this is the bit I'm struggling with, is how to mathematically examine the relationship between Features & Pax/Feature ratio.

When I look at that in my head, I think "44 features and 0.24 pax/feature, that means it's had enough features to be tested and it's got an average ratio so it's not a bad product - give it some points but don't go over the top."


This is the tricky part, converting that logic to something I can input as a formula and calculate across a series of over 1,000 products.


So far, I've got the following two formulas:

=(TAN(B2)*(C2)*-25)]

=50*(((B2*C2)-1/((B2*C2)+1)))

The second one I'm told is a Sigmoidal curve, but actually the first formula gives a better result. Although it's still not quite right. For example, using the first formula logic:

Product	Features	Pax/Feature	Points
Apple	11	0.18	1027
Banana	140	0.50	62

While they would both cap out at 50, I would classify Apple as "Low Advertisement, medium performance" whereas Banana is absolutely "High Advertisement, high performance" and consequently, Apple should sit at around 20 points. Therefore the formula isn't correctly examining the link between Features and Pax/Feature



I'm not sure if I'm barking up the wrong tree here, but any help would be appreciated. Ideally, I'd end up with a formula (however long) that I can add into a cell. Thank you!

 

[RockandGrohl]

Bump, although I really do feel like I'm barking up the wrong tree.

Some helpful information is that the average for features is 92, the average for pax/feature is 0.2