excel formula help - assigning a value depending on the range, then summing that

mts

For my course I am teaching right now I am running essentially a house cup from Harry Potter.

On my excel gradebook sheet Individuals are assigned a number (1, 2,3) based on their team
Currently it is set up to poll the team column and include it as part of a team average grade
using this formula =AVERAGEIF($C3:$C45,"1",S3:S45) where column c is the team assignment and S is the average grade for the assignments going towards the cup points.

I want to change this so that people earn points based on their grade (e.g. 90-100 gets 5 points, 80-90 gets 4 etc.)

SO what i want it to do is first check column C for the team, then look in the assignment column and assign it a point value based on a range, then add all team points together to produce a team point value.

I imagine some sort of pivot table would work, but not sure how to put the if statements into something like that

Dis'

Why not make a new column for student points, then SUMIF that column with team number as the criteria to find total team points?

mts

yea, sorta what i am doing right now

column is doing a vlookup to return a value based on score, then using sumif to sum it based on team

not quite as slick as i wanted but
it is doing the job

Septus

I really do think that using a reference table for your points and grade brackets, and doing a lookup formula will be the best overall. I toyed with what else would work, and the only other thing that came to mind was this monstrosity, to be loaded into each row where you have the grade to generate the point total, and to then be SUMIF'ed elsewhere:

S is the grade column. This gives you one value to represent the grade bracket, for each individual grade, and then uses boolean logic to give true/false for each grade bracket and assigned point value.

=SUM((TRUNC(S2/100,1)=0.9)*5,(TRUNC(S2/100,1)=0.8)*4,(TRUNC(S2/100,1)=0.7)*3,(TRUNC(S2/100,1)=0.6)*2,(TRUNC(S2/100,1)=0.5)*1)

MrTLicious

I think you might want an array function.

Suppose you have your 5 breakpoints in Z2:Z6 (maybe 0, 60, 70, 80, 90). Use the array function:

=SUM(IF($C$3:$C$45=1,MATCH($S$3:$S$45,$Z$2:$Z$6,1),0))

If you don't want a reference table at all you can replace the MATCH function with something like what Septus has or a simpler equation if the breakpoints are linear.

To make it an array function, press ctrl+shift+enter (I think command+enter on mac) after typing the formula instead of just enter (otherwise you'll get a #VALUE error).