I've entered too many arguments for function

jricks

New Member
Joined
Dec 19, 2018
Messages
2
Can someone please take a look at this formula and help me figure out what is wrong or maybe a better way to accomplish this? Thanks.

=IF(AND(D13<$A$5, Recruitment_Package!$I$9={"In Bank Footprint"}), (D13*$B$5)/10000, (D13*$D$5)/10000,
IF(AND(D13<$A$6, Recruitment_Package!$I$9={"In Bank Footprint"}), (D13*$B$6)/10000, (D13*$D$6)/10000,
IF(AND(D13<$A$5, Recruitment_Package!$I$9={"In Bank Footprint"}),(D13*$B$7)/10000, (D13*$D$7)/10000,
IF(AND(D13<$A$8, Recruitment_Package!$I$9={"In Bank Footprint"}),(D13*$D$8)/10000,(D13*$D$8)/10000,
IF(AND(D13<$A$9,Recruitment_Package!$I$9={"In Bank Footprint"}),(D13*$B$9)/10000, (D13*$D$9)/10000 )))))
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
could you explain what's the formula supposed to do?
 
Upvote 0
in all of your IFs you are providing logic, false values and true values. you cannot then add another IF to the end as you have run out of arguments.

something like
=
IF(AND(D13<$A$5, Recruitment_Package!$I$9="In Bank Footprint"), (D13*$B$5)/10000, (D13*$D$5)/10000)&IF(AND(D13<$A$6, Recruitment_Package!$I$9="In Bank Footprint"), (D13*$B$6)/10000, (D13*$D$6)/10000)&
IF(AND(D13<$A$5, Recruitment_Package!$I$9="In Bank Footprint"),(D13*$B$7)/10000, (D13*$D$7)/10000) &
IF(AND(D13<$A$8, Recruitment_Package!$I$9="In Bank Footprint"),(D13*$D$8)/10000,(D13*$D$8)/10000)&
IF(AND(D13<$A$9,Recruitment_Package!$I$9="In Bank Footprint"),(D13*$B$9)/10000, (D13*$D$9)/10000 )

would work but it will just give you back to back numbers. it really depends on what you are trying to achieve, which you need to tell us.
 
Upvote 0
One way of simplifying equations is to look to see what is common and move to the "outside" if statement. what is common between all the "and" statements is:
Recruitment_Package!$I$9={"In Bank Footprint"}
So you could move this check to an "if" statement surrounding all the other checks, so you only do it once.
I can't help further because as the others have said we don't know what you are trying to do.
 
Upvote 0
Can someone please take a look at this formula and help me figure out what is wrong or maybe a better way to accomplish this? Thanks.

=IF(AND(D13<$A$5, Recruitment_Package!$I$9={"In Bank Footprint"}), (D13*$B$5)/10000, (D13*$D$5)/10000,
IF(AND(D13<$A$6, Recruitment_Package!$I$9={"In Bank Footprint"}), (D13*$B$6)/10000, (D13*$D$6)/10000,
IF(AND(D13<$A$5, Recruitment_Package!$I$9={"In Bank Footprint"}),(D13*$B$7)/10000, (D13*$D$7)/10000,
IF(AND(D13<$A$8, Recruitment_Package!$I$9={"In Bank Footprint"}),(D13*$D$8)/10000,(D13*$D$8)/10000,
IF(AND(D13<$A$9,Recruitment_Package!$I$9={"In Bank Footprint"}),(D13*$B$9)/10000, (D13*$D$9)/10000 )))))

To explain what I'm trying to do:
I have a list of values in A5, A6, A7, A8, and A9(500,001 750,001 1,000,001 1,500,001 and>1,500,001)
I want to look at the number in D13 and see if it is < those value and keep looking until it falls in one of those categories.
There are also the condition that if a drop down is selected "In Bank Footprint" then it will calculate D13*B5/1000, but if not then calculate D13*D5/10000 and so on. Hope that helps and makes sense.
 
Upvote 0
If you change the list of numbers in A5 to A9 to include a zero first going up to 1500000, then this code should do what you want:
Code:
=INDEX(B5:D9,MATCH(D13,A5:A9,1),IF(Recruitment_Package!$I$9={"In Bank Footprint"},1,3))
Note, I can't test your "Recruitment_Package!$I$9={"In Bank Footprint"}" statement so I don't know whetehr that will work, the curly brackets??
 
Last edited:
Upvote 0
I for got to add the D13/1000 bit to it , but you should be able to work out that!!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

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