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]

For a value in cell A1, create a formula like this:

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

note the three different colors for the three different pieces of the formula.

 

[uvela]

Apologize for the post the above code works best.

 

[jtakw]

Hi,

B7 formula requires C1 to be Blank.
C7 formula requires Delta D2:D4
E7 formula is a modification of Post #2 to produce correct results.

Book3.xlsx
	A	B	C	D	E
1	Bucket Exceeding			Delta
2	0		12%	12%
3	99		8%	-4%
4	199		4%	-4%
5
6
7	98	11.76	11.76		11.76
8	150	15.96	15.96		15.96
9	250	21.92	21.92		21.92
Commission

Cell Formulas
Range	Formula
B7:B9	B7	=SUMPRODUCT((A7>A$2:A$4)*(A7-A$2:A$4)*(C$2:C$4-C$1:C$3))
C7:C9	C7	=SUMPRODUCT((A7>A$2:A$4)*(A7-A$2:A$4)*D$2:D$4)
E7:E9	E7	=(MAX(A7-199,0)*0.04)+(MIN(MAX(A7-99,0),100)*0.08)+(MIN(A7,99)*0.12)

 

[NYEXCEL1]

For a value in cell A1, create a formula like this:

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

note the three different colors for the three different pieces of the formula.

Thank you Joe. Appreciate your help. Sometimes a simple solution is better than a complex one!

 

[jtakw]

Thank you Joe. Appreciate your help. Sometimes a simple solution is better than a complex one!

I Caution, that formula will produce incorrect results, try it using 100, 150, 250

If you want a single formula with no table, use my modified version in E7 in Post #4 instead.

 

[Joe4]

I Caution, that formula will produce incorrect results, try it using 100, 150, 250

Can you produce example of where it will return incorrect results?

Note that they may need to tweak the endpoints a little, as they ended one at 99 and started the next at 100 (not considering the possibility of something like 99.50 and where that should fall).
Whatever it needs to be, you should just be able to tweak those endpoints in those formulas to get what you need, i.e.

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

 

[jtakw]

Can you produce example of where it will return incorrect results?

Note that they may need to tweak the endpoints a little, as they ended one at 99 and started the next at 100 (not considering the possibility of something like 99.50 and where that should fall).
Whatever it needs to be, you should just be able to tweak those endpoints in those formulas to get what you need, i.e.

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

I already provided the correct version in Post #4, Post #2 will produce incorrect results for anything over 99:

Book3.xlsx
	A	B	C
1		Mine in Post #4	Joe4 Post #2
2	98	11.76	11.76
3	150	15.96	16
4	250	21.92	22
5	100	11.96	12
6	101	12.04	12.08
7	200	19.92	20
8	199	19.88	19.92
Sheet1063

Cell Formulas
Range	Formula
B2:B8	B2	=(MAX(A2-199,0)*0.04)+(MIN(MAX(A2-99,0),100)*0.08)+(MIN(A2,99)*0.12)
C2:C8	C2	=(MAX(A2-200,0)*0.04)+(MIN(MAX(A2-100,0),100)*0.08)+(MIN(A2,100)*0.12)

 

[Joe4]

So essentially, we just posted the same formula, with different endpoints, like I said in my last post (substitute "99" for "100" and "199" for "200").


I explained the reason why I did that in my last post (values between 99-100 and 199-200 were not included, so I made an assumption).
In hindsight, I probably should have said that in my first reply.

 

[jtakw]

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.