03856me
Active Member
- Joined
- Apr 4, 2008
- Messages
- 297
I have this contestant worksheet that is repeated many many times, that calculates the "place" based on "time" (column F), which works perfectly. Then I need to calculate the points awarded for members only. I have no problem calculating the points but NOT excluding non-members and blank time. I thought about adding a column calculating the place with the criteria, but the user is adamant that can not happen. Could you please help me add to my current formula for "calc points" or if there is a better way, I am open to that as well.
I cannot change the "Place" column becasue it is correct and is posted as the results of the event. I have added the last two columns (I & J) for this post to show what the correct points (answer) should be.
The formula in column F (Place) =IF($E5>9999,"",IF($C5="","",COUNTIFS($E$5:$E$999,"<"&$E5)+1))
The formula in column G (calc points) =IF([@Member]="No","",IF([@Place]=1,10,IF([@Place]=2,9,IF([@Place]=3,8,IF([@Place]=4,7,IF([@Place]=5,6,IF([@Place]=6,5,IF([@Place]=7,4,IF([@Place]=8,3,IF([@Place]=9,2,IF([@Place]=10,1,0)))))))))))
I cannot change the "Place" column becasue it is correct and is posted as the results of the event. I have added the last two columns (I & J) for this post to show what the correct points (answer) should be.
The formula in column F (Place) =IF($E5>9999,"",IF($C5="","",COUNTIFS($E$5:$E$999,"<"&$E5)+1))
The formula in column G (calc points) =IF([@Member]="No","",IF([@Place]=1,10,IF([@Place]=2,9,IF([@Place]=3,8,IF([@Place]=4,7,IF([@Place]=5,6,IF([@Place]=6,5,IF([@Place]=7,4,IF([@Place]=8,3,IF([@Place]=9,2,IF([@Place]=10,1,0)))))))))))