calculate shipping costs in excel 2007

[JOVA]

I am trying desperately to create a formula in my spreadsheet that will
automatically calculate shipping cost for me based on weight. Here is my
scenario:
I have one column that calculates the weight per item.
I have the total weight entered into one cell (H42) on my sheet.
Based on the weight in cell H42, I need to determine three different factors:
a) If weight is less than 250 pounds, I need cell H42 multiplied by $1
b) If weight is BETWEEN 250 and 999 pounds, I need cell H42 multiplied by
total invoice order in cell AV42 (don't worry ... my columns are all 1 inch
wide and I merged some together)
c) If weight is greater than 1000 lbs, the shipping is a set price.

Any help will be greatly appreciated! I cannot figure this formula out by
using the IF function. I don't know how to set it the values 250<999. Excel
doesn't recognize what I am telling it to do! Please help!

Thanks!

 

[David Biddulph]

I'm assuming that you don't have an endefined (or free?) region between 999
and 1000?

Try =IF(H42<250,H42,IF(H42<=1000,H42*AV42,set_price))

 

[Robbro]

=if(h42<250,h42,if(h42<999,h42*av42,whatever your fixed price is))

 

[Paul C]

Nest 2 if functions

=IF(H42<250,H42*1,IF(H42>999,1500,H42*AV42))

I used the 1500 as the set shipping price, change as needed.