Excel -处理增量数字、序列(求和、查找值等)

k4aesqcs  于 2022-12-14  发布在  其他
关注(0)|答案(2)|浏览(154)

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

zrfyljdw

zrfyljdw1#

以下是一个选项:

B3中的公式:

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

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))))
8qgya5xd

8qgya5xd2#

对于你的第一个问题FILTER()可能工作。

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

相关问题