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!

 

[Eric W]

Welcome to the forum.

I know you said you didn't want the formulas written, but it seemed the best way to illustrate the answer to your question.

For example:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1												Parameters
2												Last Selected		Price		Location	Capacity		ROI		Date out		Frequency
3												Days	Increment	Centrepoint	Delta		Max	Delta	Centrepoint	Delta	Days	Delta	Delta
4		Last Selected	Price	Location	Bookings so far	ROI	Date of Tour	Frequency selected		Total Score		17	4	150	10	UK	44	3	6	1	56	5	1
5	Tour A	1-Jun	150	UK	23	6	1-Sep	2		55		10		10		10	7		5			8
6	Tour B	15-Jun	120	France	33	8	5-Sep	5		35		7		7		0	3		7		6		5
7	Tour C	1-Jul	175	UK	45	5	14-Sep	0		43		3		8		10	0		4		8		10
8	Welsh Experience	11-Jul	170	UK	32	7.2	28-Sep	3		46		1		8		10	4		6		10		7

<tbody>
</tbody>

Tours​

Worksheet Formulas Cell Formula P5 =IF(D5=P$4,10,0) Q5 =MAX(0,INT((Q$4-E5)/R$4)) J5 =SUM(L5:W5) L5 =MIN(10,INT((TODAY()-B5-L$4)/M$4)) N5 =MAX(0,10-INT(ABS(C5-N$4)/O$4)) S5 =MIN(10,INT((F5-S$4)/T$4)+5) U5 =MAX(0,10-INT(ABS(G5-TODAY()-U$4)/V$4)) W5 =MAX(0,INT(10-H5/W$4)) <tbody> </tbody>

<tbody>
</tbody>

Your tours are on the left, the formulas and criteria are on the right. Look at the formula in L5. This is your "Last Selected" criterion. The score is calculated by going 17 days back (L4) and giving that a 1 score, and adding 1 to the score for every 4 days (M4). If you ever want to change 17 and 4, you can just change those cells, and the scores will automatically update. The other formulas are calculated in the same way, just change the parameters in row 4.

Hope this helps.

 

[RockandGrohl]

Welcome to the forum.

I know you said you didn't want the formulas written, but it seemed the best way to illustrate the answer to your question.

For example:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1												Parameters
2												Last Selected		Price		Location	Capacity		ROI		Date out		Frequency
3												Days	Increment	Centrepoint	Delta		Max	Delta	Centrepoint	Delta	Days	Delta	Delta
4		Last Selected	Price	Location	Bookings so far	ROI	Date of Tour	Frequency selected		Total Score		17	4	150	10	UK	44	3	6	1	56	5	1
5	Tour A	1-Jun	150	UK	23	6	1-Sep	2		55		10		10		10	7		5			8
6	Tour B	15-Jun	120	France	33	8	5-Sep	5		35		7		7		0	3		7		6		5
7	Tour C	1-Jul	175	UK	45	5	14-Sep	0		43		3		8		10	0		4		8		10
8	Welsh Experience	11-Jul	170	UK	32	7.2	28-Sep	3		46		1		8		10	4		6		10		7

<tbody>
</tbody>

Tours​

Worksheet Formulas Cell Formula P5 =IF(D5=P$4,10,0) Q5 =MAX(0,INT((Q$4-E5)/R$4)) J5 =SUM(L5:W5) L5 =MIN(10,INT((TODAY()-B5-L$4)/M$4)) N5 =MAX(0,10-INT(ABS(C5-N$4)/O$4)) S5 =MIN(10,INT((F5-S$4)/T$4)+5) U5 =MAX(0,10-INT(ABS(G5-TODAY()-U$4)/V$4)) W5 =MAX(0,INT(10-H5/W$4)) <tbody> </tbody>

<tbody>
</tbody>

Your tours are on the left, the formulas and criteria are on the right. Look at the formula in L5. This is your "Last Selected" criterion. The score is calculated by going 17 days back (L4) and giving that a 1 score, and adding 1 to the score for every 4 days (M4). If you ever want to change 17 and 4, you can just change those cells, and the scores will automatically update. The other formulas are calculated in the same way, just change the parameters in row 4.

Hope this helps.

Good lord that really does help - I knew I was on to something with MIN and MAX and was wondering whether I'd need a "MEDIAN" formula to determine the actual grade. I've been struggling putting my thought process into a search engine to get what I'm sure there is.

This looks really promising, is it possible for you to attach an excel file? The formatting has been really screwed by the browser available to me at the moment. Many thanks for taking the time to do this.

 

[RockandGrohl]

Scratch that request - I replicated the example at home and it works beautifully.

I'm having trouble understanding the formula for the pricing though, so you set a max formula with the values as 0 or 10. Then you use ABS which I assume is for formatting reasons? To remove currency signs from text? Here's what I don't understand and it may just be me failing basic math but C5-N4 is 150-150, resulting a calculation of 0 in the brackets, so it's 0 divided by 10 (the delta) which should be 0? But it results in 10.

I've changed the parameters to now be £200 as centrepoint and 20 as delta. Could you help me understand what the strict purpose of the delta is for? Thanks.

 

[Eric W]

OK, here's the N4 formula, which is the score for price:

=MAX(0,10-INT(ABS(C5-N$4)/O$4))

Let's say your center point is 150, and the price is 130. 130-150 = -20. The ABS function stands for "absolute value", which essentially keeps positive values positive, and turns negative values positive, so ABS(-20) = 20. You can think of it as a distance function, it tells you how far from the center point you are, but not the direction. If the price is on the other side of the center point, say 170, then 170-150=20, and ABS(20) = 20. Now we divide by the delta, or 10, because you wanted each increment of 10 pounds to be worth 1 point. 20/10 = 2. 20 pounds from the center point = 2 points. The INT gets rid of any decimals. 5.7 = 5. You might want to consider ROUND instead (ROUND(5.7,0)=6), but I don't think that will matter much in this case.

You want prices right on the center point to be 10, and decrease as you move away. So we use the "10-" part of the formula. 10-2 = 8. Finally, if you get more than 10 points (100 pounds) away from the center point, the score will be less than 0, so the MAX(0, ... part of the formula insures the final score will never be less than 0.

The same kind of logic applies in the Days Out (U5) formula. You might need to work through a few examples to kind of get the feel of how these work. On the Formulas tab, you can use the Evaluate Formula tool to work through how Excel calculates them, step by step. Let me know if you have any other questions.

 

[RockandGrohl]

Eric, you're a genius. Thanks for explaining this. I will read and re-read it through and through so I can adjust the parameters as I need them.

My challenge is I want the range between £150 to £200 to be the golden goose, with scores dropping off outside of the range. Let me have a crack at it myself and if no luck I'll come grovelling back for help. Thanks!

 

[Eric W]

Cool! There are several ways to do that, take a shot at it and let us know. Glad I could help.

 

[RockandGrohl]

I'm not sure I can figure it out.

If say, 5 is the centre, where 5 gets 10 points, 0 gets 0 points and 10 gets 0 points, that means the score grade raises up, peaks in the middle, then falls down at an equal rate on the opposite side.

How do you make the score grade look more like this:

___
/ \
\

Instead of this:

/\
/ \
/ \


So, let's look at times advertised. 0 is great, 1 is great, 2 is great, but from 3-onwards, it should start to receive less and less marks, with the grade dropping dramatically. You'd want something like:

Weeks | Points
0 10
1 10
2 9
3 6
4 2
5 0
6 -2
7 -10
8 -20

You see how that scale isn't really linear, it's more logarithmic (correct term?) it punishes on a harsher and harsher scale, the further away from a correct answer. However, 0 weeks, 1 weeks and 2 weeks are all golden answers.. How would that work?

Thanks.

 

[RockandGrohl]

Well, those diagrams didn't work.

https://imgur.com/a/KpiJIkR

 

[Eric W]

Well, you probably could come up with a logarithmic function to get your values, but since you only have 9 values to worry about, it would be simpler to just build a table with your values and do a lookup:

	A	B	C	D	E
1	Weeks	Points		Weeks	Points
2	0	10		3	6
3	1	10
4	2	9
5	3	6
6	4	2
7	5	0
8	6	-2
9	7	-10
10	8	-20

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

Sheet7​

Worksheet Formulas Cell Formula E2 =VLOOKUP(D2,A2:B10,2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

If your week value is over 8, it would get -20 as well.