# Pick cost based on value

1. ## 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

2. ## Re: Help with Excel Formula

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"))

3. ## Re: Help with Excel Formula

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.

4. ## Re: Pick cost based on value

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.

5. ## Re: Pick cost based on value

Hi guys,

Thank you for taking the time out to help me with this problem.

Sambo Kid, unfortunately, your method gave me the same results as to what I was getting where a zero was still not appearing and that
the £5.99 was showing before any order was placed. I have taken your suggestion with regards to the £ sign and have removed this
from the formula.
Thank you.

Croweater, thank you for your reply. I must apologise as initially, the shipping cost was £5.99 and then reduced to £4 with orders over
£100. I have decided to change this to free shipping with orders over £100. Your method worked with what I was trying to achieve in
the instance that the shipping charge cell was blank until an order was placed. However, would it be possible to further amend this
formula as at the moment using your formula, the cell turn blank when orders over £100 is met.
I would like the cell to show £0 or even free (is it is easier) when orders over £100 are met.

Thank you so much guys,

Kev

6. ## Re: Pick cost based on value

I would say it is something to do with the formatting of the shipping charge cell, although I must admit I can't even get it to show a blank with any standard format.

If you post the spreadsheet here, I will have a look at it. Instructions are at the top on how to do that.

7. ## Re: Pick cost based on value

Hi Croweater,

I have attached the spreadsheet as you have suggested.

Thanks again,

Kev

8. ## Re: Pick cost based on value

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).

9. ## Re: Pick cost based on value

Ah man this is exactly what I was after!! Thank you so so much!! I really appreciate all your help!

Kind regards,

Kev

10. ## Re: Pick cost based on value

No problems at all.

11. ## Re: Pick cost based on value

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1