# I've entered too many arguments for function

#### jricks

##### New Member
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### AlanY

##### Well-known Member
could you explain what's the formula supposed to do?

#### BarryL

##### Well-known Member
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.

#### offthelip

##### Well-known Member
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.

#### jricks

##### New Member
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.

#### offthelip

##### Well-known Member
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:

#### offthelip

##### Well-known Member
I for got to add the D13/1000 bit to it , but you should be able to work out that!!

1,102,782
Messages
5,488,850
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...