Forum Discussion
IF function issues
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.
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.
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.
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"