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:
[TABLE="width: 711"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 711"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Emp ID[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Department[/TD]
[TD]Division[/TD]
[TD]Salary[/TD]
[TD]Start Date[/TD]
[TD]Birth Date[/TD]
[TD]Age[/TD]
[TD]Years In Service[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD]Gorton[/TD]
[TD]Hazel[/TD]
[TD]Accounting[/TD]
[TD]Copier[/TD]
[TD="align: right"]$27,597.85 [/TD]
[TD="align: right"]2/3/2000[/TD]
[TD="align: right"]11/21/1964[/TD]
[TD]53[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]1012[/TD]
[TD]Preston[/TD]
[TD]Liza[/TD]
[TD]Engineering[/TD]
[TD]Printer[/TD]
[TD="align: right"]$43,394.15 [/TD]
[TD="align: right"]1/26/2006[/TD]
[TD="align: right"]12/2/1984[/TD]
[TD]33[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1041[/TD]
[TD]Tercan[/TD]
[TD]Robert[/TD]
[TD]R and D[/TD]
[TD]Printer[/TD]
[TD="align: right"]$28,043.68 [/TD]
[TD="align: right"]4/16/2002[/TD]
[TD="align: right"]9/21/1965[/TD]
[TD]52[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

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?

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[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