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]

Think I got it figured out:

{=IF(G2="1",IF(COUNTIF($C$2:$C$20,CONCATENATE(LEFT(C2,5),"*"))>COUNTIFS(C:C,C2),INDEX($D$2:$D$20,MATCH(1,(1=$A$2:$A$20)*(F2=$F$2:$F$20)*(C2<>$C$2:$C$20),0)),""),IF(G2="2",IF(COUNTIF($C$2:$C$20,CONCATENATE(LEFT(C2,5),"*"))>COUNTIFS(C:C,C2),INDEX($D$2:$D$20,MATCH(1,(2=$A$2:$A$20)*(F2=$F$2:$F$20)*(C2<>$C$2:$C$20),0)),""),""))}

• G2 determines whether it's rank 1 or 2.
• C:C is Paper Name
• D:D is Tour Name
• F:F is Paper Name Left 5 (So Gloucester becomes Glouc)

Basic idea is it finds a criteria, then matches that criteria using Index-Match with multiple criteria.

Whole thing needs to be an array formula.

So what it's doing in Cell H2 is:

1. Is A2 1? (This is the rank)
2. If it's 1, count the number of times "GLOUC" appears in column C. Then count number of times the paper name appears against all other paper names. Make sure that count of the instance against all papers is greater than just that specific paper instance. This means that if there are 3 different "Gloucester" papers, the formula will proceed, but if there's only one "Bristol" named paper, it won't bother and will return blank.
3. If that count is higher, it indexes the tours and matches based on the lookup of "1"
4. The criteria for the Index Match states that it must find where 1 appears in the ranks, additionally where LEFT,5 for the paper near appears in the papers, BUT NOT where the paper name matches exactly in the list of papers.
5. It then returns the result of a SIMILAR paper's #1 tour name.
6. If the initial IF statement shows it's in fact a #2 rank, then it will do the process all over again but look up the second best tour!
7. Finally, if it isn't 1st or 2nd rank, it returns a blank.

This is the most complex formula I've ever written without outside help.

Now it looks like I need to have an additional before + after rank and score column, then give a new overall rank based on the new score. Crikey this is getting cyclical and fast.

 

[Eric W]

I read the previous post, and I got the gist of your requirements, but I didn't dig into it too deeply, since it appears you found a solution you're happy with. Based on the requirements, it's understandable that it's so long!

I can see why it's becoming complicated and cyclical. You're looking at a lot of variables. Try not to strive for perfection though, you'll never get it. Just make sure that all your variables are considered to some degree, and that you get a "good" answer. If you really want, you can try to do some post-advertising analysis. See if you can figure out which of your variables seem to really matter, and which ones not so much.

Good luck!

 

[RockandGrohl]

Try not to strive for perfection though, you'll never get it.

Haha truer words have never been spoken!

I think it's causing more of an issue than it seems, however -

Let's say we have a tour called "Welsh Beaches" and it's got some seriously high points - there are three different welsh beach tours on a particular paper and these are at:

1. - 59 points
2. - 58.5 points
3. - 58 points

The next tours down in 4, 5 and 6th place are Scotland Highlands with:

4. - 42 points
5. - 40 points
6. - 38 points.

In an ideal world, I'd want one Welsh Beach and one Scotland Highland, however, the formula above correctly searches the paper for the tour, then correctly smashes the chance of the duplicate items, including the original tour item.

So what this would do is universally, for that paper, can off Welsh Beach and overwhelmingly select Scotland Highland in its place.

I don't think there's a way to apply a formula to every other cell except the first instance where the rule is applicable... Except maybe an IF statement that says "If formula is true and score range is max, do not subtract points, otherwise subtract points"

What do you think?

 

[Eric W]

I have to admit, your sheet and formulas are starting to get beyond me! There's just a lot going on.

As far as your latest problem, if you have a formula and you want to apply it to the first matching cell only, you can preface your formula with something like this:

	A	B	C
2	Tour		First?
3	abcd		TRUE
4	abc1		FALSE
5	abcx		FALSE
6	;klj		TRUE
7	oiu		TRUE
8	abct		FALSE

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

Sheet1​

Workshee t Formulas Cell Formula C3 =MATCH(LEFT(A3,3)&"*",$A$3:$A$10,0)=ROW($A3)-ROW($A$3)+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>