+ Reply to Thread
Results 1 to 4 of 4

"Too many arguments for this function" help with nested IF statements

  1. #1
    Registered User
    Join Date
    09-07-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    2

    "Too many arguments for this function" help with nested IF statements

    Good morning!

    Would love your help with this issue. I'm trying to create a formula that
    will evaluate a number based on whether it fits into one of five
    buckets and then apply the appropriate formula to calculate the
    resulting credit value I'm seeking.

    N = The number being evaluated (referred to in my formula below as
    final!IJ3).

    I have three sheets in the same Workbook:

    Formula sheet is where this formula resides
    Final sheet is where the N data is pulled from
    Background sheet is where all of the calculations to derive the needed
    information are determined.

    On to the buckets....

    Bucket 1: N is zero or empty cell = ""

    Bucket 2: N is greater than zero but less than baseline (baseline =
    background!IJ$348) = base credit (base credit = background!IJ$346)

    Bucket 3: N is greater than or equal to baseline but less than the
    average (average = background!IJ$336) = base credit + a portion of
    half the available performance credit (performance credit = background!
    J342). Portion of performance credit awarded is calculated by where N
    falls on a percent scale where baseline = 0% and average = 100%). 100%
    = half of the performance credit.

    Bucket 4: N is greater than or equal to the average but less than the
    max (max = background!IJ$349) = base credit + half of the performance
    credit + Portion of the second half of the performance credit awarded
    calculating where N falls on a percent scale where average = 0% and
    max = 100%).

    Bucket 5: N is greater than or equal to the max = base credit + full
    performance credit.

    I wrote out this formula and I thought it was going to work fine, but
    then I got the dreaded "too many arguments fro this function" error
    when approaching the end of the formula (specifically the last IF
    statement). Any help in making this work would be INCREDIBLY
    appreciated as I'm working with small nonprofit and we are under
    intense time pressure for this project.

    Would love ideas on how to shorten it and make it work. If certain
    functions need to be handled outside of the formula, I have room in my
    background sheet to handle those calculations. For your info, this
    formula will be applied to about 300 cells in the IJ column of the
    Formulas sheet. Here is the formula I wrote.

    =IF(AND(final!IJ3>0,final!IJ3<background!IJ$348),((background!IJ$343+background!IJ$344)*background!I J$345),IF(AND(final!IJ3>=background!IJ$348,final!IJ3<background!IJ$336),(((final!IJ3-
    background!IJ$348)/(background!IJ$336-background!IJ$348))*(background! IJ$342)/2)+background!$IJ$346),IF(final!IJ3>background!IJ348,(background!IJ$343+background!IJ$344)),"")))


    ******the formula works fine until this point, then gives me the too
    many arguments error.

    I have attached the .xlsx to make things a bit easier.

    Thank you!
    Mark

    Key

    final!IJ3 = number being evaluated
    background!IJ$336 = average
    background!IJ$343 = total credit (base credit + performance credit)
    background!IJ$344 = extra credit
    background!IJ$345 = ratio of base credit (e.g. ratio of base credit *
    total credit = base credit)
    background!IJ$346 = base credit
    background!IJ$348 = baseline
    background!IJ349 = max
    Attached Files Attached Files
    Last edited by Mark O; 09-07-2009 at 04:27 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: "Too many arguments for this function" help with nested IF statements

    that last bit should be
    IF(final!IJ3>=background!IJ$349,
    background!IJ$343+background!IJ$344,""
    then as many ) as you have if's
    IF(final!IJ3>=background!IJ$349,
    background!IJ$343+background!IJ$344,"")))
    "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

  3. #3
    Registered User
    Join Date
    09-07-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: "Too many arguments for this function" help with nested IF statements

    Thank you for the suggestion. Good catch although unfortunately I only made the error in my posting and not in the actual situation. I tried entering the appropriate number of ")" and regardless of what I do "))" at the end, ")))" at the end, "))))" at the end, I have no luck and get the same stupid "Too many arguments for this function" pop-up error from Excel 2007.

    Here is my full formula with the correct number of parentheses. Any other ideas?

    =IF(AND(final!IJ3>0,final!IJ3<background!IJ$348),((background!IJ$343+background!IJ$344)*background!IJ$345),IF(AND(final!IJ3>=background!IJ$348,final!IJ3<background!IJ$336),(((final!IJ3-
    background!IJ$348)/(background!IJ$336-background!IJ$348))*(background! IJ$342)/2)+background!$IJ$346),IF(final!IJ3>background!IJ348,(background!IJ$343+background!IJ$344)),"")))


    Quote Originally Posted by martindwilson View Post
    that last bit should be
    IF(final!IJ3>=background!IJ$349,
    background!IJ$343+background!IJ$344,""
    then as many ) as you have if's
    IF(final!IJ3>=background!IJ$349,
    background!IJ$343+background!IJ$344,"")))

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: "Too many arguments for this function" help with nested IF statements

    try
    =IF(AND(final!IJ3>0,final!IJ3<background!IJ$348),((background!IJ$343+background!IJ$344)*background!IJ$345),IF(AND(final!IJ3>=background!IJ$348,final!IJ3<background!IJ$336),((((final!IJ3-
    background!IJ$348)/(background!IJ$336-background!IJ$348))*(background!IJ$342)/2)+background!$IJ$346),IF(final!IJ3>=background!IJ$349,
    background!IJ$343+background!IJ$344,""
    )))

    extra ( inserted here
    ,((((final!IJ3-

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1