How to declare formulas for banded pricing model?

Question

I am making an application (using JavaScript) where users should be able to create pricing models with a table. A user will have an ability to choose a table model. The table models are internally being referred as tiered and banded models. Users will also be able to expand number of table rows and to manually enter some table values and a volume units based on which a monthly cost will be calculated.

I had to develop simple math formulas for every cell for both models and I managed to do it only for the tiered model. I'm posting an image here as a reference of what I did:

It was decided to use a really powerful JS library for math calculations called mathjs where I was able to produce values in certain cells based on the values from the other cells, much like Excel or Google Sheets do. From the image, it should be clear that users are able to enter numeric values in cells that are marked as null while the other values are the formulas (with the exception of A1 cell which is always set to 0). Also, a user should be able to enter value of the VOLUME variable somewhere outside of the table.

When this is translated to sheets, an example of a table with the real values looks like this:

Based on the values entered in columns B and C and in the VOLUME cell, it is possible to calculate values in other columns: A starting from A2 and entire D, E and F columns. Also, it's possible to calculate Monthly Cost which is a sum of values from the D column.

What I'm trying to do now is to create similar formulas for the banded model. I have to explain how tiered model works so that I can explain how the banded model should be constructed.

What's similar to both models is that they are charging based on the tier or band (basically a row) a volume units belong to. In the model from the second image (that is using tiered model), for the volume of e.g. 328, the tier is number 4. What's the difference between these two models is that tiered model is charging differently to the banded model for the volume units in previous tiers/bands.

Based on the image, the tiered model is charging the first 50 (B1 cell) volume units (out of these 328) by the cost/unit for that tier which is $0.50 (C1 cell). That is the maximum price shown in Tier Price column which for tier 1 is at cell E1: C1 * B1 which is $0.5 * 50 which is $25. So, the first 50 volume units are charged $25.

The next 50 (B2 - B1) volume units are being charged at cost of $0.45 (C2 cell). Maximum price for these 50 volume units is at cell E2: C2 * (B2 - B1) which is $0.45 * (100 - 50) which is $22.5.

The cost for these first 100 volume units is basically the sum of the tier costs of both tiers. That is at cell F2: $25 + $22.5 which is $47.5.

The next 150 (B3 - B2) volume units belong to tier 3 and are being charged at cost of $0.40 (C3 cell). Maximum price for these 150 volume units is at cell E3: C3 * (B3 - B2) which is $0.40 * (250 - 100) which is $0.40 * 150 which is $60.

The cost for the first 250 volume units is basically the sum of the tier costs of all three tiers. That is at cell F3: $60 + $47.5 which is $107.5.

Now, the next tier is tier 4 where 328 volume units belong. The first 250 volume units are being charged at price $107.5 while the remaining 78 (328 - 250) volume units are being charged as 78 * $0.35 which is $27.3. Now, the Monthly cost is the sum of $27.3 and $107.5 which is $134.8.

From what is now visible, what Actual price column represents is the cost of the volume units per that tier. If the volume units are larger than the maximum number of volume for that tier (column B), than the Actual price for that tier becomes the Tier price of that tier, but if it's the lower, then it's 0. That's why min and max functions are used.

The banded model is different to tiered model in a way that volume units are being charged at the price from a band that they belong to. I.e. if the table from the above example was using banded model, the monthly cost for 328 value units would be simple: take the cost/unit price of the band it belongs and multiply by the volume. In this case, the band is 4 so the cost/unit for band 4 is $0.35. So, the monthly cost of 328 volume units would be 328 * $0.35 which is $114,8.

What's noticeable is that the banded model is having slightly less monthly cost because all volume units are being charged at the same price, while this is not the case with the tiered model.

Now, what I need is the monthly cost formula for the banded model. For this to work, columns A, B and C which represent From, To and Cost/Unit values are required. The others ones can be made up.

If Actual price column is being used and if the Monthly cost would represent a sum of the Actual price values, then in this example of 328 volume units I need the following:

• the values of Actual price cells that don't belong to tier 4 should be zeroes;
• and the value of Actual price cell that belongs to tier 4 should be 328 * $0.35 which is $114,8.

Like this:

Basically, based on the VOLUME variable, I need to determine the band number and use the cost that belongs to that band to multiply it with the VOLUME. How can I achieve this only by using math functions that are supported by the mathjs library?

I'd love to use if statements much like I'm able to use it in Excel/Google Sheets, but the library is determining what I can use.

One note: using min and max functions, I think I managed to do something like this for the tiered model (per a single row/tier):

Now, I think I need this for the banded model (per a single row/band):

Is it possible to achieve this using mathjs functions? Maybe using mod could help here? Or maybe Monthly cost can be written somewhat different and not as a sum of values from the D column?

I am not certain how to achieve this at this moment and I need help. I am very sorry for the long post and thankful in advance for the answers.

Answer 1

I wasn't really paying attention to the mathjs docs and now I have found a way to write conditional expressions which is all what I needed. When I wrote this in the question:

I'd love to use if statements much like I'm able to use it in Excel/Google Sheets, but the library is determining what I can use.

But I was wrong because mathjs actually supports if statements, it just have to be written as a ternary operator expression. So, my solution for Monthly cost would be:

VOLUME*(VOLUME<=B1?C1:VOLUME<=B2?C2:VOLUME<=B3?C3:VOLUME<=B4?C4:C5)

With no need to have an Actual price column. Also, if a users adds another row, I'd have to add another ternary condition near the end of the expression - basically replace C5 with VOLUME<=B5?C5:C6.

It appears that this question should've been asked on StackOverflow forum because the solution is purely programming related. However, I'm open to another answers that don't include ternary condition, but use purely math functions, if this is possible.