PDA

View Full Version : "You've entered too many arguments for this function"



JAMM302
06-11-2018, 07:14 AM
Hello....I have run into some issues while creating an Excel formula. I have created the following Excel formula (please see below) and have received an error message that "You've entered too many arguments for this function."



=IF(D8>=1,
IF(B8>=1,
IF('Client Input - Step 1'!C4='Client Input - Step 1'!AD4,VLOOKUP('Client Input - Step 1'!C4,Pricing!G5:L5,3,0)*1.1+IF(D8>1,((D8-1)*Pricing!C9)),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC6,VLOOKUP('Client Input - Step 1'!C7,Pricing!B51:F54,3,0)*1.1+IF(D8>1,((D8-1)*Pricing!C9),0)),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC4,(VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)*1.1+IF(D8>1,((D8-1)*Pricing!C9),0)),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC5,(VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)*1.1+IF(D8>1,((D8-1)*Pricing!C9))),0)))),


IF('Client Input - Step 1'!C4='Client Input - Step 1'!AD4,VLOOKUP('Client Input - Step 1'!C4,Pricing!G5:L5,4,0)+IF(D8>1,((D8-1)*Pricing!C9)),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC6,VLOOKUP('Client Input - Step 1'!C7,Pricing!B51:F54,3,0)+IF(D8>1,((D8-1)*Pricing!C9),0)),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC4,(VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)+IF(D8>1,((D8-1)*Pricing!C9),0)),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC5,(VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)+IF(D8>1,((D8-1)*Pricing!C9))),0))))),0)




I'm sure I messed up the parentheses somewhere, but am having some difficulties determining exactly where. Any assistance the group can provide would be greatly appreciated.


Thanks in advance!

offthelip
06-11-2018, 10:30 AM
I think you need to get rid of the ",0" Vlookup only take 3 parameters, the value, the aray and the column number you are putting in an extra parameter which is the ,0

JAMM302
06-11-2018, 10:41 AM
Thank you. Which 0 are you referring to?

offthelip
06-11-2018, 11:16 AM
,VLOOKUP('Client Input - Step 1'!C4,Pricing!G5:L5,3,0)
this one
and all the ones in a similar positin in the lines below

Paul_Hossler
06-11-2018, 11:48 AM
Picking nits here, but VLookup takes 4 parameters, but the 4th is optional and defaulted to TRUE = Inexact match

22407

SamT
06-11-2018, 05:08 PM
Too many nested IFs in Excel < 2007. Up to 2007, you could only nest 7 IFs in one formula. 2007 and later, you can nest 64 IFs.

If I ever saw a place for Named Formulas and Named Ranges, that Formula is it. It reminds me again why I use User Defined Worksheet Object Properties and Methods instead of Excel Formulas.

Paul_Hossler
06-11-2018, 05:26 PM
I'd suggest a User Defined Function

But without seeing what it's supposed to do, I can't come up with an example

Aflatoon
06-12-2018, 01:22 AM
Cross-posted here, BTW: https://www.excelforum.com/excel-formulas-and-functions/1234050-youve-entered-too-many-arguments-for-this-function.html

Bob Phillips
06-12-2018, 04:47 AM
Is this what you want


=IF(D8>=1,
IF(B8>=1,
IF('Client Input - Step 1'!C4='Client Input - Step 1'!AD4,VLOOKUP('Client Input - Step 1'!C4,Pricing!G5:L5,3,0)*1.1+IF(D8>1,(D8-1)*Pricing!C9),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC6,VLOOKUP('Client Input - Step 1'!C7,Pricing!B51:F54,3,0)*1.1+IF(D8>1,(D8-1)*Pricing!C9,0),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC4,VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)*1.1+IF(D8>1,(D8-1)*Pricing!C9,0),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC5,VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)*1.1+IF(D8>1,(D8-1)*Pricing!C9,0))))),

IF('Client Input - Step 1'!C4='Client Input - Step 1'!AD4,VLOOKUP('Client Input - Step 1'!C4,Pricing!G5:L5,4,0)+IF(D8>1,(D8-1)*Pricing!C9),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC6,VLOOKUP('Client Input - Step 1'!C7,Pricing!B51:F54,3,0)+IF(D8>1,(D8-1)*Pricing!C9,0),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC4,VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)+IF(D8>1,(D8-1)*Pricing!C9,0),
IF('Client Input - Step 1'!AA5='Client Input - Step 1'!AC5,VLOOKUP('Client Input - Step 1'!C7,Pricing!B5:F8,3,0)+IF(D8>1,(D8-1)*Pricing!C9,0)))))),0)