Talk

Advanced search

Help desperately needed, excel formulae!

(10 Posts)
Feelingworriednow Thu 20-Apr-17 11:10:54

What kind of formulae would I use if I need to average figures from a list but ONLY if they fulfill another criteria? I have attached a picture of a very simple version of a much, much bigger spreadsheet to illustrate what I am trying to do. Other people will be adding more children now and again and I want the formulae to be able to search for a criteria so that the others don't have to change the formulae as they have even less idea what they are doing to me!

So where I have put "???" in red, I want the formulae to average the Year 1 Autumn mark from the data immediately above but ONLY if the data is from a boy, which can been seen from the letter B or G in the gender column next to the names.

I'm the only person in school with any idea of how to use excel even at a simple level and this is doing my head in! My15yo DD has just told me to ask the hive mind since just googling an answer comes up with some very confusing answers! Your help would be very much appreciated!

ImperialBlether Thu 20-Apr-17 11:12:51

Wouldn't it be IF... then AVERAGE?

ImperialBlether Thu 20-Apr-17 11:13:24

I'm going to PM you.

UniversallyUnchallenged Thu 20-Apr-17 11:13:45

You need "IF" statements, goggle them, cut and paste and then change them to the column labels you are using

Feelingworriednow Thu 20-Apr-17 11:16:42

I can do an average formula but don't know how to link it to the IF.

Reow Thu 20-Apr-17 11:17:44

www.exceltrick.com/formulas_macros/excel-if-statement/

NatalieRushman Thu 20-Apr-17 11:26:16

Use AVERAGEIF.

The formula you would use in this case is =AVERAGEIF(Range of gender cells,"B",Range of marks you want to average"

Example: in the data you have pictured, presuming the first column at the top of the screen is 1, the formula would be:
=AVERAGEIF(E5:E12,"B",K5:K12)

NatalieRushman Thu 20-Apr-17 11:27:32

The "B" can obviously be substituted by "G" or even "1" when you get to calculating FSM or PP.

Feelingworriednow Thu 20-Apr-17 12:30:48

There really is nothing MN can't solve! Many, many thanks and flowers to ImperialBlether for working her magic on my spreadsheet and to the super advice and links from everyone else. I know where to come next time! Thank you, thank you, thank you.

eeyore2 Thu 20-Apr-17 14:39:16

You can also use 'Sumif' and then 'countif' and divide one by the other if you want to show your working in a more step by step way.

Join the discussion

Join the discussion

Registering is free, easy, and means you can join in the discussion, get discounts, win prizes and lots more.

Register now