Excel Points Formula

Occasional Visitor

Hey, please can you help me with a couple formula? On my spread sheet I need the following:

1. Column C, D and E on the sheet "league table" to calculate how many of the % in the table each person got throughout the quarter on the sheet "Competition"

2. Column F on the sheet "league table" needs to calculate how many points each person gets for each % (100% = 3 points, 91%-99% = 1 point, 90% or below = 0 points and add it up as more shoppers are added throughout the quarter. 

I have tried multiple formulas but cant figure it out for the god of me.


1 Reply

Hi @jacobsscoots ,


If you have freedom to add new columns to your "Teams" tables, I recommend you to use the solution below. It is not the fancy approach, but it is one of the easiest and it is pretty simple to adjust if your table context change in the future.


1. We add 3 new columns (G, H, I, in the example), which goal is to count the number of weeks that the player got in with each range of %:



2. For the league table, we use a VLOOKUP formula to return this countings:



3. To calculate the points, we just use the following formula: =(C21*3)+D21 (just replacing the row numbers for the following players)


Check the attached file for more details and let me know if it helps. If you need anything else or if this solution does not fit to your case, let me know and we think together about a better solution.