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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
jtakw said:
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?
Click to expand...

Dsunil05 said:
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>
Click to expand...

Dsunil05 said:
[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
Click to expand...

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
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

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