Context / Example

I am working with some datasets that have incrementing numbers¹. For example, I mean where the cost of an upgrade gets more and more expensive for each level you go up.

We can describe the cost formula for a single upgrade as Cost = 150 ([Level] * 100), however I am struggling a bit when trying to work out values over ranges (i.e. summing the cumulative cost of multiple upgrades)...

1️⃣ Summing Cost

How do I work out the total cost of doing multiple upgrades -- e.g. in this case, going from level 4 to 7 would cost a total of 550 650 750 = 1950

Please can you provide the Excel formula for this type of calculation

2️⃣ Finding a value

Another thing I have been asked to work out is essentially the inverse... i.e. given a starting point/level, at what point does the cumulative cost exceed the number of units available.

In this case, Num upgrades = 2 | 1500 > 1200 (550 650)
and Needed for next level = 450 | 750 - (1500 - 1200)

Please can you provide the Excel formulas for these two types of calculations

¹ If there is a better term for 'incrementing numbers' please let me know and am happy to revise the question.

LAMBDA TAG: I believe this may involve the lambda function, so have added that tag to this post. If this is not right, please let me know and I can remove the tag.

CodePudding user response：

For you r first problem FILTER() may work.

=SUM(FILTER(B2:B7,(A2:A7>=E2)*(A2:A7<E3)))

CodePudding user response：

Here is one option:

Formula in B3:

=SUM(SEQUENCE(B2-B1,,B1)*100 150)

Formula in E3:

=LET(z,SCAN(0,SEQUENCE(E2/150,,E1),LAMBDA(a,b,a b*100 150)),y,XMATCH(E2,z,-1),VSTACK(y,ABS(((E1 y)*100 150)-INDEX(z,y))))