CODEWITHSUNDEEP
excelexcel-formulaformulatiers
Skeeve
Skeeve

Reputation: 169

How can I determine the 'total cost' from a tiered pricing structure using standard formulas in Excel?

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

Edit (16-Jul-2024): Still trying to get the 'split' sorted with no joy...

I have something close... but it's still not right... If anyone might help, please.

Sample .xlsx file via Dropbox

Thanks.

Upvotes: -1

Views: 1359

Answers (1)

Solar Mike
Solar Mike

Reputation: 8375

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.

Upvotes: 0

Related Questions