Related Posts
Where do tax seniors go as exit opp?
We’re looking for a Chicago brand strategist, 3-5 years experience. Will consider 5+, but salary is capped at $85k. Click link, read job description DM me if interested.
LinkedIn à https://www.linkedin.com/jobs/cap/view/1596879751/?pathWildcard=1596879751&trk=mcm
Merge.com à https://myelincommunicationsdbamerge.applytojob.com/apply/YT7dRHYHzt/Brand-Strategist
Bowl Leader
Pro
Wow, so no more free lunches…what a joke
What does these bands mean in EXL? (A,A2,B,C)
Additional Posts in Excel Genius
Visual Storyteller
Would anyone be willing to teach me vlookup?
Coach
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Tldnr
easy.
E26: max(0,min(186000-sum($e$23:e$23),e$23)) * $b$4
E27: max(0,min(sum($e$23:e$23)-186000,e$23)) * $b$5
copy e26 and e27 over to every column afterwards
Just build a table and do an approximate lookup instead of that clunky formula
Mentor
How would you do it if it's cumulative? And, most importantly, how would you deal with the year you cross the limit?
Mentor
Is the 186k limit per year, or in total?
It may be better if you could describe what you need (in terms of the rules per se) so we can work together here.
P.s.: Just a friendly reminder that it may be better to use dummy data and not the actual data (although this seems annonimized enough)
Mentor
Okey
So, ideally your company may reach the 186k in year 2, but what if it happens on year 3? And what if it reaches 186k mid year?
If you ignore those cases, your formula is almost correct: the only thing is that in the TRUE criteria of the 2nd =IF(), the actual calculation is 186k - D26 (as the sum of D26 +(186k - D26) is equal to 186k)
For the 3% fee, for ALL BUT THE YEAR YOU ACHIEVE THE 186K (and previous years) the formula is just net revenue *3%. For the year you achieve it, it's not 100% clear the rule of it (for me, at least) but I'd do: calculate the net revenue needed to achieve the 186k, and subtract it from the current net revenue before applying the 3%
In excel terms:
=IF(SUM(incentive_row_last)>=186k, net_revenue*3%, IF(SUM(incentive_row)>=186k, (net_revenue -(186k-SUM(incentive_row_last))/15%)*3%,0))
Thank you for your service
Why not a look up using the table.
=IF(total\_amount<=186000, total\_amount*0.15, (total\_amount-186000)*0.03+27900)$$
Here is an example of how this formula works in a table:
| Year | Total Amount | Incentive Fee |
|------|--------------|---------------|
| 1 | 100000 | 15000 |
| 2 | 200000 | 31800 |
| 3 | 300000 | 61500 |
The formula for the incentive fee in cell C2 is:
`=IF(B2<=186000,B2*0.15,(B2-186000)*0.03+27900)`
You can copy and paste this formula to the other cells in column C.
I'm adding this to my LinkedIn. I am now a finanicoa modeller 😭