IF function issues

[mtd2987]

I'm looking for help creating a formula that will populate up to a certain amount, then move anything over that dollar amount to the next "bucket" up to a certain amount, and anything above that to the last "bucket." This would be quarter over quarter data. I realized that probably doesn't make too much sense, but I'm stuck on this one if anyone can help. Attached is look at the form. Red highlighted cells are where I need my formulas. I've come close with a few formulas I've created but it's not working as a whole.

[#2]

I'm looking at the sample Excel file, and am not able to figure out from your words above what the rules are. Could you spell them out in English? I see the nested IF statements in Cell H8, and the various antecedents to it, but it's hard to translate somebody else's code.... Among other things, which buckets are the "next buckets"--you said the cells in red--but is each one "next" from the cell above or the cell to the left? And so on....

You're so close to it that it's obvious to you, but words like "next" can be ambiguous.... and when you talk about "eligible" is that "eligible" on a YTD basis or just the current quarter...and so on.

Your box containing data on incentives has a confusing label on the second line: "Incentive on the second $200,000" is what you say, but the preceding line is only the "first 100,000" so do you actually mean, the "second 100,000" which would be "up to 200,000"? I think that's actually what you mean.

I envision something like the income tax table, where the tax rate up to X is such and so, up to Y something more, and so on.

Anyway, this doesn't look difficult once it's understood, and it's possible others are more able to grasp the intent already....but I'd be happy to give my input if you can help describe it more clearly.

[#3]

@mtd2987 

Have look at the attached file (i.e. a copy of your own with some formulae added). I assume that this does what you asked for. I chose to work with MIN and MAX functions to determine the overflow of each "bucket". I've tested it with varying quarterly fees earned and it seems to work all the time.

[#4]

@mathetes  - Thanks for your response.

To start, the nested IF statement in cell H8 is not working correctly, so I'm sure if I'm on the right track with that one, or if it needs to be removed and rewritten entirely.

The following cells all have formulas that are currently working how I need them to:

 - G7, G8, G9, H7, H9, I7, I9, J7, J9

The cells I could use help with are H8, I8 and J8

The "buckets" I'm referring to what would "fees generated"

  - $0 - $100,000 (bucket 1)

  - $100,001 - $200,000 (bucket 2)

  - $200,001+ (bucket 3)

So on "sheet1" of the sample file I attached would be where I record the fees generated each quarter which flow over to "sheet2."

What I need to happen is, once fees generated are greater than $100,000 (bucket 1), fees generated would then move to bucket 2, ($100,001 - $200,000). Once fees generated are greater than $200,000, all remaining fees generated for the year would move to bucket 3, ($200,001+).

So for example, in the file attached, cell H8 should be showing $50,000 -->$75,000 fees generated in Q1 should all be in bucket 1 as shown in cell G7. Another $75,000 was generated in Q2, so $25,000 should be in bucket 1, hitting the $100,000 threshold and the remaining $50,000 should be in bucket 2 in cell H8. If we keep going with another $75,000 generated in Q3, $50,000 should be in bucket 2 (cell I8) hitting the next $100,000 threshold, and the remaining $25,000 should then be in bucket 3, cell I9. Now that we're in the final bucket, all remaining fees in Q4 would fall into bucket 3, cell J9.

I probably didn't need to include rows 11-14 in my example file as those are working correctly and are dependent on the information in rows 4-9 being correct.

Please let me know if you'd like any further clarification and thanks again for your help.

[#5]

@Riny_van_Eekelen  - thank you so much. I didn't even think to use MIN MAX statements. Still learning some of the more "advanced" excel functions but this will be a great reference going forward.

Very much appreciated!!!

[#6]

@mtd2987 

So here in the attached you'll see yet another approach. Like @Riny_van_Eekelen  I've made use of MIN and MAX.

I've also made use of a table in such a way that you can, in the future, readily make changes to the thresholds and incentive percentages in this table. I've also written the formulas so they only use the row containing the quarters "Total Eligible Fees," letting the formulas take care of the different step levels.

You'll see I use VLOOKUP to access the Incentive Table, and I use an assigned range name "IncentiveTbl" in the VLOOKUP formula.

[#7]

Thank you! @mathetes 

[#8]

@mtd2987   let me know if it makes sense to you, how the formulas and tables work...and that you see how it gives you flexibility. (I actually wondered why you had the different levels given that the incentive added was always 15% of whatever the number was...; but maybe you were just putting that out to protect proprietary info.)   I always try to design so that tables are at the heart of as much as possible, to give room for future nuances without requiring the whole program to be rewritten. Back in my database days we spoke of "table-driven design"

[#9]

@mtd2987 

I wrote the attached file specifically for a dynamic array version of Excel which would allow the arrays to resize without user intervention.  Since it is unlikely that many potential users with have dynamic array code, I have changed it to CSE arrays.

I have used matrix multiplication to accumulate the cumulative fees.  After picking the lower value from every pair of cumulative fee and the upper limit of a fee band, incremental values are returned by differencing adjacent values, again using matrix multiplication (using the inverse of the matrices that accumulated the fees).  This is not for the faint-hearted!

Sadly CSE is clunky and the solution will be a poor shadow of the dynamic array solution.