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