英文:
Find centre of gravity of a column of data representing weights on a bar
问题
我正在分析一些离散信号信息，有两列数据。第一列是赫兹，从105到115。第二列是信号强度，取值介于0和1之间。例如：
| 赫兹 | 权重 |
|---|---|
| 105 | 0.0 |
| 106 | 0.2 |
| 107 | 0.1 |
| 108 | 0.4 |
我想找到加权中心值：也就是说，如果这些是权重，在以赫兹标记的条上，我想找到条平衡的点。
理想情况下，我想要一个公式放在第三列，给我在那一点的总扭矩，然后我只需要寻找该列中的最小值。这也会让我能够绘制每个赫兹值上的“扭矩”。如果不行，任何直接带我到平衡点赫兹值的公式都足够好。
如果我在一行中放入一个公式来找到该赫兹值上的扭矩，它必须将第二列中的每个信号强度与该行的赫兹差值相乘。例如，对于第107行的扭矩公式是（来自105的扭矩）+（来自106的扭矩）+（来自108的扭矩）。例如，来自106的扭矩将是0.2 *（106 - 107）。但是，我不能手动编写这个公式，因为我有180行的数据。我怀疑我需要以某种方式使用矩阵乘法，但是我看不到如何做。
英文:
I'm analyzing some discrete signal information, and have two columns of data. Column 1 is hertz, from 105 to 115. Column 2 is a signal strength -- a number between 0 and 1. For example:
| Hertz | Weight |
|---|---|
| 105 | 0.0 |
| 106 | 0.2 |
| 107 | 0.1 |
| 108 | 0.4 |
I want to find the weighted centre value: that is, if these were weights, on a bar marked with Hertz, then I want to find the point at which the bar will balance.
Ideally I would like a formula in a third column that would give me the total torque at that point -- then I would just be looking for the minimum value in that column. That would also allow me to chart the "torque" at each Hertz value. Failing that, any formula that takes me directly to the balance point Hertz value would be good enough.
If I put in a formula on one row to find that torque at that Hertz value, it has to multiply each signal strength in column two by the difference in Hertz from that row. E.g. the formula for the torque in row 107 is (torque from 105) + (torque from 106) + (torque from 108). Torque from 106, for example, would be 0.2 * (106 - 107). I can't write that formula manually, though, as I have 180 rows of data. I suspect I need to use matrix multiplication in some way but am not seeing how.
答案1
得分: 1
你想要的是 =SUMPRODUCT((赫兹数组)*(权重数组-当前重量))
=SUMPRODUCT(($B$2:$B$5)*($A$2:$A$5-A4))
英文:
What you want is =SUMPRODUCT((Hertz_array)*(Weight_array-Current_weight))
=SUMPRODUCT(($B$2:$B$5)*($A$2:$A$5-A4))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论