Excel spreadsheet calculations

[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.

 

[Myrna Larson]

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.

 

[Aladin Akyurek]

Do you mean:

=MAX(100,AVERAGE(A1:C1))

 

[Harlan Grove]

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.

....

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.

 

[Sandy Mann]

=(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.

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

 

[hgrove]

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.

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