average in excle

[jeff.white]

I'm raking my brains...I have a table where column A has job titles,
column B has the salary. Column A has duplicate job titles, which is
OK, what I'm looking for is an average function that will give me the
average salary per job title. I know I could do this via a pivot
table but I was planning on using vlookup and/or average. I'm just
not getting the syntax correct. Any help?

 

[Guest]

=SUMPRODUCT(--(A2:A100=Job),B2:B100))/SUMPRODUCT(--(A2:A100=Job)

=SUMIF(A2:A100,job,B2:B100)/COUNTIF(A2:A100,job)

where Job is Job Title (text) OR cell containing Job Title

 

[Bob Phillips]

=AVERAGE(IF(A2:A100=Job),B2:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

or
=average(if(A:A=Job_title,B:B)
entered as array control-shift-enter

 

[Dave Thomas]

An AVERAGEIF function may help you. Example: A1:A100 contains the job titles
with duplicates, B1:B100 contains the salaries. You could put all of the job
titles in column C starting with C1, just once, no duplicates, sort them
alphabetically. Then in column D1 put the formula
=AVERAGEIF(A1:A100,C1,B1:B100) and drag the formula down so that there is a
formula in each cell in column D for every entry in column C. You will get a
"Formula omits adjacent cells error" for the formulas. Just clear the error
as it's meaningless in this case.
..

 

[Dave Thomas]

An AVERAGEIF function may help you. Example: A1:A100 contains the job titles
with duplicates, B1:B100 contains the salaries. You could put all of the job
titles in column C starting with C1, just once, no duplicates, sort them
alphabetically. Then in column D1 put the formula
=AVERAGEIF($A$1:$A$100,C1,$B$1:$B$100) and drag the formula down so that
there is a
formula in each cell in column D for every entry in column C. You will get a
"Formula omits adjacent cells error" for the formulas. Just clear the error
as it's meaningless in this case.

 

[Dave Thomas]

The addresses in columns A and B have to be absolute.

An AVERAGEIF function may help you. Example: A1:A100 contains the job titles
with duplicates, B1:B100 contains the salaries. You could put all of the job
titles in column C starting with C1, just once, no duplicates, sort them
alphabetically. Then in column D1 put the formula
=AVERAGEIF($A$1:$A$100,C1,$B$1:$B$100) and drag the formula down so that
there is a
formula in each cell in column D for every entry in column C. You will get a
"Formula omits adjacent cells error" for the formulas. Just clear the error
as it's meaningless in this case.

 

[Peo Sjoblom]

AVERAGEIF is only for Excel 2007

 

[Bob Phillips]

AVERAGEIF is only for Excel 2007

no matter how many times he says it ...

 

[Dave Thomas]

An AVERAGE function may help you. Example: A1:A100 contains the job titles
with duplicates, B1:B100 contains the salaries. You could put all of the job
titles in column C starting with C1, just once, no duplicates, sort them
alphabetically. Then in column D1 put the formula
=AVERAGE(IF(C1=$A$1:$A$10,$B$1:$B$10)) and press CRTL+SHIFT+ENTER
to enter the formula as an array formula and drag the formula down so that
there is formula in each cell in column D for every entry in column C.

 

[Dave Thomas]

An AVERAGE function may help you. Example: A1:A100 contains the job titles
with duplicates, B1:B100 contains the salaries. You could put all of the
job
titles in column C starting with C1, just once, no duplicates, sort them
alphabetically. Then in column D1 put the formula
=AVERAGE(IF(C1=$A$1:$A$100,$B$1:$B$100)) and press CRTL+SHIFT+ENTER
to enter the formula as an array formula and drag the formula down so
that
there is formula in each cell in column D for every entry in column C.