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

 

[jtakw]

Is the E Column formula in Post #12 not working?

 

[Dsunil05]

it is working very well,

now i am trying to do modification in column E calculate direct result of commission by removing column c and d with formulas you provided
but not its not working

how to do it?

	A	B	C	D	E
1	MONTH	FD A/C	TOTAL	CARRY FORWORD	COMISSION AMOUNT
2	1	25	25	0	400
3	2	250	250	0	4000
4	3	1250	1250	0	20000
5	4	5000	5000	0	80000
6	5	20000	20000	0	320000
7	6	80000	80000	0	1280000
8	7	32000	320000	0	5120000
9	8	96000	960000	0	15360000
10	9	288000	2880000	0	46080000
11	10	576000	5760000	0	92160000

<thead>
</thead><tbody>
</tbody>

 

[Dsunil05]

any suggestion

 

[jtakw]

I'm probably misunderstanding what you mean and/or you're not explaining it in a way I can understand.

Without Column C and D, commission based on Column B:

Book1
	A	B	C
1	MONTH	FD A/C	COMISSION AMOUNT
2	1	25	400
3	2	250	4000
4	3	1250	20000
5	4	5000	80000
6	5	20000	320000
7	6	80000	1280000
8	7	32000	512000
9	8	96000	1536000
10	9	288000	4608000
11	10	576000	9216000
Sheet118 (2)

Cell Formulas
Range	Formula
C2	=INT(B2/25)*400

Formula copied down.

 

[Dsunil05]

MONTH	FD A/C	COMMISSION AMOUNT
1	15	0
2	8	0
3	8	0
4	8	0
5	8	0
6	8	0
7	8	0
8	8	0
9	8	0
10	8	0

<tbody>
</tbody>

C2=INT(B2/25)*400
not working in this condition
because total account in 3rd month is 31 cause of this commission paid amount 400.00 for 25 so 31-25= 6 carry forward to next moth no.4
total account in 4th month is 8+6=14 cause of this commission paid amount 00.00 for 14 not qualify for 25 so 14 carry forward to next moth no.5
total account in 5th month is 8+14=22 cause of this commission paid amount 00.00 for 22 not qualify for 25 so 22 carry forward to next moth no.6
total account in 6th month is 8+22=30 cause of this commission paid amount 400.00 for 25 so 30-25= 5 carry forward to next moth no.7
.
.
.
.
.
.

how to do?

please help last time then close it

 

[jtakw]

Is the E Column formula in Post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=12]#12 [/URL] not working?

it is working very well,

now i am trying to do modification in column E calculate direct result of commission by removing column c and d with formulas you provided
but not its not working

how to do it?

	A	B	C	D	E
1	MONTH	FD A/C	TOTAL	CARRY FORWORD	COMISSION AMOUNT
2	1	25	25	0	400
3	2	250	250	0	4000
4	3	1250	1250	0	20000
5	4	5000	5000	0	80000
6	5	20000	20000	0	320000
7	6	80000	80000	0	1280000
8	7	32000	320000	0	5120000
9	8	96000	960000	0	15360000
10	9	288000	2880000	0	46080000
11	10	576000	5760000	0	92160000

<thead>
</thead><tbody>
</tbody>

MONTH	FD A/C	COMMISSION AMOUNT
1	15	0
2	8	0
3	8	0
4	8	0
5	8	0
6	8	0
7	8	0
8	8	0
9	8	0
10	8	0

<tbody>
</tbody>

C2=INT(B2/25)*400
not working in this condition
because total account in 3rd month is 31 cause of this commission paid amount 400.00 for 25 so 31-25= 6 carry forward to next moth no.4
total account in 4th month is 8+6=14 cause of this commission paid amount 00.00 for 14 not qualify for 25 so 14 carry forward to next moth no.5
total account in 5th month is 8+14=22 cause of this commission paid amount 00.00 for 22 not qualify for 25 so 22 carry forward to next moth no.6
total account in 6th month is 8+22=30 cause of this commission paid amount 400.00 for 25 so 30-25= 5 carry forward to next moth no.7
.
.
.
.
.
.

how to do?

please help last time then close it

Then I recommend sticking with the setup as in Post #12 , without removing Column C and D, if you just don't want to "See" Columns C and D, Hide them (select Columns C and D, right click, select "Hide").

 

[Dsunil05]

Sir I want to delete c and d column then which formula is work here with all conditions above #26 post

 

[Dsunil05]

Close this thread