Average if Help

[RFJ]

I'm trying to calculate average salaries by for a grade within a payroll

I've got two named ranges - one is called 'grade' the other 'salary'. These
contain the grades and salaries of all staff

I'm trying to enter an array formula along the lines of

=average(if(grade=1),salary)

However, I can't get the syntax right as I keep getting error messages. I've
done the obvious things like check the range naming, etc. but can't get the
formulae right.

Can SKS help. TIA

Regards,

Robin

 

[Guest]

Try

=SUMPRODUCT(--(grade=1),salary)/SUMPRODUCT(--(grade=1))

 

[Guest]

try
=sumif(grade,1,salary)/countif(grade,1)

 

[Bob Phillips]

You almost had it right, just a ) in the wrong place

=AVERAGE(IF(grade=1,salary))

and this is an array formula, so commit with Ctrl-Shift-Enter