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

D

Destined

New Member
Joined
Dec 11, 2015
Messages
6
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:
[TABLE="width: 1037"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]Faculty Rankings
[/TD]
[TD]Min Credit Weight
[/TD]
[TD]Max Credit Weight
[/TD]
[TD]Min. Length of Service
[/TD]
[TD]Max. Length of Service
[/TD]
[TD]Min. Student Enrollment
[/TD]
[TD]Max. Student Enrollment
[/TD]
[TD]Salaries
[/TD]
[/TR]
</tbody>[/TABLE]
FacultyData Worksheet:
[TABLE="width: 1211"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Faculty Ranking
[/TD]
[TD]Length of Service
[/TD]
[TD]Student Enrollment
[/TD]
[TD]Credit
[/TD]
[TD]Salary
[/TD]
[TD]Saln
[/TD]
[TD]Firstname
[/TD]
[TD]Lastname
[/TD]
[TD]EMP#
[/TD]
[TD]SSN
[/TD]
[TD]Dept.
[/TD]
[TD]Course #
[/TD]
[TD]Section
[/TD]
[TD]Term
[/TD]
[/TR]
</tbody>[/TABLE]

thanks much
Destined
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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")
 
Upvote 0
Thank you for picking up that error!
destined

MarcelBeug said:
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")
Click to expand...
 
Upvote 0
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")
 
Upvote 0
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:
[TABLE="width: 689"]
<tbody>[TR]
[TD="class: xl68, width: 188"]Faculty Rankings[/TD]
[TD="class: xl68, width: 60"]Credit [/TD]
[TD="class: xl69, width: 96"]Min. Length of Service[/TD]
[TD="class: xl69, width: 99"]Max. Length of Service[/TD]
[TD="class: xl69, width: 88"]Min. Student Enrollment[/TD]
[TD="class: xl69, width: 89"]Max. Student Enrollment[/TD]
[TD="class: xl70, width: 69"]Salaries
[/TD]
[/TR]
</tbody>[/TABLE]

FacultyData worksheet:
[TABLE="width: 630"]
<tbody>[TR]
[TD="class: xl68, width: 135"]Faculty Ranking[/TD]
[TD="class: xl68, width: 63"]Length of Service[/TD]
[TD="class: xl68, width: 72"]Student Enrollment [/TD]
[TD="class: xl72, width: 44"] Credit[/TD]
[TD="class: xl69, width: 80"] Salary[/TD]
[TD="class: xl68, width: 39"]Saln[/TD]
[TD="class: xl68, width: 99"]Firstname[/TD]
[TD="class: xl68, width: 98"]Lastname

[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for helping,

Destined
 
Upvote 0
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:

[TABLE="width: 487"]
<colgroup><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD]Faculty Rankings[/TD]
[TD]Credit Weight[/TD]
[TD]Min. Length of Service[/TD]
[TD]Max. Length of Service[/TD]
[TD]Min. Student Enrollment[/TD]
[TD]Max. Student Enrollment[/TD]
[TD]Salaries[/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Senior[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] xxxx [/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD] xxxx



[/TD]
[/TR]
</tbody>[/TABLE]
THanks,
Destined
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Automation Error - possibly born from my complete lack of understanding with Error Handling
2 3
Replies
23
Views
8K
RockandGrohl
R
S
  • Question Question
Nested IF functions giving Too Many Arguments error
Replies
1
Views
1K
Stuepef
S
L
  • Question Question
Excel says I entered too many arguments
Replies
6
Views
708
Luaptar
L
M
  • Question Question
Grid Sum (it could be all way possible from min to max)
Replies
4
Views
631
motilulla
M
V
  • Question Question
Simplyfying Nested IFs, Index and Match Can I improve my current workbook ?
Replies
0
Views
543
Vince1199s
V

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top