+ Reply to Thread
Results 1 to 11 of 11

Pick cost based on value

  1. #1
    Registered User
    Join Date
    05-14-2020
    Location
    London, England
    MS-Off Ver
    Microsoft Office 10
    Posts
    14

    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
    Last edited by FDibbins; 05-14-2020 at 11:15 PM. Reason: edited thread title

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,846

    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"))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,846

    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. #4
    Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    155

    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.
    Last edited by Croweater; 05-15-2020 at 03:07 AM.

  5. #5
    Registered User
    Join Date
    05-14-2020
    Location
    London, England
    MS-Off Ver
    Microsoft Office 10
    Posts
    14

    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. #6
    Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    155

    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. #7
    Registered User
    Join Date
    05-14-2020
    Location
    London, England
    MS-Off Ver
    Microsoft Office 10
    Posts
    14

    Re: Pick cost based on value

    Hi Croweater,

    I have attached the spreadsheet as you have suggested.

    Thanks again,

    Kev
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    155

    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).
    Attached Files Attached Files
    Last edited by Croweater; 05-17-2020 at 12:49 AM.

  9. #9
    Registered User
    Join Date
    05-14-2020
    Location
    London, England
    MS-Off Ver
    Microsoft Office 10
    Posts
    14

    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. #10
    Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    155

    Re: Pick cost based on value

    No problems at all.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,104

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA, User-defined formula argument referring to cell value calculated by Excel formula
    By ARAGORN II in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2017, 01:57 PM
  2. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  3. Replies: 10
    Last Post: 12-16-2015, 03:16 PM
  4. Replies: 2
    Last Post: 03-21-2014, 11:43 AM
  5. Macro: Applying formula to multiple cells in excel (formula editing)
    By city in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 06:41 AM
  6. Replies: 0
    Last Post: 03-19-2012, 07:32 PM
  7. Replies: 1
    Last Post: 03-04-2012, 12:03 AM

Bookmarks

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