Averaging Marks Depending on their Value

Help Please!

I have the following information

 


CAT 1 CAT 2 SUP 1 SUP 2 AVERAGE MARK
48 55 67    
50 50      
30 61 87    

 

I need to calulate the average mark for each person. however

If CAT 1 and CAT 2 are both equal to or greater than 50, i need to find the average

if CAT 1 is less than 50 and CAT 2 is equal or greater than 50 and SUP 1 has a number greater than 50 then i need (CAT 2 + 50)/2

If CAT 1 is equal or greater than 50 and CAT 2 is less than 50 and SUP2 is greater than 50, i need to (CAT 1 + 50)/2

if both CAT 1 and CAT 2 are less than 50, and SUP 1 and SUP2 have values equal or greater than 50 i need to (50+50)/2

 

i hope this makes sense, any help to put this into a formula would be helpful

 

Thanks

I’m currently using Excel for Mac 2011 but need this to transfer to an older version of excel.

 

One thought on “Averaging Marks Depending on their Value

  1. andrew walker Post author

    Hi there

    Try this formula…

    =IF(AND(A2>=50,B2>=50),AVERAGE(A2:B2),IF(AND(A2=50,C2>50),SUM(B2+50)/2,IF(AND(A2>=50,B250),SUM(A2+50)/2,IF(AND(A2<50,B2=50,D2>=50),SUM(50+50)/2,""))))

    When typing this I have Cat A in column A, Cat2 in B and so on. Row 2 contains the first line of scores.

    I hope this helps!

    Thanks for writing.

    Andy

Leave a Reply

Your email address will not be published. Required fields are marked *