Multi-condition if statements for sales order form??

[datadigger]

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"/> <vath 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"> <vath 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"> <vath 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"> <vath 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"> <vath 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"> <vath 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"> <vath 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

 

[xld]

This calculates the discount percentage for you

=LOOKUP(A1,{0,5000,10000,15000,25000},{0,0.04,0.05,0.06,0.08})

 

[datadigger]

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></o>
<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> </o>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" width="172"> <o> </o>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" width="105"> Order *<o></o>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" width="172"> Product Discount****<o></o>
</td> </tr> <tr style="height: 13.5pt;"> <td style="padding: 0in; width: 79pt; height: 13.5pt;" width="105"> <o> </o>
</td> <td style="padding: 0in; width: 129pt; height: 13.5pt;" width="172"> <o> </o>
</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></o>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.04" width="172"> 4%<o></o>
</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></o>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.05" width="172"> 5%<o></o>
</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></o>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.06" width="172"> 6%<o></o>
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0in; width: 79pt; height: 15.75pt;" u1:num="25000" width="105"> $25,000+ <o></o>
</td> <td style="padding: 0in; width: 129pt; height: 15.75pt;" u1:num="0.08" width="172"> 8%<o></o>
</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></o>
<o> </o>
<o> </o>
If I am unclear about anything please let me know or if you need more details please advise.

Thanks in advance - <o></o>
<o> </o>

 

[datadigger]

Thanks xld - worked perfectly