AVERAGE but treat scores < 75 as 50

[burnsy180]

Hi everyone,

I'm working on a spreadsheet to calculate average scores from a given range,where they're 0-100. What I have is working fine, however for the purpose ofranking the averages I need to treat scores that are < 75 as “50” whencalculating the averages.

Eg. Scores are 100, 89, 85, 76, 74, 66 currently

=AVERAGE(that range)

is 81.667

I need the average to calculate based on 100, 89, 85, 76,50, 50 returning 75

I want to retain the original scores for graphing, justneed the 50 for calculations.

Does anyone have any suggestions? TIA

 

[Marcelo Branco]

Welcome to Mr Excel forum

Assuming data in A2:A7, maybe this array formula
=AVERAGE(IF(A2:A7>=75,A2:A7,50))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[burnsy180]

Welcome to Mr Excel forum

Assuming data in A2:A7, maybe this array formula
=AVERAGE(IF(A2:A7>=75,A2:A7,50))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

Success! Can't believe it was so simple haha.

{=AVERAGE(IF(C11:AK11>=75,C11:AK11,50))}

works where C11:AK11 is my data.
Now the only issue is that blanks (not filled yet) add as 50.
Can an extra IF be added to ignore blanks? Something like this? Perhaps a cleaner method?

{=AVERAGE(IF(AND(C11:AK11>=75,C11:AK11>0),C11:AK11,50))}

 

[burnsy180]

this works

=AVERAGE(IF(C11:AK11>=75,C11:AK11,IF(C11:AK11>0,50)))

thanks for your help!

 

[burnsy180]

this works

=AVERAGE(IF(C11:AK11>=75,C11:AK11,IF(C11:AK11>0,50)))

thanks for your help!

with {} obviously

 

[Marcelo Branco]

You are welcome. Glad to help.

M.