Is there a formula to "Grade" the results in a cell based on varying criteria?

[RockandGrohl]

I wonder if this is possible.


I'm trying to create a series of operators to make "choices" for me, and my concept is to grade each of the factors out of ten.

To make this easier to understand, I'm trying to choose which two holiday "Tours" to run, based on a series of criteria. I want to grade each criteria's suitability out of 10, then sum up the grades and find the two best scorers which will be my choice.

As an example, I want to run a tour for, let's say, a Wales Holiday - "The Welsh Experience"

My idea criteria for a Holiday would be:

1) "When the tour was last selected" - if it was selected 1 week ago, then 0. If 2 weeks ago, then 0, if 3 weeks ago, then 2, if 4 weeks ago, then 4 and so on, so the longer time between today and when the tour was selected, the higher the score up to a maximum of 10.

2) "Price of Tour" - my ideal criteria would be a tour costing approximately £150. So if a tour is £150, that scores 10. If a tour is either £160 or £140 (£10 above or below ideal) then it scores 9, and so on, until tours that are too expensive or too cheap are graded 0.

3) "Location" - simple, Tours that are in the UK score 10, tours that aren't score 0. This should be an easy IF statement which I can easily run.

4) "Capacity" - Where tours already have a lot of bookings, the score gets lower. So anything above a 44 gets 0, with the score gradually increasing the further towards a lower existing capacity.

5) "ROI" - I have an average ROI figure, let's say it's 6. 6 ROI represents a score of 5, with higher ROI's earning scores towards 10, and lower ROI's earning scores towards 0.

6) "Date" - Tours that run too close to the current date earn close to 0 points, and tours that run too far away earn close to 0 points, if I pick, say, the golden date of 56 days from TODAY date to be a 10, then tours around that date earn 10's with the score decreasing the closer or further from that golden date (same principle as price)

7) "Frequency" - I have a formula that tells me how frequent these tours have appeared in advertisement. I want to set a 10 score to never before appeared, with the score decreasing as frequency goes up.




What I'm asking for here is not to have all these formula's written for me, but wondering if it's possible to define a criteria and then add a formula in to give a score, without using 10+ IF statements for each criteria?

So let's use those criteria to work out an approximate score for the Welsh Experience.

Today is the 1st August.

Our average ROI for Adverts is 6

Here's the criteria and an example of what I think each score would be:

Tour Selection Date: 3 Weeks ago - Score 2/10
Price of Tour: £170 - Score 8/10
Location: UK - Score 10/10
Capacity: 32 - Score 5/10
ROI: 7.2 - Score 6/10
Date: 28th Sept - Score 9/10
Frequency: 3 times - Score 6/10

Overall score 46/70


These calculations would be weighed against other tour scores to determine the best tours to advertise.

Thank you!

 

[RockandGrohl]

I see - where there are a structured amount of variables with a standard gap between them, this makes sense to use a quick 'n' dirty lookup table. What about a range of variables such as pricing? We can have £250, 259, 265, 269, 270 and anything else in between. This makes sense to have a logarithmic scale. Let's say the sweet spot is £190, anything from £100-190 is fine, say £100 starts as an 8, 10 is £190, then £250 is 8 - so 100 pounds before is an 8, but 60 pounds after is an 8 - see where it's weighted to be predisposed towards lower price ranges? Then progressing after £300 it rapidly drops off. £300 might be a 6, but £320 is too expensive so you're looking at 0, then £350 is out of the question and would be -5 or something.

 

[Eric W]

You could just find a curve you like, then scale it to match your values. For example:

Excel 2012

	A	B	C	D	E	F
1	Mid	Scale	Power		Price	Score
2	190	30	3		130	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =MAX(0,11-C2^(ABS(A2-E2)/B2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You can tweak the values in A2:C2, and see how the score changes based on the value in E2. There are a lot of possibilities, but I think at some point the level of complexity will be more than its worth.

 

[RockandGrohl]

Hi Eric,

While that formula works, it still results in an even dropoff either side of the "correct" value. I might add an operator like IF [price] is lower than [MID], multiply [price] by 0.1 and add to score. Something like that.

 

[Eric W]

	A	B	C	D	E	F	G	H
1	Price	Score		Mid1	Mid2	Scale	Power
2	190	10		100	190	25	3
3	195	9
4	200	8
5	210	8
6	220	6
7	230	4
8	240	1
9	250	0

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula B2 =ROUND(MAX(0,10-IF(A2<$D$2,$G$2^(($D$2-A2)/$F$2),IF(A2<=$E$2,0,$G$2^((A2-$E$2)/$F$2)))),0) <tbody> </tbody>

<tbody>
</tbody>

Anything in the range 100-190 gets a 10, numbers outside that range drop off as shown. You can tweak the values as needed.

 

[RockandGrohl]

Hi Eric, that works beautifully. I've actually gone ahead with a factor of 2, scale off 55, mid1 as 140 and a mid2 as 275.

This is due to some research I did on ROI per tour cost and needed to alter the ideal pricing.

Right now, the whole sheet is working excellently and serves as an ideal proof of concept. Thank you so much for all your help and explanations.

 

[Eric W]

Cool! Glad it works for you.

 

[RockandGrohl]

During the implementation and testing I think I'm running into a new problem with the capacity rule.

If it were as clean-cut as 1 coach per tour with 53 seats, that would be fine, unfortunately it's not the case.

Sometimes we run multiple coaches for the same tour. We want to identify that anything 42 and under is good, with the score quickly dropping off after 42, however on a tour with three coaches, I need to calculate the same parameters but multiplied by three, except the -11 part.

So on a tour with one coach, let's say "Exciting Wales" tour, that has a capacity of 53, current passengers are 41. That can get 10 points.

But we have another tour "Liverpool Docks" that has 3 coaches, so max capacity of 159. However, you wouldn't do 53-11 for each coach (which gives 126), you would do the whole capacity of the tour, -11, to effectively say we are relying on getting 11 bookings naturally without advertisement, for whichever tour, so these tours that have more than 11 spaces to fill score well, but tours with 11 spaces or less remaining need to be penalised.

I can do this with a lookup table, however I feel like your solutions where you divide a score by a delta are infinitely more elegant.

 

[Eric W]

Well, I can't think of anything really more elegant than what we already have. In your latest scenario, I think you can use the same formula from post 14. For the Mid2 value, just set it to something very large, it really doesn't matter in this case. For the Mid1 value, use a lookup table. "Exciting Wales" returns 41, "Liverpool Docks" returns 148. When you have different values that don't follow any particular pattern, a lookup table is the way to go. If you want to get real fancy, you can include the scale and power in the lookup table, so different tours could have different drop-off rates, but that's probably overkill.

 

[RockandGrohl]

Well, I can't think of anything really more elegant than what we already have. In your latest scenario, I think you can use the same formula from post 14. For the Mid2 value, just set it to something very large, it really doesn't matter in this case. For the Mid1 value, use a lookup table. "Exciting Wales" returns 41, "Liverpool Docks" returns 148. When you have different values that don't follow any particular pattern, a lookup table is the way to go. If you want to get real fancy, you can include the scale and power in the lookup table, so different tours could have different drop-off rates, but that's probably overkill.

Hi Eric,

Cheers, this is what I have - I have a habit of struggling to vocalise my thoughts until I type them, and then after I post I am able to see my problem more clearly and thus figure out a solution. What I did was take the calculation of how many vehicles there are, let's say 3. So that's a max capacity of 159. Then I subtract the number of current passengers - that's 142 Pax, so the outcome is 17. This is then fed into a lookup table which tells me if the value is 0-5, give it -10 points, if it's 5-8, give it 0 points, if it's 8-10, give it 4 points, if it's 10 or above, then 10 points is fine.

So we both came to the right conclusion on this. I wanted to avoid look-up tables where I can as this whole sheet needs to be macro-tised, but it's important to lay the logic out.

I've got a new challenge.. see post below.

 

[RockandGrohl]

Here's my newest challenge.

	A	D	O	P	Q
1	Paper	Tour Name	Score	Diversity	Diversity2
2	Gloucester Herald	Llandudno	50	50
3	Gloucester Herald	Liverpool Heights	49	49
4	Gloucester Herald	Liverpool Docks	31	31
5	Gloucestershire News	Llandudno	53	53
6	Gloucestershire News	Liverpool Heights	45	45
7	Gloucestershire News	Liverpool Docks	33	33
8	Gloucestershire News	Cornwall Flowers	32	32
9	Gloucestershire News	Swindon	21	21
10	Worcester News	Stunning Seaside	68	68
11	Worcester News	Golden Seaside	57	37
12	Worcester News	Majestic Railway	56	56
13	Worcester Happenings	Stunning Seaside	68	68
14	Worcester Happenings	Majestic Railway	56	56
15	Worcester Happenings	Golden Seaside	57	57
16	Worcester Happenings	London Tower	49	49
17	Worcester Happenings	Grand Railway	52	52
18	Worcester Voice	Grand Railway	52	52
19	Worcester Voice	London Tower	49	49
20	Worcester Voice	Stunning Seaside	68	68

<tbody>
</tbody>

Here's the explanation.

In Column A we have a list of over 5,000 papers. There are 74 unique papers and each can have a maximum of 75 tours to choose from. We show two ads per paper.

Right now, I have a Diversity column (P) - this looks at (D) and determines (successfully, to a degree) how similar two tours are. This is to prevent two of the same type of tour showing in the advert as we want to offer a diverse range of tours. You can see for the Worcester News, tours ranked 1st and 2nd on points are "Stunning Seaside" and "Golden Seaside"

This formula then subtracts 20 points because that's two Seaside tours, so now the rank is 1 - Stunning Seaside. 2 - Majestic Railway. 3 - Golden Seaside.

Fantastic, working great. Now there's a new problem. There are sometimes multiple papers covering the same region, or the same paper featured twice in one week (We may advertise in the Monday and Thursday editions of the same paper)

So my thought is this, in cell Q2:

1) Look in Column A2 for the first 5 characters using Left.
2) See if that value matches another cell in A:A
3) EXCLUDE any values that are an exact match for A2 (I'll explain why below)
4) If the left 6 pulls a match, lookup highest score in (P) for corresponding paper
5) If the Tour that matches the highest score in (P) is equal to the tour in D2, then take the score in P2 and subtract 20.


In Plain English, what I'm trying to do is find a similar (but not the same!) paper in the A:A range, look in that paper for the two best performing tours, then see if those tours match the two best performing tours for my paper. If they do, then penalise the matches in my paper to prevent Worcester from running three duplicate ads in the week.

And a concrete example:

Stunning Seaside is the best tour for each of the three Worcester papers and would run 3x in the week. The ideal formula would look and see that we've found 1 Stunning Seaside in Worcester News, so it penalises Stunning Seaside for Worcester Happenings, moving the score down to 48.


Is this possible, or will I run into a circular warning, seeing as the values will constantly update and penalise themselves because all the top tours will be penalised at once, then the 2nd best will be the top and will be penalised, etc etc.

Thanks!