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

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

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote:
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

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

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

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote:
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

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

welcome, Bee.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote in message
...
thanks Max, I'll give that a go. Cheers

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

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

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote:
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

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

Hi Bee

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


--
Regards
Roger Govier



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

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote:
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

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

Think you could also try, normal ENTER:
=SUMPRODUCT(('Data Input Sheet'!$D$4:$D$10000=$A4)*('Data Input
Sheet'!$X$4:$X$10000<""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote in message
...
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

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

welcome. glad it worked for you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote in message
...
Thank you again, that worked a treat!

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

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" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote:
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

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bee" wrote in message
...
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