Dear All,

I try to use averahe function to get result for average salary for each job
position,with data as follows:


Row1 Col A Col B Col C
Row2 Empl_ID Position Salary
Row3 10 Junior 10,000.00
Row4 11 Senior 25,000.00
Row5 12 Junior 12,000.00
Row6 13 Senior 27,500.00
Row7 14 Junior 11,000.00
Row8 15 Senior 28,000.00
Row9 16 Manager 50,000.00
Row10 17 Senior 29,000.00
Row11 18 Manager 55,000.00
Row12 19 Junior 30,000.00
Row13 20 Manager 65,000.00



Average Salary Summary

Junior 15,750.00 ---How to modify Average Function to get
average for "Junior" without sort above data?

Senior 27,375.00

Manager 56,666.67


Appreciate any advise or suggestion, so I can use modified/advance "average
function" that enable to calculate that average salary without sorting above
data

Many thanks for your help,

PA

Hello,

=sumproduct(--(B3:B13="Junior"),
C3:C13)/sumproduct(--(B3:B13="Junior"))

for Juniors, for example. The first sumproduct calculates the sum of
all Junior salaries and the second the count.

HTH,
Bernd

=AVERAGE(IF(B1:B20="Junior",C1:C20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"PA" wrote in message
...
Dear All,

I try to use averahe function to get result for average salary for each
job
position,with data as follows:


Row1 Col A Col B Col C
Row2 Empl_ID Position Salary
Row3 10 Junior 10,000.00
Row4 11 Senior 25,000.00
Row5 12 Junior 12,000.00
Row6 13 Senior 27,500.00
Row7 14 Junior 11,000.00
Row8 15 Senior 28,000.00
Row9 16 Manager 50,000.00
Row10 17 Senior 29,000.00
Row11 18 Manager 55,000.00
Row12 19 Junior 30,000.00
Row13 20 Manager 65,000.00



Average Salary Summary

Junior 15,750.00 ---How to modify Average Function to get
average for "Junior" without sort above data?

Senior 27,375.00

Manager 56,666.67


Appreciate any advise or suggestion, so I can use modified/advance
"average
function" that enable to calculate that average salary without sorting
above
data

Many thanks for your help,

PA

Dear Bob and Bern,

Many thanks for your help.

Best Regards

PA

"Bob Phillips" wrote:

=AVERAGE(IF(B1:B20="Junior",C1:C20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"PA" wrote in message
...
Dear All,

I try to use averahe function to get result for average salary for each
job
position,with data as follows:


Row1 Col A Col B Col C
Row2 Empl_ID Position Salary
Row3 10 Junior 10,000.00
Row4 11 Senior 25,000.00
Row5 12 Junior 12,000.00
Row6 13 Senior 27,500.00
Row7 14 Junior 11,000.00
Row8 15 Senior 28,000.00
Row9 16 Manager 50,000.00
Row10 17 Senior 29,000.00
Row11 18 Manager 55,000.00
Row12 19 Junior 30,000.00
Row13 20 Manager 65,000.00



Average Salary Summary

Junior 15,750.00 ---How to modify Average Function to get
average for "Junior" without sort above data?

Senior 27,375.00

Manager 56,666.67


Appreciate any advise or suggestion, so I can use modified/advance
"average
function" that enable to calculate that average salary without sorting
above
data

Many thanks for your help,

PA