Help desperately needed, excel formulae!

[Feelingworriednow]

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!

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

I'm going to PM you.

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

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

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

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)

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

There really is nothing MN can't solve! Many, many thanks and 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.

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.

exceltutorialonline.com/excel-compare-two-columns/

Hi, If anyone Compare Multiple ExcelSheet Data Without using any Excel formula's Then Try Synkronizer Excel Comparing Tool. www.synkronizer.com/