Total Points Formula in Excel

Question

Hello, does anyone know how can I create a formula using this specific example:

If a member provides 6-10 hours of training they receive 10 points, for 13-20 they get 20 points etc. So on the excel sheet is listed the following way.

Columns C5, E5, and G5 have hours: 2, 4, and 3. Since they completed 9 training hours they get 10 points based on the point system listed above. How can I create a formula to calculate the hours and how many hours total equal the total points for the numbers above.

Also, putting a stop after 21+ hours as the maximal points they can receive is 30 points.

I have three different sheets that I would like a formula for if anyone can please help! They are all under different columns hope these screenshots below help.

Answer 1

Hi Veronica

Please try the following steps

1. On a new sheet (recommended) create 3 tables as in the picture below and give them a meaningful Named Range

a) Points Per Hours Table aka "PtsHours"

b) Points Per Percentage Table aka "PtsPercent"

c) Points Per Communications Table aka "PtsComs"

2. Assuming, for example, that cell X5 sums the hours of C5, E5, and G5

Then to calculate the points try the formulas

For Points Per Hours = VLOOKUP(X5,PtsHours,2,TRUE)

For Points Per Percentage = VLOOKUP(X5,PtsPercent,2,TRUE)

For Points Per Communications = VLOOKUP(X5,PtsComs,2,TRUE)

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hi Jeovany,

Thank you for the information! It solved my problem. Have a nice weekend.