Results 1 to 3 of 3

Thread: Help to price up a commodity using formulae within Excel Spreadsheet

  1. #1
    Neophyte Solly's Avatar
    Join Date
    Dec 2020
    Posts
    4
    Articles
    0
    Excel Version
    Microsoft Excel Worksheet

    Smile Help to price up a commodity using formulae within Excel Spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello guys - i'm a bit of a novice when it comes to Excel but would really appreciate some help with pricing up our commodity. When someone buys 750,000 or 1,000,000 or 3,000,000 etc items we charge in a specific way. The first 500,000 are charged at 1%, the second 500,000 are charged at 0.75%, the next million at 0.5%, the next million at 0.25% and so on an so on

    What i'd like to be able to do is to just put one number in and it automatically breaks it all down and charges the correct price at the end.

    Is that possible at all ?

    Much thanks

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    Assuming the commodity amount is in A1, you can use this to calculate the charge

    =SUMPRODUCT(--(A1>{0;500;1000;2000}*1000), (A1-{0;500;1000;2000}*1000), {1;-0.25;-0.25;-0.25}/100)

  3. #3
    Neophyte Solly's Avatar
    Join Date
    Dec 2020
    Posts
    4
    Articles
    0
    Excel Version
    Microsoft Excel Worksheet
    Very kind of you Bob. Thanks

Posting Permissions

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