using IF with a {>= but <= } range

[wolfsoul]

I have a spreadsheet that is an order form. There is a
range of shipping charges (i.e. if you order totals:
$.0.01-25.00 then the S&H is 2.50
$25.01-50.00 then S&H is $3.50
$50.01-150 then S&H is 7% of total
$150 & up then S&H is 6%

I want the spreadsheet to automatically calulate the S&H
charges for the order total and enter that value in the
cell. The first and last S&H charges are easily written as
a formula, but when I use the >= & <= functions in the
same formula nothing is entered in the cell.
Here's how I wrote the formulas....
=IF(E106>25<=50,3.50,0) (for second choice [25.01-50.00]
=IF(E106>50<=150,E106*7%,0) (for third choice)

I don't receive an error message for my formulas...but
there is no value entered in the cell when the order total
(at E106) equals either between $25-$50 or between $50-
$150. Can someone please tell me how to make the formula
work. I tried just using the "less then" value but then I
get a response in 3 of the cells instead of just the one I
need the response in.
Thanks for any assistance.
Wolfsoul

 

[Biff]

Hi Wolfsoul,

Here's one way:

E1 = total

=IF(E1="","",IF(E1<=25,2.5,IF(E1<=50,3.5,IF
(E1<=150,E1*0.07,E1*0.06))))

Biff

 

[A.W.J. Ales]

Hi Wofsoul,

Adding to Biff's correct solution

To test for a cell to have a value between two given values you should use a
formula like :

=IF(AND(E106>25,E106<=50),3.50,0) ( in stead of : =IF(E106>25<=50,3.50,0)
(for second choice [25.01-50.00] )

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Hi Wolfsoul,

Here's one way:

E1 = total

=IF(E1="","",IF(E1<=25,2.5,IF(E1<=50,3.5,IF
(E1<=150,E1*0.07,E1*0.06))))

Biff

-----Original Message-----
I have a spreadsheet that is an order form. There is a
range of shipping charges (i.e. if you order totals:
$.0.01-25.00 then the S&H is 2.50
$25.01-50.00 then S&H is $3.50
$50.01-150 then S&H is 7% of total
$150 & up then S&H is 6%

I want the spreadsheet to automatically calulate the S&H
charges for the order total and enter that value in the
cell. The first and last S&H charges are easily written as
a formula, but when I use the >= & <= functions in the
same formula nothing is entered in the cell.
Here's how I wrote the formulas....
=IF(E106>25<=50,3.50,0) (for second choice [25.01-50.00]
=IF(E106>50<=150,E106*7%,0) (for third choice)

I don't receive an error message for my formulas...but
there is no value entered in the cell when the order total
(at E106) equals either between $25-$50 or between $50-
$150. Can someone please tell me how to make the formula
work. I tried just using the "less then" value but then I
get a response in 3 of the cells instead of just the one I
need the response in.
Thanks for any assistance.
Wolfsoul
.