Multiple condition in sales commission table

D

Dsunil05

New Member
Joined
Feb 20, 2015
Messages
34
Office Version
  1. 2021
  2. 2019
  3. 2013
  4. 2007
  5. 2003 or older
Platform
  1. Windows

<tbody>
[TD="class: xl66"]SAVING A/C[/TD]
[TD="class: xl66"]COMISSION AMOUNT[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]FD A/C[/TD]
[TD="class: xl66"]COMISSION AMOUNT[/TD]

[TD="class: xl66"]10[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl66"]25[/TD]
[TD="class: xl66"]400[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]25[/TD]
[TD="class: xl66"]400[/TD]

[TD="class: xl66"]35[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]35[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl66"]50[/TD]
[TD="class: xl66"]400[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]900[/TD]

[TD="class: xl66"]65[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]65[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl66"]70[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]70[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl66"]75[/TD]
[TD="class: xl66"]400[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]75[/TD]
[TD="class: xl66"]1500[/TD]

</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
 
Last edited:
Sort by date Sort by votes

<tbody>
[TD="class: xl65"]MONTH[/TD]
[TD="class: xl65, width: 122"]FD A/C[/TD]
[TD="class: xl65, width: 103"]TOTAL[/TD]
[TD="class: xl65, width: 132"]CARRY FORWORD[/TD]
[TD="class: xl65, width: 125"]COMISSION AMOUNT[/TD]
[TD="class: xl65, width: 98"]BONUS AMOUNT[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]400.00[/TD]
[TD="class: xl66"]10000.00[/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]250[/TD]
[TD="class: xl65"]250[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]4000.00[/TD]
[TD="class: xl66"]20000.00[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]1250[/TD]
[TD="class: xl65"]1250[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]20000.00[/TD]
[TD="class: xl66"]40000.00[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]5000[/TD]
[TD="class: xl65"]5000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]80000.00[/TD]
[TD="class: xl66"]80000.00[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl65"]20000[/TD]
[TD="class: xl65"]20000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]320000.00[/TD]
[TD="class: xl66"]160000.00[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]80000[/TD]
[TD="class: xl65"]80000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]1280000.00[/TD]
[TD="class: xl66"]320000.00[/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl65"]320000[/TD]
[TD="class: xl65"]320000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]5120000.00[/TD]
[TD="class: xl66"]640000.00[/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"]960000[/TD]
[TD="class: xl65"]960000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]15360000.00[/TD]
[TD="class: xl66"]1280000.00[/TD]

[TD="class: xl65"]9[/TD]
[TD="class: xl65"]2880000[/TD]
[TD="class: xl65"]2880000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]46080000.00[/TD]
[TD="class: xl66"]2560000.00[/TD]

[TD="class: xl65"]10[/TD]
[TD="class: xl65"]5760000[/TD]
[TD="class: xl65"]5760000[/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]92160000.00[/TD]
[TD="class: xl66"]7680000.00[/TD]

</tbody>

BONUS FOR EVERY SALESMEN SINGLE TIME IN ALL JOB TIME

[TABLE="width: 489"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]FD A/C NOS SLAB[/TD]
[TD]NO OF FD A/C FROM[/TD]
[TD]NO OF FD A/C TO[/TD]
[TD]SLAB BONUS AMOUNT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]24[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]249[/TD]
[TD]10000.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]250[/TD]
[TD]1249[/TD]
[TD]20000.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1250[/TD]
[TD]4999[/TD]
[TD]40000.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5000[/TD]
[TD]19999[/TD]
[TD]80000.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]20000[/TD]
[TD]79999[/TD]
[TD]160000.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]80000[/TD]
[TD]319999[/TD]
[TD]320000.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]320000[/TD]
[TD]959999[/TD]
[TD]640000.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]960000[/TD]
[TD]2879999[/TD]
[TD]1280000.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2880000[/TD]
[TD]5759999[/TD]
[TD]2560000.00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]5760000[/TD]
[TD]AND ABOVE[/TD]
[TD]7680000.00[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
ABCDEF
1MONTHFD A/CTOTALCARRY FORWORDCOMISSION AMOUNTBONUS AMOUNT
212525040010000
322502500400020000
431250125002000040000
545000500008000080000
6520000200000320000160000
76800008000001280000320000
8732000320000512000160000
98960009600001536000320000
10928800028800004608000320000
111057600057600009216000640000
12
13
14FD A/C NOS SLABNO OF FD A/C FROMNO OF FD A/C TOSLAB BONUS AMOUNT
1510240
1622524910000
173250124920000
1841250499940000
19550001999980000
2062000079999160000
21780000319999320000
228320000959999640000
23996000028799991280000
2410288000057599992560000
25115760000AND ABOVE7680000
Sheet118
Cell Formulas
RangeFormula
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)
 
Upvote 0
PROBLEM IN BONUS

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


[TABLE="width: 730"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MONTH[/TD]
[TD]FD A/C[/TD]
[TD]TOTAL[/TD]
[TD]CARRY FORWARD
[/TD]
[TD]COMMISSION AMOUNT
[/TD]
[TD]BONUS AMOUNT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4800[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]1270[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]40000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]80000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]20020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]320000[/TD]
[TD="align: right"]160000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]80020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1280000[/TD]
[TD="align: right"]320000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]320000[/TD]
[TD="align: right"]320020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5120000[/TD]
[TD="align: right"]640000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]960000[/TD]
[TD="align: right"]960020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15360000[/TD]
[TD="align: right"]1280000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2880000[/TD]
[TD="align: right"]2880020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]46080000[/TD]
[TD="align: right"]2560000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]5760000[/TD]
[TD="align: right"]5760020[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]92160000[/TD]
[TD="align: right"]7680000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FD A/C NOS SLAB[/TD]
[TD]NO OF FD A/C FROM[/TD]
[TD]NO OF FD A/C TO[/TD]
[TD]SLAB BONUS AMOUNT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]1249[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]4999[/TD]
[TD="align: right"]40000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]19999[/TD]
[TD="align: right"]80000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]79999[/TD]
[TD="align: right"]160000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]319999[/TD]
[TD="align: right"]320000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]320000[/TD]
[TD="align: right"]959999[/TD]
[TD="align: right"]640000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]960000[/TD]
[TD="align: right"]2879999[/TD]
[TD="align: right"]1280000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2880000[/TD]
[TD="align: right"]5759999[/TD]
[TD="align: right"]2560000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]5760000[/TD]
[TD]AND ABOVE[/TD]
[TD="align: right"]7680000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Just saying the result is "Wrong" doesn't tell me why, please show the desired results with explanation.
 
Upvote 0
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
 
Last edited:
Upvote 0
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???
 
Upvote 0
Yes sir

=320000-126525
QUALIFY FOR BONUS SLAB 1 TO 6
[TABLE="width: 824"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 156"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SLAB[/TD]
[TD]BONUS AMT.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10000.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20000.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]40000.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]80000.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]160000.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]320000.00[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]630000.00
[/TD]
[/TR]
</tbody>[/TABLE]


AND GOT COMMISSION AMT 2024400.00
 
Last edited:
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
SumIf problem
Replies
2
Views
313
sharshra
S
C
  • Solved
Add a table to e-mail to be sent to multiple addreses
Replies
3
Views
120
mumps
mumps
S
  • Question Question
Scan function in calculated field in excel pivot table
Replies
3
Views
259
RoryA
RoryA
B
  • Solved
Repeat Excel Spreadsheet Column Headings on each row until they change
2
Replies
12
Views
581
awoohaw
awoohaw
J
  • Question Question
Lookup from Pivot Table based on Multiple Criteria
Replies
1
Views
216
shift-del
shift-del

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top