10-28-2019 02:19 PM
10-28-2019 02:19 PM
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.
10-28-2019 03:22 PM
10-29-2019 06:17 AM
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.
10-29-2019 06:18 AM
@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.
10-29-2019 07:48 AM
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.
10-29-2019 09:08 AM
@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"
10-29-2019 11:28 AM
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.