Multiple condition in sales commission table

[Dsunil05]

SAVING A/C	COMISSION AMOUNT		FD A/C	COMISSION AMOUNT
10	0		10	0
25	400		25	400
35	0		35	0
50	400		50	900
65	0		65	0
70	0		70	0
75	400		75	1500

<tbody>
</tbody>

THIS SALE COMMISSION TABLE OF BANK

1.) IN FIRST TABLE HOW COMMISSION REFLECT SAME IN "COMMISSION AMOUNT COLUMN" WHEN A/C COMES IN MULTIPLE OF 25 ( OR ANY OTHER MULTIPLE OF NUMBER)
I TRIED THIS FOR "B" COLUMN = IF (A3/25>=1,400,0)
BUT B4 TO B8 SHOWING 400


2.)IN SECOND TABLE HOW COMMISSION REFLECT DIFFERENT IN "COMMISSION AMOUNT COLUMN" WHEN A/C COMES IN MULTIPLE OF 25 ( OR ANY NUMBER)

IN SECOND TABLE EVERY TIME CHANGE COMMISSION AMOUNT WHEN FD A/C COMES IN MULTIPLE OF 25 ( OR ANY OTHER MULTIPLE OF NUMBER)

WHAT TO DO?

GUIDE ME WITHOUT USING VBA / MACRO I AM USING MS EXCEL 2007

 

[Dsunil05]

MONTH	FD A/C	TOTAL	CARRY FORWORD	COMISSION AMOUNT	BONUS AMOUNT
1	25	25	0.00	400.00	10000.00
2	250	250	0.00	4000.00	20000.00
3	1250	1250	0.00	20000.00	40000.00
4	5000	5000	0.00	80000.00	80000.00
5	20000	20000	0.00	320000.00	160000.00
6	80000	80000	0.00	1280000.00	320000.00
7	320000	320000	0.00	5120000.00	640000.00
8	960000	960000	0.00	15360000.00	1280000.00
9	2880000	2880000	0.00	46080000.00	2560000.00
10	5760000	5760000	0.00	92160000.00	7680000.00

<tbody>
</tbody>

BONUS FOR EVERY SALESMEN SINGLE TIME IN ALL JOB TIME

FD A/C NOS SLAB	NO OF FD A/C FROM	NO OF FD A/C TO	SLAB BONUS AMOUNT
1	0	24	0.00
2	25	249	10000.00
3	250	1249	20000.00
4	1250	4999	40000.00
5	5000	19999	80000.00
6	20000	79999	160000.00
7	80000	319999	320000.00
8	320000	959999	640000.00
9	960000	2879999	1280000.00
10	2880000	5759999	2560000.00
11	5760000	AND ABOVE	7680000.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[jtakw]

As mention in my Post #9 , your "Commission Amount" seems Incorrect in your Post #8 and Post #11 above.

Formulas copied down Columns, Column F formula uses Table 2:

Book1
	A	B	C	D	E	F
1	MONTH	FD A/C	TOTAL	CARRY FORWORD	COMISSION AMOUNT	BONUS AMOUNT
2	1	25	25	0	400	10000
3	2	250	250	0	4000	20000
4	3	1250	1250	0	20000	40000
5	4	5000	5000	0	80000	80000
6	5	20000	20000	0	320000	160000
7	6	80000	80000	0	1280000	320000
8	7	32000	32000	0	512000	160000
9	8	96000	96000	0	1536000	320000
10	9	288000	288000	0	4608000	320000
11	10	576000	576000	0	9216000	640000
12
13
14	FD A/C NOS SLAB	NO OF FD A/C FROM	NO OF FD A/C TO	SLAB BONUS AMOUNT
15	1	0	24	0
16	2	25	249	10000
17	3	250	1249	20000
18	4	1250	4999	40000
19	5	5000	19999	80000
20	6	20000	79999	160000
21	7	80000	319999	320000
22	8	320000	959999	640000
23	9	960000	2879999	1280000
24	10	2880000	5759999	2560000
25	11	5760000	AND ABOVE	7680000
Sheet118

Cell Formulas
Range	Formula
C2	=SUM(D1,B2)
D2	=C2-INT(C2/25)*25
E2	=INT(C2/25)*400
F2	=LOOKUP(C2,B$15:B$25,D$15:D$25)

 

[Dsunil05]

PROBLEM IN BONUS

WHEN DIRECTLY SUBMIT 300 THEN BONUS REFLECT 20000 IS WRONG BUT QUALIFY SLAB 1ST AND 2ND IT IS 30000 IS RIGHT

MONTH	FD A/C	TOTAL	CARRY FORWARD	COMMISSION AMOUNT	BONUS AMOUNT
1	20	20	20	0	0
2	300	320	20	4800	20000
3	1250	1270	20	20000	40000
4	5000	5020	20	80000	80000
5	20000	20020	20	320000	160000
6	80000	80020	20	1280000	320000
7	320000	320020	20	5120000	640000
8	960000	960020	20	15360000	1280000
9	2880000	2880020	20	46080000	2560000
10	5760000	5760020	20	92160000	7680000
FD A/C NOS SLAB	NO OF FD A/C FROM	NO OF FD A/C TO	SLAB BONUS AMOUNT
1	0	24	0
2	25	249	10000
3	250	1249	20000
4	1250	4999	40000
5	5000	19999	80000
6	20000	79999	160000
7	80000	319999	320000
8	320000	959999	640000
9	960000	2879999	1280000
10	2880000	5759999	2560000
11	5760000	AND ABOVE	7680000

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[jtakw]

Just saying the result is "Wrong" doesn't tell me why, please show the desired results with explanation.

 

[Dsunil05]

Sir it means if salesmen submit/open a/c first month 20 and second month 300 total 320 then he will qualify for 2 bonus slabs and got bonus amount for slabs 25 & 250 both total amount of bonus 10000+20000=30000 in second month but shows only 20000

 

[jtakw]

Sounds like this is going to get Very complicated.

What if someone got 126525 FD A/C in a month, would the Slab bonus be 320000+160000+80000+40000+20000+10000???

 

[Dsunil05]

Yes sir

=320000-126525
QUALIFY FOR BONUS SLAB 1 TO 6

<tbody>
</tbody>

SLAB	BONUS AMT.
1	10000.00
2	20000.00
3	40000.00
4	80000.00
5	160000.00
6	320000.00
TOTAL	630000.00

<colgroup><col><col></colgroup><tbody>
</tbody>

AND GOT COMMISSION AMT 2024400.00

 

[Dsunil05]

any reply sir

 

[jtakw]

No, I'm sorry, I can't come up with a formula for this because the Bonus structure is not linear and there's no particular pattern to follow.

If someone got 81275 in a month, that means Bonus would be 320000+40000+10000, and that's without taking into consideration of the "Carry Forward".

 

[Dsunil05]

OK sir, can you help in formula for commission amount

means all are conditions applicable e.g. multiple of 25 a/c, total , carry forward

all calculations in commission amount column

there are two columns only 1. no of account and 2. commission amount column