Excel spreadsheet calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a teacher and I am trying to figure out a calculation that will average
3 grade columns together, but with a max possible score of 100. 2 of the 3
columns could potentially have a value in excess of 100, but our final weekly
averages can not be in excess of 100. We grade on a weekly basis and each
final grade is based on 4 weekly grades, so having even one of the weekly
averages in excess of 100 throws the final average out of wack.
 
Assuming then, that when calculating the average you want any >100 grades to
be changed to 100, for the grades in B2:E2

=AVERAGE(IF(B2:E2)>100,100,B2:E2))

This is an array formula, so you must type it in, then press CTRL+SHIFT+ENTER,
not just ENTER.

Adjust the references if necessary. You said "average 3 grade columns", but
then referred to 4 weekly grades, so I don't know for sure how many there are.
 
Myrna Larson said:
Assuming then, that when calculating the average you want any >100
grades to be changed to 100, for the grades in B2:E2

=AVERAGE(IF(B2:E2)>100,100,B2:E2))

This is an array formula, so you must type it in, then press
CTRL+SHIFT+ENTER, not just ENTER.
Click to expand...
....

Mind the typo - the first right parenthesis should be deleted.

This can be done without recourse to array formulas.

=(SUMIF(B2:E2,"<100")+100*COUNTIF(B2:E2,"<100"))/COUNT(B2:E2)

This is much longer, but it has the potential benefit of ignoring error
values in B2:E2.
 
Harlan Grove said:
=(SUMIF(B2:E2,"<100")+100*COUNTIF(B2:E2,"<100"))/COUNT(B2:E2)

This is much longer, but it has the potential benefit of ignoring error
values in B2:E2.
Click to expand...
Perhaps I don't understand your formula but I would have thought that

=(SUMIF(B2:E2,"<100")+100*COUNTIF(B2:E2,">100"))/COUNT(B2:E2)

would have been what was reqired.

Regards

Sandy
 
Sandy Mann wrote...
...
Perhaps I don't understand your formula but I would have thought that

=(SUMIF(B2:E2,"<100")+100*COUNTIF(B2:E2,">100"))/COUNT(B2:E2)

would have been what was reqired.
Click to expand...

Simple screw-up on my part. Your correction also ignores cases where
cell equals 100 exactly. So either

=(SUMIF(B2:E2,"<=100")+100*COUNTIF(B2:E2,">100"))/COUNT(B2:E2)

or

=(SUMIF(B2:E2,"<100")+100*COUNTIF(B2:E2,">=100"))/COUNT(B2:E2
 
Back
Top