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

RockandGrohl

Well-known Member
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 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.

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:

Excel Formula:
``=(TAN(B2)*(C2)*-25)]``

Excel Formula:
``=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!

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

RockandGrohl

Well-known Member
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

Replies
1
Views
109
Replies
0
Views
256
Replies
0
Views
206
Replies
5
Views
3K

1,141,770
Messages
5,708,436
Members
421,570
Latest member
BaileyJ

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.

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