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

 

[Eric W]

Try:

	A	B	C	D	E
1	SAVING A/C	COMISSION AMOUNT		FD A/C	COMISSION AMOUNT
2	10	0		10	0
3	25	400		25	400
4	35	0		35	0
5	50	400		50	900
6	65	0		65	0
7	70	0		70	0
8	75	400		75	1400

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula B2 =IF(MOD(A2,25)=0,400,0) E2 =IF(MOD(D2,25)=0,400+(INT(D2/25)-1)*500,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

To check to see if a number is a multiple of 25, use MOD. MOD returns the remainder when you divide by 25 (or some other number), so when the remainder is 0, it's a multiple. For the second formula, I don't know how you intend to change the amounts, so the formula I created starts at 400 and adds 500 each time. It doesn't match your example, so if you have a different method, let me know and I'll adapt it.

 

[jtakw]

Hi,

This is my take on it.

Please note Column E commission is not linear.

Book1
	A	B	C	D	E	F
1	SAVING A/C	COMISSION AMOUNT		FD A/C	COMISSION AMOUNT	COMISSION AMOUNT
2	10	0		10	0	0
3	25	400		25	400	400
4	35	0		35	0	0
5	50	400		50	900	900
6	65	0		65	0	0
7	70	0		70	0	0
8	75	400		75	1500	1500
Sheet118

Cell Formulas
Range	Formula
E2	=IF(MOD(D2,25),0,CHOOSE(D2/25,400,900,1500))
F2	=IF(MOD(D2,25),0,400*(D2/25)+100*CHOOSE(D2/25,0,1,3))
B2	=IF(MOD(A2,25),0,400)

Formulas copied down.

 

[Dsunil05]

sorry sir it is if in single month saving A/C OR FD ACCOUNT CROSSING 25 (e.g. 28 , 32, 39.....49) means greater than 25 and less than 49 ,then its not working

 

[jtakw]

sorry sir it is if in single month saving A/C OR FD ACCOUNT CROSSING 25 (e.g. 28 , 32, 39.....49) means greater than 25 and less than 49 ,then its not working

I do not understand what you're saying at all.

Please explain clearly, and may be also post detailed samples with expected results.

 

[Dsunil05]

MONTH	SAVING A/C	TOTAL	CARRY FORWORD	COMISSION AMOUNT
1	10	10	10	0
2	25	35	10	400
3	35	45	20	400
4	50	70	20	800
5	65	85	10	1200
6	70	80	5	1200
7	75	80	5	1200
8	15	20	20	0

<tbody>
</tbody>

MEANS IN
1ST MONTH NEW 10 A/C, TOTAL A/C= 10, COMMISSION =00, QUALIFY A/c=00, REMAIN FOREWORD TO NEXT MONTH=10 A/C
2ND MONTH NEW 25 A/C , TOTAL A/C= 35, COMMISSION =400, QUALIFY A/c=25, REMAIN FOREWORD TO NEXT MONTH=10 A/C
3RD MONTH NEW 35 A/C , TOTAL A/C=45 (LAST MONTH REMAIN 10+), COMMISSION =400, QUALIFY A/c=25, REMAIN FOREWORD TO NEXT MONTH=20 A/C
4TH MONTH NEW 50 A/C , TOTAL A/C=70 (LAST MONTH REMAIN 20+), COMMISSION =800, QUALIFY A/c=50, REMAIN FOREWORD TO NEXT MONTH=20 A/C
5TH MONTH NEW 65 A/C , TOTAL A/C=85 (LAST MONTH REMAIN 20+), COMMISSION =1200, QUALIFY A/c=75, REMAIN FOREWORD TO NEXT MONTH=10 A/C
6TH MONTH NEW 70 A/C , TOTAL A/C=80 (LAST MONTH REMAIN 10+), COMMISSION =1200, QUALIFY A/c=75, REMAIN FOREWORD TO NEXT MONTH=05 A/C
7TH MONTH NEW 75 A/C , TOTAL A/C=80 (LAST MONTH REMAIN 5+), COMMISSION =1200, QUALIFY A/c=75, REMAIN FOREWORD TO NEXT MONTH=05 A/C
8TH MONTH NEW 15 A/C , TOTAL A/C=20 (LAST MONTH REMAIN 5+), COMMISSION =00, QUALIFY A/c=00, REMAIN FOREWORD TO NEXT MONTH=20 A/C

SAME FOR F/D ACCOUNT BUT COMMISSION SLAB IS DIFF

 

[jtakw]

Thank you for providing Clear details, it's quite a bit different than what I understand from you OP.

I believe this will work for this part of your query.

I'm providing formulas for "TOTAL", "CARRY FORWARD", and "COMMISSION AMOUNT", if you Only need "COMMISSION AMOUNT", then disregard Column C and D formulas, and use Only Column E formula.

All formulas copied down.

Book1
	A	B	C	D	E
1	MONTH	SAVING A/C	TOTAL	CARRY FORWORD	COMISSION AMOUNT
2	1	10	10	10	0
3	2	25	35	10	400
4	3	35	45	20	400
5	4	50	70	20	800
6	5	65	85	10	1200
7	6	70	80	5	1200
8	7	75	80	5	1200
9	8	15	20	20	0
Sheet118

Cell Formulas
Range	Formula
C2	=SUM(D1,B2)
D2	=C2-INT(C2/25)*25
E2	=INT(C2/25)*400

 

[Dsunil05]

THANKS ALL DONE WITH SAVING A/C

BUT WITH FD A/C BONUS GIVEN BELOW

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	32000	32000	0.00	5120000.00	640000.00
8	96000	96000	0.00	15360000.00	1280000.00
9	288000	288000	0.00	46080000.00	2560000.00
10	576000	576000	0.00	92160000.00	7680000.00

<colgroup><col><col><col><col><col><col></colgroup><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	31999	320000.00
8	32000	95999	640000.00
9	96000	287999	1280000.00
10	288000	575999	2560000.00
11	576000	AND ABOVE	7680000.00

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

HOW TO DO?

 

[jtakw]

Please re-check your figures:

Table 1:
Line 7, 8, 9, 10, "Commission Amount" do Not seem to be correct.

Table 2:
Line 7, 8, Columns "From" and "To", went Backwards?

 

[Dsunil05]

yes my mistake,

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>