I have created a spreadsheet for a weightloss contest between myself and two of my friends. I've looked at some of the other examples here, but the formatting is different enough that I have been having trouble figuring this out and would appreciate help from someone more experienced with Excel (I play with coding in my spare time, but have no background beyond C++ classes I took 20 years ago).
Originally, the contest was only with one other person, head to head on a weekly basis. Each week, we would weigh in on a Friday morning and input our weight for the week. The spreadsheet would then calculate our weekly percentage change, relative to our previous week's weigh in, and compare our percentages to determine the winner. I used the following formula:
= if(F21=N21, 0, if(F21>N21, 20, -20))
My weight loss percentage is in column F, where my friend's is in column N. This nested if statement worked really well when it was just the two of us. I also have another cell giving us a running total of what we owe each other. If we tied in a given week, neither person won.
We are adding a 3rd friend to our contest, and I am struggling with how to compare all 3 sets of numbers, and give output based on the comparison.
By way of example, my goal would be to compare the value from cell F21, N21, and W21. If a given participant has the highest value, then I want to output +30, if the middle -10, and if the least lost, -20.
In this way, the winner will be assigned 30 each week, with 2nd and 3rd place chipping in different amounts based on their rankings. If possible, I'd also like to conditionally format the cells to display different colors based on the weekly outcome (ie. +30 = Green, -10 = Yellow, -20 = Red), but that is secondary to having the amounts automatically compared and payouts listed in a column. If you have any suggestions or resources, I'd appreciate it!
Bookmarks