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
Bookmarks