Multiple condition in sales commission table

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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?


ABCDE
MONTHFD A/CTOTALCARRY FORWORDCOMISSION AMOUNT

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]320000
[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1280000
[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]32000[/TD]
[TD="align: right"]320000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5120000
[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]96000[/TD]
[TD="align: right"]960000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15360000
[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]288000[/TD]
[TD="align: right"]2880000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]46080000
[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]576000[/TD]
[TD="align: right"]5760000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]92160000
[/TD]
[TD="align: right"][/TD]

</tbody>
 
Upvote 0
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
ABC
1MONTHFD A/CCOMISSION AMOUNT
2125400
322504000
43125020000
54500080000
6520000320000
76800001280000
8732000512000
98960001536000
1092880004608000
11105760009216000
Sheet118 (2)
Cell Formulas
RangeFormula
C2=INT(B2/25)*400


Formula copied down.
 
Upvote 0
[TABLE="width: 534"]
<tbody>[TR]
[TD="class: xl65, width: 178"]MONTH
[/TD]
[TD="class: xl65, width: 178"]FD A/C[/TD]
[TD="class: xl65, width: 178"]COMMISSION AMOUNT
[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Last edited:
Upvote 0

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?


ABCDE
MONTHFD A/CTOTALCARRY FORWORDCOMISSION AMOUNT

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]320000
[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1280000
[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]32000[/TD]
[TD="align: right"]320000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5120000
[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]96000[/TD]
[TD="align: right"]960000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15360000
[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]288000[/TD]
[TD="align: right"]2880000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]46080000
[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]576000[/TD]
[TD="align: right"]5760000
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]92160000
[/TD]
[TD="align: right"][/TD]

</tbody>

[TABLE="width: 534"]
<tbody>[TR]
[TD="class: xl65, width: 178"]MONTH
[/TD]
[TD="class: xl65, width: 178"]FD A/C[/TD]
[TD="class: xl65, width: 178"]COMMISSION AMOUNT
[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
</tbody>[/TABLE]

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").
 
Upvote 0
Sir I want to delete c and d column then which formula is work here with all conditions above #26 post
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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