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 )))))
 

Some videos you may like

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
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
could you explain what's the formula supposed to do?
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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
Joined
Dec 23, 2017
Messages
1,314
Office Version
2010
Platform
Windows
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
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 )))))

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
Joined
Dec 23, 2017
Messages
1,314
Office Version
2010
Platform
Windows
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
Joined
Dec 23, 2017
Messages
1,314
Office Version
2010
Platform
Windows
I for got to add the D13/1000 bit to it , but you should be able to work out that!!
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top