英文:
Converting a die-roll "equation" into an average result?
问题
I can help you with the translation:
我正在处理一份电子表格，以计算各种Pathfinder 2e角色建模的伤害输出。我已经基本搞定了，但我想找出一种将伤害骰子公式（例如“1d8+5+2d6”）转换成平均结果（例如“16.5”）的方法。
我认为将问题中的公式拆分为其各个组成部分，然后再将它们相加起来应该很容易（应该只需使用=SPLIT函数，然后跟着一个=SUM函数）。困难的部分是将骰子投掷本身转换为平均值，因为我需要一种方法，让Google文档将字符串“XdY”转换为((Y/2) + 0.5) * X。
有没有一种简单的方法可以做到这一点，而不需要在多个单元格之间链接变量？理想情况下，我希望有一个公式，将伤害骰子公式作为输入，并将平均值作为输出输出。
英文:
I'm working on a spreadsheet to figure out the damage output of various Pathfinder 2e builds. I've got most of it down, but I want to figure out a way to convert a damage-roll equation (e.g "1d8+5+2d6") into an its average result (e.g "16.5").
I figure splitting the equation in question into its individual components and then adding them back together is pretty easy (it should just be a =SPLIT function followed by a =SUM function). The hard part is converting the die-rolls themselves into averages, because I need some way for google docs to convert the string "XdY" into ((Y/2) + 0.5) * X.
Is there an easy way to do this without needing to chain variables across multiple cells? Ideally I'd like one formula that takes the damage-roll equation as an input and spits the average out as an output.
答案1
得分: 0
使用A1单元格中的掷骰子方程式，您可以尝试：
=arrayformula(let(
splitarray,split(transpose(split(A1,"+")),
"d"),
padwith1s,if(splitarray,splitarray,1),
sum(choosecols(padwith1s,1)*((choosecols(padwith1s,2)/2)+0.5))))
注意：此公式将常数有效地视为单面骰子（例如，您的 '5' 被视为 '5d1'）。这简化了数组的评估，因为然后可以在每行上执行相同的操作 - 从数学上讲，结果是相同的（因为（1/2）+0.5=1）。
英文:
With your dice-rolling equation in A1 you can try:
=arrayformula(let(
splitarray,split(transpose(split(A1,"+")),"d"),
padwith1s,if(splitarray,splitarray,1),
sum(choosecols(padwith1s,1)*((choosecols(padwith1s,2)/2)+0.5))))
N.B. this formula effectively treats constants as a 1-sided die (e.g. your '5' is evaluated as if it were '5d1'). This simplifies the evaluation of the array as the same operation can then be done on each row - mathematically the result is identical (as (1/2)+0.5=1).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论