Help with a nested function

[Bmaho]

I have a project I'm working on for class. I have a workbook with two sheets, one is a database and the other is where I am submitting my answers. The database is a list of employees with that looks like this, but longer:

Emp ID Last Name First Name Department Division Salary Start Date Birth Date Age Years In Service 1011 Gorton Hazel Accounting Copier $27,597.85 2/3/2000 11/21/1964 53 18 1012 Preston Liza Engineering Printer $43,394.15 1/26/2006 12/2/1984 33 12 1041 Tercan Robert R and D Printer $28,043.68 4/16/2002 9/21/1965 52 16 <colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody> </tbody> I am recording a macro that will allow a user to calculate the percentage of employees from a specific department that have salaries over $45,000 among all employees in the database. Is there a nested function that will allow me to calculate this to the answer sheet?

<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[palaeontology]

if the sample data you provided sat in the range A1:J4, then ...

in N2 enter the following as an array formula (ctrl, shift, enter) ... =INDEX($D$2:$D$4,MATCH(0,COUNTIF($N$1:N1,$D$2:$D$4),0)) ... you'll need to change $D$4 to whatever cell is the bottom of your list, and drag down as far as is needed to display all possible department names

in O2 enter the following ... =COUNTIFS($D$2:$D$4,N2,$F$2:$F$4,">45,000")/COUNTIF($D$2:$D$4,N2) .. you'll ned to change $D$4 and $F$4 to whatever cell is at the bottom of your list, and drag down as far as you need

be sure to format column O as percentage

Kind regards,

Chris

 

[Bmaho]

Hi Chris,

Thank you for your help, it is very much appreciated. I am not familiar with the index function. I am going to try the way you mentioned right now, but is there a way to do it using a nested function with DGET, or a nested IF function?

if the sample data you provided sat in the range A1:J4, then ...

in N2 enter the following as an array formula (ctrl, shift, enter) ... =INDEX($D$2:$D$4,MATCH(0,COUNTIF($N$1:N1,$D$2:$D$4),0)) ... you'll need to change $D$4 to whatever cell is the bottom of your list, and drag down as far as is needed to display all possible department names

in O2 enter the following ... =COUNTIFS($D$2:$D$4,N2,$F$2:$F$4,">45,000")/COUNTIF($D$2:$D$4,N2) .. you'll ned to change $D$4 and $F$4 to whatever cell is at the bottom of your list, and drag down as far as you need

be sure to format column O as percentage

Kind regards,

Chris