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
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
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
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).
No ... leave the matrix as-is!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks