Lookup to return count, median, lower quartile, upper quartile&ave

[Guest]

Hello,
Can anyone help with a spreadsheet that I am developing..
I have a list employee data with unique job codes and associated salary data
eg

AD100 Administration Manger $54,000

We have lots of entries for each job from different companies and 250 unique
job codes so I have set up a seperate spreadsheet ending up with thousands of
lines of data. I have set up another tab on the spreadsheet with the job
codes and now want to return one value that tells the count of entries,
median, lower quartile, upper quartile and average for each of those job
codes.
What is the best approach for this? Do I need to step the process?
Appreciate your help. Thanks
B

 

[Guest]

Here's a way to quickly set it up ..

Assume source data is in a sheet: x,
job codes in A2 down, descriptions in B2 down, salary in C2 down

In a new sheet,
assume the unique job codes are listed in A2 down

In B2:
=COUNTIF(x!A$2:A$1000,A2)

In C2, array-entered**:
=MEDIAN(IF(x!A$2:A$1000=A2,x!C$2:C$1000))

In D2, array-entered**:
=QUARTILE(IF(x!A$2:A$1000=A2,x!C$2:C$1000),1)

In E2, array-entered**:
=QUARTILE(IF(x!A$2:A$1000=A2,x!C$2:C$1000),3)

In F2, array-entered**:
=AVERAGE(IF(x!A$2:A$1000=A2,x!C$2:C$1000))

Select B2:F2, copy down as far as required. Cols B to F will return the
required count of entries, median, lower quartile (25th percentile), upper
quartile (75th percentile) and average for each of the unique job codes in
col A. Adapt the ranges to suit the actual extent of source data in x.

**"Array-entered" means to press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER.

 

[Guest]

thanks Max, I'll give that a go. Cheers

Here's a way to quickly set it up ..

Assume source data is in a sheet: x,
job codes in A2 down, descriptions in B2 down, salary in C2 down

In a new sheet,
assume the unique job codes are listed in A2 down

In B2:
=COUNTIF(x!A$2:A$1000,A2)

In C2, array-entered**:
=MEDIAN(IF(x!A$2:A$1000=A2,x!C$2:C$1000))

In D2, array-entered**:
=QUARTILE(IF(x!A$2:A$1000=A2,x!C$2:C$1000),1)

In E2, array-entered**:
=QUARTILE(IF(x!A$2:A$1000=A2,x!C$2:C$1000),3)

In F2, array-entered**:
=AVERAGE(IF(x!A$2:A$1000=A2,x!C$2:C$1000))

Select B2:F2, copy down as far as required. Cols B to F will return the
required count of entries, median, lower quartile (25th percentile), upper
quartile (75th percentile) and average for each of the unique job codes in
col A. Adapt the ranges to suit the actual extent of source data in x.

**"Array-entered" means to press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER.

 

[Max]

welcome, Bee.

 

[Guest]

HI Max or anyone, can you help me with another formula?
I've introduced another variable which is a benefit that some employees have
eg Company Car. Not everyone has it but how do I count the number of
employees in each job code who have a company car. I tried using the
SUMPRODUCT AND THE COUNT FUNCTION with no luck,
I tried this
=COUNT(IF('Data Input Sheet'!$D$4:$D$10000=$A4,'Data Input
Sheet'!$X$4:$X$10000))

Data input sheet is where all the employee data is
Column D is job code
Column X details the value of the car, if they don't ahve one, it shows as
blank
Cell A4 is the job code reference.
Hope that make sense.
Thanks

 

[Roger Govier]

Hi Bee

Try
=COUNTIF('Data Input Sheet'!$D$4:$D$10000,$A4,'Data Input
Sheet'!$X$4:$X$10000))

 

[Max]

Think you could also try, normal ENTER:
=SUMPRODUCT(('Data Input Sheet'!$D$4:$D$10000=$A4)*('Data Input
Sheet'!$X$4:$X$10000<>""))

 

[Guest]

Thank you again, that worked a treat!

Think you could also try, normal ENTER:
=SUMPRODUCT(('Data Input Sheet'!$D$4:$D$10000=$A4)*('Data Input
Sheet'!$X$4:$X$10000<>""))

 

[Max]

welcome. glad it worked for you

 

[Guest]

HI Max,
Is there any way of excluding cells that are blank.All the UQ, LQ, Median
and average are being assessed across all the cells in the range.
Thanks
B

 

[Max]

=MEDIAN(IF(x!A$2:A$1000=A2,x!C$2:C$1000))

Try adding the extra condition like this into the array:
=MEDIAN(IF((x!A$2:A$1000=A2)*(x!C$2:C$1000<>""),x!C$2:C$1000))