I have a set of columns in a Google Sheet which are calculating the average duration between two times. I would like to calculate a score in the cells marked with an X based on the calculated averages. The goal being to award 75 for the fastest, 50 for second and 25 for third. In the example below that would mean that Yellow’s score cell would be 75, Green’s would be 50 and Blue’s would be 25.
| Run | Fastest Time | Slowest Time | Average | Score |
|---|---|---|---|---|
| Green | 10:02:01 | 17:22:11 | 13:42:06 | X |
| Blue | 10:07:08 | 18:22:12 | 14:14:40 | X |
| Yellow | 10:11:09 | 16:07:11 | 13:09:10 | X |
I am assuming that some sort of VLOOKUP or IF function should be used but given the comparison between the averages I’m not sure how to put the function together. How do I calculate a cell value based on the comparison of other cells?
