yblanchette
New Member
- Joined
- Feb 26, 2008
- Messages
- 7
I have this formula that is not quite working and need help...
this works fine =IF($B$1<3, 180, 60)
but I am using this cell to multiply by another cell which is qty...
What I want is if the qty is equal to or less than 3 ft. than the price is $180 (minimum charge)... If it is more than 3 the qty is multiplied by $60.
Example A:
No. of FT: 3<TABLE style="WIDTH: 266pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=355 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: #99ccff" width=67 height=63>Price per ft.</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=99>Sub-total</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=96 x:str="Installation ">Installation </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=93>Total</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.95pt; BACKGROUND-COLOR: transparent" height=29 x:num="60">$60.00</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="180">$180.00</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="60">$60.00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right x:num="240" x:fmla="=SUM(B2:C2)">$240.00</TD></TR></TBODY></TABLE>
Example B:
No. of FT: 10 <TABLE style="WIDTH: 266pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=355 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: #99ccff" width=67 height=63>Price per ft.</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=99>Sub-total</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=96 x:str="Installation ">Installation </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=93>Total</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.95pt; BACKGROUND-COLOR: transparent" height=29 x:num="60">$60.00</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="180">$600.00</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="60">$60.00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right x:num="240" x:fmla="=SUM(B2:C2)">$660.00</TD></TR></TBODY></TABLE>
Thanks in advance for your help.
Yvan
this works fine =IF($B$1<3, 180, 60)
but I am using this cell to multiply by another cell which is qty...
What I want is if the qty is equal to or less than 3 ft. than the price is $180 (minimum charge)... If it is more than 3 the qty is multiplied by $60.
Example A:
No. of FT: 3<TABLE style="WIDTH: 266pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=355 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: #99ccff" width=67 height=63>Price per ft.</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=99>Sub-total</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=96 x:str="Installation ">Installation </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=93>Total</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.95pt; BACKGROUND-COLOR: transparent" height=29 x:num="60">$60.00</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="180">$180.00</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="60">$60.00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right x:num="240" x:fmla="=SUM(B2:C2)">$240.00</TD></TR></TBODY></TABLE>
Example B:
No. of FT: 10 <TABLE style="WIDTH: 266pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=355 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: #99ccff" width=67 height=63>Price per ft.</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=99>Sub-total</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=96 x:str="Installation ">Installation </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=93>Total</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.95pt; BACKGROUND-COLOR: transparent" height=29 x:num="60">$60.00</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="180">$600.00</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="60">$60.00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right x:num="240" x:fmla="=SUM(B2:C2)">$660.00</TD></TR></TBODY></TABLE>
Thanks in advance for your help.
Yvan