ExcelBanter - average in excle

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - average in excle (https://www.excelbanter.com/excel-worksheet-functions/150400-average-excle.html)

average in excle

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?

Toppers

average in excle

=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

" wrote:

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?

Bob Phillips

average in excle

=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)

"Toppers" wrote in message
...

=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

" wrote:

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?

average in excle

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

" wrote:

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?

Dave Thomas

average in excle

Dave Thomas

average in excle

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" wrote in message
et...
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.
.

wrote in message
ps.com...
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?

Dave Thomas

Correction to original post.

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.

"Dave Thomas" wrote in message
et...
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.
.

wrote in message
ps.com...
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?

Peo Sjoblom

Correction to original post.

AVERAGEIF is only for Excel 2007

--
Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
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.

"Dave Thomas" wrote in message
et...
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.
.

wrote in message
ps.com...
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?

Bob Phillips

Correction to original post.

"Peo Sjoblom" wrote in message
...
AVERAGEIF is only for Excel 2007

no matter how many times he says it ...

Dave Thomas

average in excle - works in 2007 and prior.

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.

wrote in message
ps.com...
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?

Dave Thomas

average in excle - works in 2007 and prior - Address correction

"Dave Thomas" wrote in message
. net...
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.

wrote in message
ps.com...
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?

All times are GMT +1. The time now is 06:34 PM.