Consulting

Results 1 to 9 of 9

Thread: "You've entered too many arguments for this function"

  1. 06-11-2018, 07:14 AM #1
    JAMM302
    JAMM302 is offline
    VBAX Regular
    Joined
    Jun 2018
    Posts
    6
    Location

    "You've entered too many arguments for this function"

    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!
    Reply With Quote Reply With Quote
  2. 06-11-2018, 10:30 AM #2
    offthelip
    offthelip is offline
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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
    Reply With Quote Reply With Quote
  3. 06-11-2018, 10:41 AM #3
    JAMM302
    JAMM302 is offline
    VBAX Regular
    Joined
    Jun 2018
    Posts
    6
    Location
    Thank you. Which 0 are you referring to?
    Reply With Quote Reply With Quote
  4. 06-11-2018, 11:16 AM #4
    offthelip
    offthelip is offline
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    ,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
    Reply With Quote Reply With Quote
  5. 06-11-2018, 11:48 AM #5
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,726
    Location
    Picking nits here, but VLookup takes 4 parameters, but the 4th is optional and defaulted to TRUE = Inexact match

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  6. 06-11-2018, 05:08 PM #6
    SamT
    SamT is offline
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ
    Reply With Quote Reply With Quote
  7. 06-11-2018, 05:26 PM #7
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,726
    Location
    I'd suggest a User Defined Function

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  8. 06-12-2018, 01:22 AM #8
    Aflatoon
    Aflatoon is offline
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Cross-posted here, BTW: https://www.excelforum.com/excel-for...-function.html
    Be as you wish to seem
    Reply With Quote Reply With Quote
  9. 06-12-2018, 04:47 AM #9
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules