-1

I'm trying to evaluate various tiered pricing structures (for say, electricity plans) using Excel (more-or-less) to see what costing/plan is 'optimal', given some existing usage data I have.

Consider an example 'Table of Usage & Rates' (with fictitious but easily manipulated values):-

spreadsheet extract

For a daily usage value of 120, we'd have 100 (in the 1st tier) and 20 (in the 2nd tier). The amount used within a tier gets charged at a certain rate (the 'factor')... and each 'tier charge' is addded together to form a total charge for the day.

So, we can calculate:-

100 x 8 = 800    ...a part of the total
 20 x 4 = 80     ...another part of the total

...and that's all, giving a total of 880.

...but how to do that in a single formula within a cell?

I've done some pretty decent explorations for a few hours today, as I can't nut out how to deal with this... and most suggestions talk about multiple =IF formulas (cumbersome and unscalable - I shouldn't need to recode cell contents if I split/add another tier)... and suggestions with =VLOOKUP just don't 'click' with me ( = I don't understand them).

I'm actually using 'PlanMaker', a component of Softmaker's 'Office 2021' product to create/maintain this spreadsheet.. and there is no VBA-like plugin available.

I'd appreciate a method of attack, if anyone can suggest something, please...

Skeeve
  • 159
  • 1
  • 2
  • 11
  • If you provide data that can be copied and pasted, I might build an example. But if you look there are many examples of sumproduct() on here. – Solar Mike Feb 15 '23 at 07:05

1 Answers1

0

So:

=product(10,8)+product(20,4)

or if we assume Factor starts in B9 then =product(A9,B9)+product(A10,B10+product(A11,B11)

then take the sum of those results etc assuming A9 is the amount used.

You can also use:

=sumproduct(A9:A11,B9:B11)

for the same but only needs one cell. And the advantage of a lot less typing.

You can include a 3rd array in sumproduct (or as many as needed) such as a binary value to include in the calculation or not.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • Whilst the =sumproduct() function will likely be helpful, the main issue is how to break the input value according to the 'bins' defined by each tier. For the example shown, how can I use functions/formulas to determine that '100' applies to the 'max of 100' bin... and that '20' applies to the '100 to 200' bin. Again, the 'range extent' of the table should be used in the functions/formulas as that will allow for adding/removing tier definitions. – Skeeve Feb 15 '23 at 06:52
  • So, I did mention that sumproduct() can have a 3rd array or as many more as needed, so you can set constraints to control values to be < or > or not equal to points that you decide. – Solar Mike Feb 15 '23 at 07:02