| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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(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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correction to original post.
"Peo Sjoblom" wrote in message ... AVERAGEIF is only for Excel 2007 no matter how many times he says it ... |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| why ctrl+ home is not working in excle 2003? | Excel Discussion (Misc queries) | |||
| calendar control excle 2003 | Excel Worksheet Functions | |||
| How do I set up Auto sequence number in excle | Charts and Charting in Excel | |||
| Excle should allow define a fix exponent in scientific notation | Excel Discussion (Misc queries) | |||
| help in excle setting | New Users to Excel | |||
Linear Mode
