Hi Mark, Your description is very detailed, but I still get lost in it. Perhaps you could attach the file. Fudge the data if it's sensitive.
Regards - Dave. > Date: Mon, 7 Sep 2009 06:37:32 -0700 > Subject: $$Excel-Macros$$ "Too many arguments for this function" help with > nested IF statements > From: marka...@gmail.com > To: excel-macros@googlegroups.com > > > Good morning! > > Would love your help with this issue. I'm try 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!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)),"") > > ******the formula works fine until this point, then gives me the too > many arguments error. > > 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 > > > _________________________________________________________________ Get Hotmail on your iPhone Find out how here http://windowslive.ninemsn.com.au/article.aspx?id=845706 --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
