Can someone help me to correct the formula and arrive at correct result ?

[RapchikM]

Hello

I think i've made a mistake in writing the formula to derive the exact answer

E	F	G	H	I
000-100 Units		110	2.18	239.8
101-300 Units		220	5.36	1179.2
301-500 Units		220	11.62	2556.4
Above 500 Units		171	12.56	2147.76

E29 = 721

Formula in H21 =IF($E29<=$G$18,$G$18*$H$18,IF($E29<=$G$19,(($G$18*$H$18)+($E29-100)*5.36),IF($E29<=$G$21,(($G$18*$H$18)+($G$19*$H$19)+(($E29-300)*$H$20)),(($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E29-$G$21)*$H$21)))))

answer = 10883.40 instead of 6123.16

another try
E31 = 721
Formula in H21 =IF($E31<=$G$18,$G$18*$H$18,IF($E31<=$G$19,(($G$18*$H$18)+($E31-220)*5.36),IF($E31<=$G$21,(($G$18*$H$18)+($G$19*$H$19)+(($E31-220)*$H$20)),(($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E31-(G$21+G$20+G$19+G$18))*$H$21)))))

Answer is 3975.4 instead of 6123.16

So summing of 239.8+1179.2+2556.4+2147.76 = 6123.16

Your help shall be appreciated

Thanks
RapchikM

 

[RapchikM]

At Last Succeded Thank you Sir

HELP-INCORRECTION FORMULA.xlsx
	D	E	F	G	H	I	J	K
15
16
17				Units	Rate	Amount
18		000-100 Units		110	2.18	239.8
19		101-300 Units		220	5.36	1179.2
20		301-500 Units		220	11.62	2556.4
21		Above 500 Units		171	12.56	2147.76
22
23
24
25
26
27
28
29		721			10883.4
30
31		721			3975.4
32
33
Sheet1

Cell Formulas
Range	Formula
I18:I21	I18	=G18*H18
H29	H29	=IF($E29<=$G$18,$G$18*$H$18,IF($E29<=$G$19,(($G$18*$H$18)+($E29-100)*5.36),IF($E29<=$G$21,(($G$18*$H$18)+($G$19*$H$19)+(($E29-300)*$H$20)),(($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E29-$G$21)*$H$21)))))
H31	H31	=IF($E31<=$G$18,$G$18*$H$18,IF($E31<=$G$19,(($G$18*$H$18)+($E31-220)*5.36),IF($E31<=$G$21,(($G$18*$H$18)+($G$19*$H$19)+(($E31-220)*$H$20)),(($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E31-(G$21+G$20+G$19+G$18))*$H$21)))))

Pl Remember 6123.16 should be in either H29 or H31
RapchikM

 

[Dave Patton]

What are the amounts for the respective tiers (brackets)?
If you use the data in column E what amount do you calculate for 721 units?

Commissions 2023_x.xlsm
	E	F	G	H	I
16			Units	Rate	Amount
17
18	000-100 Units		0	2.18	218.00
19	101-300 Units		100	5.36	1,072.00
20	301-500 Units		300	11.62	2,324.00
21	Above 500 Units		500	12.56	2,775.76
22			1E+35		6,389.76
23
24
25	721		6,389.76	6,389.76
3b

Cell Formulas
Range	Formula
G22	G22	=10^35
I18:I20	I18	=(G19-G18)*H18
I21	I21	=(E25-G21)*H21
I22	I22	=SUM(I18:I21)
G25	G25	=SUM((E25>G18:G21)*(E25-G18:G21)*(H18:H21-H17:H20))
H25	H25	=SUMPRODUCT(--(E25>G18:G21),E25-G18:G21,H18:H21-H17:H20)

 

[Dave Patton]

How did you calculate the numbers in column G?
The bracket information indicates 0-100 @ 2.18

 

[RapchikM]

Dave Patton

How did you calculate the numbers in column G?
The bracket information indicates 0-100 @ 2.18

Thanks for your formulas in #12. but did not derive at to 6213.16. But then again you asked your doubt as above quoted

Actually in obtaining and exectuing XL2BB i forgot to state there is 10% penalty in each slab so therfore from 100 it becomes 110, 200-becomes 220 and so on. As total units consumed are 721 so therfore from 721 we substract 220-220-110 and balance we have 171

Hope this clarifies
Rapchik

 

[Dave Patton]

If your penalty is calculated by just changing the brackets, try the formula with the edit.

With correct information, you could build your own function with Lambda.

Commissions 2023_x.xlsm
	E	F	G	H	I
15
16			Units	Rate	Amount
17
18			0	2.18	239.80
19			110	5.36	1,179.20
20			330	11.62	2,556.40
21			550	12.56	2,147.76
22			1E+35		6,123.16
23
24
25	721		6,123.16	6,123.16
26
3b

Cell Formulas
Range	Formula
G22	G22	=10^35
I18:I21	I18	=MAX(0,MIN($E$25-G18,G19-G18)*H18)
I22	I22	=SUM(I18:I21)
G25	G25	=SUM((E25>G18:G21)*(E25-G18:G21)*(H18:H21-H17:H20))
H25	H25	=SUMPRODUCT(--(E25>G18:G21),E25-G18:G21,H18:H21-H17:H20)

 

[RapchikM]

Dear Dave Patton

Thanks for the Formulas.

How you got 330 units in G20. If you Study the slab in E20 it mentions 301-500 Units therfore 200 Units and penalty 10% on 200 = 220
even @ 220 in G20 i Get the Sum Total as 6578.42 instead of 6123.16 with both respective formulas
also H17 is blank

See below

 

[RapchikM]

HELP-INCORRECTION FORMULA.xlsx
	E	F	G	H	I
16			Units	Rate	Amount
17
18	000-100 Units		110	2.18	239.8
19	101-300 Units		220	5.36	1179.2
20	301-500 Units		220	11.62	2556.4
21	Above 500 Units		171	12.56	2147.76
22					6123.16
23
24
25
26
27
28
29	721			6578.42
30
31	721			6578.42
32
33
Sheet1

Cell Formulas
Range	Formula
I18:I21	I18	=G18*H18
I22	I22	=SUM(I18:I21)
H29	H29	=SUM((E29>G18:G21)*(E29-G18:G21)*(H18:H21-H17:H20))
H31	H31	=SUMPRODUCT(--(E31>G18:G21),E31-G18:G21,H18:H21-H17:H20)

 

[MARK858]

How you got 330 units in G20. If you Study the slab in E20 it mentions 301-500 Units therfore 200 Units and penalty 10% on 200 = 220

Dave's formulas are based on the difference between the bands so 330 - 110 = 220

Have a read of the link below which explains the theory of the sumproduct formula

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

The "How it works" link on the page is below (reworded)

McGimpsey & Associates : Excel : How differential rates work

 

[RapchikM]

Dear Mark858 Sir,

Have a read of the link below which explains the theory of the sumproduct formula
McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

After you click the "How it works" link on the page

McGimpsey & Associates : Excel : How differential rates work

Thank you so much for providing the link

Dave Patton for your detailed formula help and Peter_SSs Sir
Thank you so much for the helping me

Thread Resolved
Rapchik