IFERROR (AVERAGEIFS....) Question - error it says too many arguments

[Destined]

I have this formula and it is telling me too many arguments. How do i fix it? Since i need all these criteria to determine the salaries? Salary is based on student enrollment, ranking, length of service, and credit weight. can someone help?

=IFERROR(AVERAGEIFS('Salary Layout'!H:H,'Salary Layout'!A:A,FacultyData!A3,'Salary Layout'!D:D,"<="& FacultyData!B3,'Salary Layout'!E:E,">="&FacultyData!B3,'Salary Layout'!F:F,"<="&FacultyData!C3,'Salary Layout'!G:G,">="&FacultyData!C3), ‘Salary Layout’!B:B,“<=”&FacultyData!M3,’Salary Layout’!C:C,”>=”&FacultyData!M3),“Unknown”)

My two worksheets are:
Salary Layout worksheet:

A	B	C	D	E	F	G	H
Faculty Rankings	Min Credit Weight	Max Credit Weight	Min. Length of Service	Max. Length of Service	Min. Student Enrollment	Max. Student Enrollment	Salaries

<tbody>
</tbody>

FacultyData Worksheet:

A

B

C

D

E

F

G

H

I

J

K

L

M

Faculty Ranking

Length of Service

Student Enrollment

Credit

Salary

Saln

Firstname

Lastname

EMP#

SSN

Dept.

Course #

Section

Term

<tbody>
</tbody>

thanks much
Destined

 

[MarcelBeug]

The issue is with the parenthesis behind "...C3" and with different kinds of quotes.

Try: =IFERROR(AVERAGEIFS('Salary Layout'!H:H,'Salary Layout'!A:A,FacultyData!A3,'Salary Layout'!D:D,"<="& FacultyData!B3,'Salary Layout'!E:E,">="&FacultyData!B3,'Salary Layout'!F:F,"<="&FacultyData!C3,'Salary Layout'!G:G,">="&FacultyData!C3, 'Salary Layout'!B:B,"<="&FacultyData!M3,'Salary Layout'!C:C,">="&FacultyData!M3),"Unknown")

 

[Destined]

Thank you for picking up that error!
destined

The issue is with the parenthesis behind "...C3" and with different kinds of quotes.

Try: =IFERROR(AVERAGEIFS('Salary Layout'!H:H,'Salary Layout'!A:A,FacultyData!A3,'Salary Layout'!D:D,"<="& FacultyData!B3,'Salary Layout'!E:E,">="&FacultyData!B3,'Salary Layout'!F:F,"<="&FacultyData!C3,'Salary Layout'!G:G,">="&FacultyData!C3, 'Salary Layout'!B:B,"<="&FacultyData!M3,'Salary Layout'!C:C,">="&FacultyData!M3),"Unknown")

 

[MarcelBeug]

You're welcome.

 

[Aladin Akyurek]

Also, if you are running this formula in FacultyData, try to drop references to this sheet...

=IFERROR(AVERAGEIFS('Salary Layout'!H:H,'Salary Layout'!A:A,A3,'Salary Layout'!D:D,"<="&B3,'Salary Layout'!E:E,">="&B3,'Salary Layout'!F:F,"<="&C3,'Salary Layout'!G:G,">="&C3,'Salary Layout'!B:B,"<="&M3,'Salary Layout'!C:C,">="&M3),"Unknown")

 

[Destined]

I have added another criteria, Credit, to my formula so that it will look up the right salary not based on only Ranking, Length of service, Student Enrollment but also Credit but the formula but it didn't give me the right results. Can someone kindly enlightened me? Sorry for the many questions. I am newbie to excel. Excel highlighted the last C4 in the formula that needs fixing! Not sure how to fix it. HELP! HELP!

=IFERROR(AVERAGEIFS('Salary Layout'!G:G,'Salary Layout'!A:A,'Salary Layout'!B:B,FacultyData!A4,'Salary Layout'!C:C,"<="& FacultyData!B4,'Salary Layout'!D:D,">="&FacultyData!B4,'Salary Layout'!E:E,"<="&FacultyData!C4,'Salary Layout'!F:F,">="&FacultyData!C4),"Unknown")

Salary Layout worksheet:

Faculty Rankings

Credit

Min. Length of Service

Max. Length of Service

Min. Student Enrollment

Max. Student Enrollment

Salaries

<tbody>
</tbody>

FacultyData worksheet:

Faculty Ranking

Length of Service

Student Enrollment

Credit

Salary

Saln

Firstname

Lastname

<tbody>
</tbody>

Thanks for helping,

Destined

 

[Aladin Akyurek]

Must column E of Salary Layout <= C4 and column F of Salary Layout >= C4 ?

 

[Destined]

Yes, because i have different ranges for student enrollment e.g. <5, 5-10, 11-15, etc. and same with length of service - <5, 5-10, >10. And credits is between 1-6 but i had layed it out like this in Salary Layout so i didnt use <= or >=. I need to add the credit to the formula as a criteria so that the salary is based on ranking, length of service, student enrollment and credit. I give a small portion to show you how it looks:

Faculty Rankings	Credit Weight	Min. Length of Service	Max. Length of Service	Min. Student Enrollment	Max. Student Enrollment	Salaries
Senior	1	0	4	0	5	xxxx
Senior	1	0	4	6	10	xxxx
Senior	2	0	4	0	5	xxxx
Senior	2	0	4	6	10	xxxx
Senior	3	0	4	0	5	xxxx
Senior	3	0	4	6	10	xxxx
Senior	4	0	4	0	5	xxxx
Senior	4	0	4	6	10	xxxx
Senior	6	0	4	0	5	xxxx
Senior	6	0	4	6	10	xxxx
Junior	1	0	4	0	5	xxxx
Junior	1	0	4	6	10	xxxx
Junior	2	0	4	0	5	xxxx
Junior	2	0	4	6	10	xxxx
Junior	3	0	4	0	5	xxxx
Junior	3	0	4	6	10	xxxx
Junior	4	0	4	0	5	xxxx
Junior	4	0	4	6	10	xxxx
Temporary	1	0	4	0	5	xxxx
Temporary	1	0	4	6	10	xxxx
Temporary	2	0	4	0	5	xxxx
Temporary	2	0	4	6	10	xxxx
Temporary	3	0	4	0	5	xxxx
Temporary	3	0	4	6	10	xxxx
Temporary	4	0	4	0	5	xxxx
Temporary	4	0	4	6	10	xxxx
Temporary	6	0	4	0	5	xxxx

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

THanks,
Destined

 

[Aladin Akyurek]

What do you have in A4, B4, and C4?

 

[Destined]

in FacultyData worksheet i A4 - Ranking (e.g. senior), B4 is length of service (eg. 5 years), c4 is student enrollment (15 students), d4 is credit (2 credits). in Salary Layout A is Ranking, B is credit, C is Min Length of Service, D is Maximum Length of service, E is Min Student Enrollment and F is Max student enrollment. Hope i am answering your question.
Thanks,
D