Pick cost based on value

Hi All,

I've been racking my brain for the last few hours and have finally admitted defeat and need to ask for some help from the Excel gods. I am creating an Order Form for my business and have just added a section for the shipping cost. I think I have done it half way but I just cannot get over this last hurdle. So what I want the charges to be is that items totalling less than £100, incurs a £5.99 shipping cost. Orders over £100, the shipping cost will be free. Rightly or wrongly, I have used the formula below and it has kinda done what I have asked for.

=IF(I47<100,"£5.99","£4")

However, I would like it if the cell for the shipping costs, was £0 until someone has put an item in the basket. At the moment, without anything being choose, the total already says £5.99 as it is less than £100.

Any help would be so much appreciated!

Thanks,

Kev

do you mean =IF(I47="","",IF(I47<100,"£5.99","£4"))
or if you want it to be zero then... =IF(I47="",0,IF(I47<100,"£5.99","£4"))

By the way, these as outputs "£5.99","£4" will be text because of the quotes. You ought to change them to 5.99 and 4 without the quotes in case you want to do any downstream functions with them and then format the cells as currency so £ will appear with the results.

I'm struggling to see where the 4 quid is coming from. Do you mean orders 100 quid+ are Free, or 4 quid? ;-)

If free, this is the formula I would use.. =IF(OR(I47=0,I47>=100),0,5.99)

Format your cells as currency and that should be exactly what you want.

Is this what you want Kev?

=IF(I47>=100,"£0", IF(I47=0,0,5.99)) in the shipping cost cell.

Don't forget though to put in a similar formula (to check for your total order being more than 100 quid) wherever you use shipping and handling costs (i.e. for VAT and your net total).

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.