Multiple If statements

[NYEXCEL1]

I am trying to build a simple bonus conditions model using the following scenarios. I am having an issue calculating when conditions cross over multiple "bucket" levels using IFS and AND/OR conditions

Current Revenue $98

Bucket 1: $0 - $99 12%
Bucket 2: $100 - $199 8%
Bucket 3: $200 - $300 4%

Based on the above, which the current revenue level< the person would get ($98*12%) as a bonus. What i am trying to figure out is if the current revenue is $150 how can i calculate the first $99 at 12% then the next $51 at 8%. Also looking to the future same question but if current revenue is at $250 what is the formula to calculate over all three buckets?

Appreciate the forums help and thank you in advance.

 

[Joe4]

No, if the endpoints are different, then it's not the same, but that's fine.
and I'm not talking about fractions between 99-100, or 199-200...(anything over 99)...
And I like your formula approach.

Yeah, when I posted the formula with the updated endpoints in post 7, I accidentally updated the "gap" too, not just endpoints (my bad), i.e.

=(MAX(A1-199,0)*0.04)+(MIN(MAX(A1-99,0),99)*0.08)+(MIN(A1,99)*0.12)

When I went back and corrected it, i.e.

=(MAX(A1-199,0)*0.04)+(MIN(MAX(A1-99,0),100)*0.08)+(MIN(A1,99)*0.12)

it returns the exact same results as yours.

	jtakw in Post #4	Joe Revised Post #7
98​	11.76​	11.76​
150​	15.96​	15.96​
250​	21.92​	21.92​
100​	11.96​	11.96​
101​	12.04​	12.04​
200​	19.92​	19.92​
199​	19.88​	19.88​