RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 810
- Office Version
- 365
- Platform
- Windows
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!
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!
Glad it works for you.
[/TD]
