+ Reply to Thread
Results 1 to 4 of 4

Calculating a "total" when a cost changes between ranges

Hybrid View

  1. #1
    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
    30,834

    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.

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

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

  3. #3
    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
    30,834

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