# 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