Modified Average Function

[PA]

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

[[email protected]]

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

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>