+ Reply to Thread
Results 1 to 16 of 16

[IF/AND] You've entered too many arguments for this function.

  1. 03-21-2013, 06:15 PM #1
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    [IF/AND] You've entered too many arguments for this function.

    Hi all,

    I'm trying to generate a score based on two criteria. However the tricky part is that there exists a negative relationship between two criteria.

    I've come up with this two piece of formulas... However Excel wouldn't let me combine these two and says this error message "You've entered too many arguments for this function."

    The two functions are:

    IF(AND(G5="A",H5>=3),"10",IF(AND(G5="A",H5=2),"9",IF(AND(G5="A",H5=1),"8",IF(AND(G5="B",H5>=3),"7",IF(AND(G5="B",H5=2),"6",IF(AND(G5="B",H5=1),"5","False")))))

    AND

    =IF(AND(G5="C",H5>=3),"2",IF(AND(G5="C",H5=2),"3",IF(AND(G5="C",H5=1),"4",IF(AND(G5="D",H5>=2),"0",IF(AND(G5="D",H5=1),"1","False")))))


    Your assistance would be greatly appreciated!!!

    I'm including a screenshot for better understanding. Thank you!

    problem.png
    Reply With QuoteRegister To Reply
  2. 03-21-2013, 06:28 PM #2
    Tank997
    Tank997 is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: [IF/AND] You've entered too many arguments for this function.

    Excel 2007 has a limit of 7 nested IF statements which wold cause the error you're seeing. If you upload a sample spreadsheet maybe there's another way to do what you're asking.
    Tom S.
    ↙ If you find my reply helpful click on the * down there on the left. Yeah that's it, right there, down on the left
    If your question is resolved, mark it SOLVED using the thread tools.
    Reply With QuoteRegister To Reply
  3. 03-21-2013, 06:33 PM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: [IF/AND] You've entered too many arguments for this function.

    well in excel 2007 this works (2007 allows 64 nested if levels)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  4. 03-21-2013, 06:34 PM #4
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: [IF/AND] You've entered too many arguments for this function.

    actually...I'm using Excel 2003. My workplace is kinda obsolated...
    Reply With QuoteRegister To Reply
  5. 03-21-2013, 06:37 PM #5
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: [IF/AND] You've entered too many arguments for this function.

    Error1.png Just tried yours... got this error message. Thanks anyway I appreciate your help!!
    Reply With QuoteRegister To Reply
  6. 03-21-2013, 06:38 PM #6
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: [IF/AND] You've entered too many arguments for this function.

    Just tried yours... got this error message. Thanks anyway I appreciate your help!!

    Error1.png

    Quote Originally Posted by martindwilson View Post
    well in excel 2007 this works (2007 allows 64 nested if levels)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 03-21-2013, 06:39 PM #7
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: [IF/AND] You've entered too many arguments for this function.

    VlookupSolution.xlsx

    Would using a Vlookup improve this for you? The attached is an example of how to use this (Col H and Col G are called so to refer to you original formula if that makes sense).
    Say thanks, click *
    Reply With QuoteRegister To Reply
  8. 03-21-2013, 06:40 PM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: [IF/AND] You've entered too many arguments for this function.

    there must be a better way of writing it but a work around would be to put the first formula in say b1 and the put the second part in c1 like this

    b1 =
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    c1=
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 03-21-2013, 06:41 PM #9
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: [IF/AND] You've entered too many arguments for this function.

    How about this:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
    Reply With QuoteRegister To Reply
  10. 03-21-2013, 06:43 PM #10
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: [IF/AND] You've entered too many arguments for this function.

    Works like a magic... Holy.... Thank you so much mate

    I would also like to thanks to everyone in this thread.

    You guys are wonderful.

    Quote Originally Posted by ConneXionLost View Post
    How about this:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 03-21-2013, 06:45 PM #11
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: [IF/AND] You've entered too many arguments for this function.

    and i did say in excel 2007 it wont work as one formula in excel 2003
    here it is working in 2007
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  12. 03-21-2013, 06:46 PM #12
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: [IF/AND] You've entered too many arguments for this function.

    told you there was a better way!
    Reply With QuoteRegister To Reply
  13. 03-21-2013, 06:51 PM #13
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: [IF/AND] You've entered too many arguments for this function.

    Thank you so much I really appreciate your help as well!!

    Quote Originally Posted by martindwilson View Post
    told you there was a better way!
    Reply With QuoteRegister To Reply
  14. 03-21-2013, 06:54 PM #14
    Sean Thomas
    Sean Thomas is offline
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Smile Re: [IF/AND] You've entered too many arguments for this function.

    A little late,
    heres using vlookup
    Attached Files Attached Files
    Regards
    Sean

    Please add to my reputation if you think i helped     (click on the star below the post)
    Mark threads as "Solved" if you have your answer     (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:     [code] Your code here [code]
    Please supply a workbook containing example Data:     It makes its easier to answer your problem & saves time!
    Reply With QuoteRegister To Reply
  15. 03-21-2013, 06:59 PM #15
    seaspi
    seaspi is offline
    Registered User
    Join Date
    04-30-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: [IF/AND] You've entered too many arguments for this function.

    Thank you my friend. Really appreciate it!

    Quote Originally Posted by Sean Thomas View Post
    A little late,
    heres using vlookup
    Reply With QuoteRegister To Reply
  16. 03-21-2013, 07:04 PM #16
    Tank997
    Tank997 is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: [IF/AND] You've entered too many arguments for this function.

    Quote Originally Posted by martindwilson View Post
    well in excel 2007 this works (2007 allows 64 nested if levels)
    martindwilson, You're right I was thinking 2003, my mistake. I still sure there are better ways to do these formulas
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

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


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1