Aug 23 2021 10:17 PM
What would be the best way to achieve the following?
Compare columns E,F,G with column D, determine if there is a match, and if there is a match (i.e. true), count the number of matches?
I'd like to create an index that indicates the reliability of a team. For example, if TC, DCW and TMI all guess a team to win, and that team loses (against all odds), how can I keep a score of such occurrences?
Sep 03 2021 03:20 AM
Hi @theirlaw
You can achieve your first requirement using below formula:
=SUMPRODUCT(($E$2:$E$300=F2:F300)*($D$2:$D$300<>"")*($F$2:$F$300=G2:G300)*($D$2:$D$300<>""))
And second requirement using this formula:
=M15-SUMPRODUCT(($D$2:$D$300=E2:E300)*($D$2:$D$300<>"")*($D$2:$D$300=F2:F300)*($D$2:$D$300<>"")*($D$2:$D$300=G2:G300)*($D$2:$D$300<>""))
A sample file is also attached for your reference.
Please let me know if it works for you.
Thanks
Tauqeer
Sep 05 2021 09:59 PM
Thanks for your assistance. It kind of works, but I need a little more. Let me explain further:
For each team (there are 16 teams), I need to know how many times that team was guessed, and what the outcome was (win or lose). This will then enable me to calculate how 'reliable' that team is (i.e. how many times they are picked, and how many times they actually win).
An example:
The team Bulldogs have performed very poorly in 2021. Picking them to win is a very risky move, and so they are 'unreliable'. Conversely, the team named Storm have won almost every single one of their games this year. Picking them is a very reliable choice.
Are you available to chat and discuss this further?
Sep 05 2021 10:01 PM
@Riny_van_Eekelen you've helped me previously, perhaps do you understand my requirements?
Sep 05 2021 10:16 PM
SolutionHi @theirlaw
Yes, you may explain your requirements through chat. I will message my contact details to you.
Thanks
Tauqeer
Sep 08 2021 12:03 AM - edited Sep 08 2021 12:04 AM
@tauqeeracma super thanks to you sir for your amazing help!
I'll upload the finished spreadsheet for everyone to see once I've had a chance to check and implement everything.
Sep 08 2021 01:35 AM
Sep 12 2021 08:55 PM
@theirlaw here's the file, for anyone who's interested. There's still some tweaks to be made but ti's great to receive such friendly help from everyone.
Sep 05 2021 10:16 PM
SolutionHi @theirlaw
Yes, you may explain your requirements through chat. I will message my contact details to you.
Thanks
Tauqeer