+ Reply to Thread
Results 1 to 4 of 4

Calculating a "total" when a cost changes between ranges

  1. #1
    Registered User
    Join Date
    07-08-2019
    Location
    Hillsboro, Oregon
    MS-Off Ver
    365
    Posts
    28

    Calculating a "total" when a cost changes between ranges

    Hello Everyone,

    My apologies in advance for the title to this post as it may not be 100% indicative of what I am trying to achieve here. On paper, I know exactly how to make a calculation like this, but I am struggling and possibly overcomplicating how I need to do this.

    In short, I am attempting to create a very specific cost calculator for specific teams in my organization. They will only need to input 2 values (a specific "volume type" and a "size input") and the rest should be output for them. I have most of my logic down at this point but was recently told that one of the costs we are calculating is not as simple as what I was thinking.

    For context, I originally interpreted it as being if X number was between a specific range (0 to 32000, 32001 to 64000, 64000+), the cost per X would be calculated by a specific number. Thus I would only need to evaluate where that number falls under. However, I was told now that if that value of X was to be something like 36000, I would need to essentially calculate 32000 times one cost and the remaining 4000 by another cost to get that total. See the little cost matrix I put together for reference.

    Cost Matrix
    0.065 0-32000
    0.046 32001-64000
    0.032 >64000

    I'm not sure how I can easily make a calculation for this. Do I do it based on some type of matrix/table? Any assistance would be appreciated. Sample workbook included for reference. Any guidance would be greatly appreciated as I am 90% sure I am over-thinking this. The 2 inputs I mentioned above are cells D3 and D4. The logic that would need to be changed to accommodate this is in cell D9.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculating a "total" when a cost changes between ranges

    In H5

    =SUMPRODUCT((H3>=L3:L5)*(H3-L3:L5),N3:N5)

    See table in L3:N5

    See here for explanation:

    http://www.mcgimpsey.com/excel/variablerate.html
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    07-08-2019
    Location
    Hillsboro, Oregon
    MS-Off Ver
    365
    Posts
    28

    Re: Calculating a "total" when a cost changes between ranges

    Major thanks for the quick support, JohnTapley!

    This is an excellent link you directed me to that helps explain it. I think I need to let the logic "sink in" a little, but the concept makes perfect sense. I admittedly didn't conceptualize the idea for the negative decimal values starting from 0.065 (-.019 and -.014) and that would have been crucial to making this work. I rarely worked with SUMPRODUCT as well but I believe what I was thinking of would need it in some respect.

    One last question:
    Since technically the costs are between 0-36000, 36001-64000, and 64001+... would I need to adjust the cost matrix values of L3:L5 to 0, 32001, and 64001? Or am I thinking through this incorrectly?

    Thank you again!! I really appreciate your help (especially the link to learn this concept a bit better).

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculating a "total" when a cost changes between ranges

    No ... leave the matrix as-is!

+ 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. Replies: 8
    Last Post: 01-31-2014, 03:20 PM
  2. [SOLVED] Pasting values between ranges in different workbooks: "Range" works but "Cells" doesn't
    By Flaubert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2013, 01:19 PM
  3. Calculating total cost from range of cell tally's by cost of item.
    By patrickdjames in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 11-04-2012, 10:27 AM
  4. [SOLVED] Macro to delete entire row if cell contains the word "total" or "Total"
    By theatricalveggie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2011, 12:38 PM
  5. [SOLVED] Excel 2003 VBA - "Maximizing" Window & "Calculating" Workbook
    By JingleRock in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2006, 12:10 AM
  6. Linking two "total" pages to create a "Complete Total" page
    By Jordon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-10-2006, 07:20 PM

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