Multi-condition if statements for sales order form??

datadigger

New Member
Joined
Mar 16, 2009
Messages
4
I was asked to help out with a form to tabulate sales orders.
What I think I need is a four condition if statement. The structure of the promotion is that if you order x amount you get x discount. There are four ranges that follow:

<table x:str="" style="border-collapse: collapse; width: 208pt;" width="277" border="0" cellpadding="0" cellspacing="0"><col style="width: 79pt;" width="105"> <col style="width: 129pt;" width="172"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl24" style="height: 15.75pt; width: 79pt;" width="105" height="21">
</td> <td class="xl27" style="width: 129pt;" width="172">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl25" style="height: 15.75pt; width: 79pt;" width="105" height="21">Order *</td> <td class="xl28" style="width: 129pt;" width="172">Product Discount****</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt; width: 79pt;" width="105" height="18">
</td> <td class="xl29" style="width: 129pt;" width="172">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="height: 15.75pt; width: 79pt;" x:num="5000" width="105" height="21">$5,000-9,999 </td> <td class="xl31" style="width: 129pt;" x:num="0.04" width="172"> 4%</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="height: 15.75pt; width: 79pt;" x:num="10000" width="105" height="21">$10,000-14,999 </td> <td class="xl31" style="width: 129pt;" x:num="0.05" width="172"> 5%</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="height: 15.75pt; width: 79pt;" x:num="15000" width="105" height="21">$15,000-24,999 </td> <td class="xl31" style="width: 129pt;" x:num="0.06" width="172"> 6%</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="height: 15.75pt; width: 79pt;" x:num="25000" width="105" height="21">$25,000+ </td> <td class="xl31" style="width: 129pt;" x:num="0.08" width="172"> 8%</td> </tr> </tbody></table>
So you buy $5K you get 4% off your order total at full price, Order $10K get 5% off full price, etc

The columns in the form would be: Product, Full Price, Quantity Ordered, Price with applied Discount -

In effect the total on the bottom will have the if statement and apply the discount to another cells. The preceding cells will have the product numbers and the customer inputs the quantity, with the price being figured in the column next to it with the appropriate discount based on the total.

It would be great to lock the cells so the customer cannot change the formula.
<table x:str="" style="border-collapse: collapse; width: 790px; height: 101px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 74pt;" width="98"><col> <tbody><tr><td class="xl26" style="height: 56.25pt; width: 74pt;" width="98" height="75">Item </td> <td class="xl30" style="border-left: medium none; width: 50pt;" x:str="Full Price" width="66">
</td> <td style="width: 54pt;" valign="top" width="72" align="left"><!--[if gte vml 1]><v:shapetype id="_x0000_t201" coordsize="21600,21600" o:spt="201" path="m,l,21600r21600,l21600,xe"> <v:stroke joinstyle="miter"/> <v:path shadowok="f" o:extrusionok="f" strokeok="f" fillok="f" o:connecttype="rect"/> <o:lock v:ext="edit" shapetype="t"/> </v:shapetype><v:shape id="_x0000_s1026" type="#_x0000_t201" style='position:absolute; margin-left:33.75pt;margin-top:-1.5pt;width:24pt;height:17.25pt;z-index:1; mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f" strokecolor="windowText [64]" o:insetmode="auto"> <v:path shadowok="t" strokeok="t" fillok="t"/> <o:lock v:ext="edit" rotation="t"/> <v:textbox style='mso-direction-alt:auto' o:singleclick="f"> <![if excel]>
<![endif]></v:textbox> <![if excel]><x:ClientData ObjectType="Checkbox"> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:AutoLine>False</x:AutoLine> <x:TextVAlign>Center</x:TextVAlign> <x:NoThreeD/> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1027" type="#_x0000_t201" style='position:absolute; margin-left:87.75pt;margin-top:-1.5pt;width:24pt;height:17.25pt;z-index:2; mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f" strokecolor="windowText [64]" o:insetmode="auto"> <v:path shadowok="t" strokeok="t" fillok="t"/> <o:lock v:ext="edit" rotation="t"/> <v:textbox style='mso-direction-alt:auto' o:singleclick="f"> <![if excel]>
<![endif]></v:textbox> <![if excel]><x:ClientData ObjectType="Checkbox"> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:AutoLine>False</x:AutoLine> <x:TextVAlign>Center</x:TextVAlign> <x:NoThreeD/> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1030" type="#_x0000_t201" style='position:absolute; margin-left:87.75pt;margin-top:-1.5pt;width:24pt;height:18pt;z-index:5; mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f" strokecolor="windowText [64]" o:insetmode="auto"> <v:path shadowok="t" strokeok="t" fillok="t"/> <o:lock v:ext="edit" rotation="t"/> <v:textbox style='mso-direction-alt:auto' o:singleclick="f"> <![if excel]>
<![endif]></v:textbox> <![if excel]><x:ClientData ObjectType="Checkbox"> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:AutoLine>False</x:AutoLine> <x:TextVAlign>Center</x:TextVAlign> <x:NoThreeD/> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1028" type="#_x0000_t201" style='position:absolute; margin-left:140.25pt;margin-top:-1.5pt;width:24pt;height:17.25pt;z-index:3; mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f" strokecolor="windowText [64]" o:insetmode="auto"> <v:path shadowok="t" strokeok="t" fillok="t"/> <o:lock v:ext="edit" rotation="t"/> <v:textbox style='mso-direction-alt:auto' o:singleclick="f"> <![if excel]>
<![endif]></v:textbox> <![if excel]><x:ClientData ObjectType="Checkbox"> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:AutoLine>False</x:AutoLine> <x:TextVAlign>Center</x:TextVAlign> <x:NoThreeD/> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1029" type="#_x0000_t201" style='position:absolute; margin-left:195.75pt;margin-top:-1.5pt;width:24pt;height:17.25pt;z-index:4; mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f" strokecolor="windowText [64]" o:insetmode="auto"> <v:path shadowok="t" strokeok="t" fillok="t"/> <o:lock v:ext="edit" rotation="t"/> <v:textbox style='mso-direction-alt:auto' o:singleclick="f"> <![if excel]>
<![endif]></v:textbox> <![if excel]><x:ClientData ObjectType="Checkbox"> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:AutoLine>False</x:AutoLine> <x:TextVAlign>Center</x:TextVAlign> <x:NoThreeD/> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1032" type="#_x0000_t201" style='position:absolute; margin-left:195.75pt;margin-top:-1.5pt;width:24pt;height:18pt;z-index:6; mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f" strokecolor="windowText [64]" o:insetmode="auto"> <v:path shadowok="t" strokeok="t" fillok="t"/> <o:lock v:ext="edit" rotation="t"/> <v:textbox style='mso-direction-alt:auto' o:singleclick="f"> <![if excel]>
<![endif]></v:textbox> <![if excel]><x:ClientData ObjectType="Checkbox"> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:AutoLine>False</x:AutoLine> <x:TextVAlign>Center</x:TextVAlign> <x:NoThreeD/> </x:ClientData> <![endif]></v:shape><![endif]--><!--[if !vml]--><!--[endif]-->

</td> <td class="xl29" style="border-left: medium none; width: 54pt;" x:str="$10,000 Order = 5% Off" width="72"> $10,000 Order = 5% Off

</td> <td class="xl29" style="border-left: medium none; width: 54pt;" x:str="$15,000 Order = 6% Off" width="72"> $15,000 Order = 6% Off

</td> <td class="xl29" style="border-left: medium none; width: 54pt;" x:str="$25,000 Order = 8% Off" width="72"> $25,000 Order = 8% Off

</td> <td class="xl26" style="border-left: medium none; width: 38pt;" width="50">Qty

</td> <td class="xl28" style="border-left: medium none; width: 83pt;" x:str="Total" width="110"> Total

</td> </tr></tbody><col style="width: 50pt;" width="66"><col style="width: 54pt;" span="4" width="72"><col style="width: 38pt;" width="50"><col style="width: 83pt;" width="110"></table>
If I am unclear about anything please let me know or if you need more details please advise.

Thanks in advance -
*edited by Admin to remove filepaths
 
Last edited by a moderator:

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.
This calculates the discount percentage for you

=LOOKUP(A1,{0,5000,10000,15000,25000},{0,0.04,0.05,0.06,0.08})
 
Upvote 0
Sorry had a formatting error -



I was asked to help out with a form to tabulate sales orders.
What I think I need is a four condition if statement. The structure of the promotion is that if you order x amount you get x discount. There are four ranges that follow:<o:p></o:p>
<table class="MsoNormalTable" style="width: 208pt; border-collapse: collapse;" u1:str="" border="0" cellpadding="0" cellspacing="0" width="277"> <col style="width: 79pt;" width="105"><col style="width: 129pt;" width="172"> <tbody><tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" width="105"> <o:p> </o:p>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" width="172"> <o:p> </o:p>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" width="105"> Order *<o:p></o:p>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" width="172"> Product Discount****<o:p></o:p>
</td> </tr> <tr style="height: 13.5pt;"> <td style="padding: 0in; width: 79pt; height: 13.5pt;" width="105"> <o:p> </o:p>
</td> <td style="padding: 0in; width: 129pt; height: 13.5pt;" width="172"> <o:p> </o:p>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" u1:num="5000" width="105"> $5,000-9,999 <o:p></o:p>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.04" width="172"> 4%<o:p></o:p>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" u1:num="10000" width="105"> $10,000-14,999 <o:p></o:p>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.05" width="172"> 5%<o:p></o:p>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" u1:num="15000" width="105"> $15,000-24,999 <o:p></o:p>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.06" width="172"> 6%<o:p></o:p>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" u1:num="25000" width="105"> $25,000+ <o:p></o:p>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.08" width="172"> 8%<o:p></o:p>
</td> </tr> </tbody></table>
So you buy $5K you get 4% off your order total at full price, Order $10K get 5% off full price, etc

The columns in the form would be: Product, Full Price, Quantity Ordered, Price with applied Discount -

In effect the total on the bottom will have the if statement and apply the discount to another cells. The preceding cells will have the product numbers and the customer inputs the quantity, with the price being figured in the column next to it with the appropriate discount based on the total.

It would be great to lock the cells so the customer cannot change the formula.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
If I am unclear about anything please let me know or if you need more details please advise.

Thanks in advance - <o:p></o:p>
<o:p> </o:p>
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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