Working out an average of class test results, where resits are capped at 65% and where those not on a resit don't have their average effected.

[baileyb103]

I have been asked to produce a spreadsheet where we track trainee test results and to get an average for each trainee. I am currently using the formula =IFERROR(AVERAGEIF(B2:H2,"<>0"),"") so that it doesn't decrease the average for those that don't require a resit. However, for average purposes we want to cap the result at 65%. Ideally for other analysis we want to put in the actual marks scored but have the average work it out as 65% if it is 65% and over. I've been told to just put in 65 and then add a comment of actual score but that wouldn't suit for when they want other analysis conducted.

I've considered putting an IF function in, however unsure as to where it would go, and if there is a better function or method to utilise.

Thank you in advance for any help.

 

[FormR]

Hi, you could try:

=IFERROR(AVERAGE(IF(B2:H2>0,IF(B2:H2>65%,65%,B2:H2))),"")

 

[baileyb103]

Hi, you could try:

=IFERROR(AVERAGE(IF(B2:H2>0,IF(B2:H2>65%,65%,B2:H2))),"")

Thank you. Unfortunately that will generate a flat 65% across the board, whereas I was ideally looking at just col C, E, and G being capped at 65% if over.

 

[FormR]

Is there something about those columns that we can use to logically determine if they should be capped or not? Something in the header maybe? The screen shot in your first post doesn't appear to be related to the problem at hand.

 

[baileyb103]

Apologies, I took the cpature image from the wrong file like a muppet. Here should be the correct image.

It would be columns C, E, and G that would need to be capped at 65%.

 

[FormR]

You could try this:

=IFERROR(AVERAGE(IF(B2:H2>0,IF($B$1:$H$1="Resit",IF(B2:H2>65,65,B2:H2),B2:H2))),"")

 

[baileyb103]

That works beautifully. Thank you