2

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.

Level Cost
4 550
5 650
6 750
7 850
8 950
9 1050

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

Input/Output Value
Current Level: 4
Desired Level: 7
TOTAL 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

Input/Output Value
Current Level: 4
Units Available: 1,500
NUM UPGRADES: ???
NEEDED FOR NEXT LEVEL: ???

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.

Guy Coder
  • 24,501
  • 8
  • 71
  • 136
Martin
  • 280
  • 1
  • 10
  • If desired level is `7` then it should be 4,5,6,7 `550+650+750+850`? – Harun24hr Dec 05 '22 at 10:41
  • To clarify, the cost is what it takes to complete the given level, so think of 4 as more "4 --> 5". – Martin Dec 05 '22 at 11:07
  • The `lambda` function and `Lambda Calculus` are not the same. Replaced with correct tag. – Guy Coder Dec 06 '22 at 14:25
  • Why not just get the [Amulet of Zenithar](https://en.uesp.net/wiki/Skyrim:Specialty_Gear#Amulet_of_Zenithar) then `Prices are 10% better` so you can level of faster. :) – Guy Coder Dec 06 '22 at 14:32
  • `If there is a better term for 'incrementing numbers' ` - [Recurrence relation](https://en.wikipedia.org/wiki/Recurrence_relation) comes to mind. – Guy Coder Dec 06 '22 at 14:37

2 Answers2

1

Here is one option:

enter image description here

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))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
0

For you r first problem FILTER() may work.

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Thanks, I take with this approach I would need to create an interim table to `FILTER`?... I did consider that however I was reluctant to go down this road as (1) I am expecting to have many different models by the end (so Cost2, Cost3, Cost 3B, etc) and also (2) I don't know the max level, so there could be tens or hundreds of rows needed... I suppose for now, this might have to be 'Plan B'(?) – Martin Dec 05 '22 at 11:11