I'm struggling to solve a calculation problem in Excel which seems to be harder than I thought.. and I just can't find the approach I need on the internet. I will present a simplified version of what I want to get and would be eternally grateful to whoever can help me get the result.
我正在努力用Excel解决一个似乎比我想象的要难的计算问题。我在网上找不到我需要的方法。我将呈现一个我想要得到的简化版本，并将永远感谢任何能够帮助我得到结果的人。
I have the following row:
我有下面一行:
100 // 20 / 30 / 10 / 60 / 20 / 80
100 // 20 / 30 / 10 / 60 / 20 / 80。
What I would like to see is:
我想看到的是:
100 // 20 / 30 / 10 / 40 / 0 / 0
100 / 20 / 30 / 10 / 40 / 0 / 0
So the first column is a total and for all the next columns, I have time-distributed values that should add up to this total and return the value that is in the cell, and at some point the amount that is needed to equal the 100 in the first cell. After that, the threshold is reached and it should return zero's.
第一列是total，对于所有的下一列，我有时间分布的值应该加到这个总数中并返回单元格里的值，在某个点上，在第一个单元格中需要等于100的量。在那之后，阈值达到，它应该返回0。
Is there some formula for this I don't know about or an elegant way of solving this problem? Help is very much appreciated!
我不知道有什么公式可以解决这个问题吗?非常感谢您的帮助!
1 个解决方案
#1
1
If you have your first row going from A2:G2 in Sheet1 (100 / 20 / 30 / 10 / 60 / 20 / 80, respectively), you could use the following by entering the formula in cell B2 of Sheet2 and dragging to G2.
如果第一行从Sheet1中的A2:G2(分别为100 / 20 / 30 / 10 / 60 / 20 / 80)开始，您可以使用以下方法，在Sheet2的cell B2中输入公式，并拖动到G2。
=IF(COLUMN(B2)=2,'Sheet1'!B2,IF('Sheet1'!B2+SUM(A2:$B2)>='Sheet1'!$A2,'Sheet1'!$A2-SUM(A2:$B2),'Sheet1'!B2))
#1
1
If you have your first row going from A2:G2 in Sheet1 (100 / 20 / 30 / 10 / 60 / 20 / 80, respectively), you could use the following by entering the formula in cell B2 of Sheet2 and dragging to G2.
如果第一行从Sheet1中的A2:G2(分别为100 / 20 / 30 / 10 / 60 / 20 / 80)开始，您可以使用以下方法，在Sheet2的cell B2中输入公式，并拖动到G2。
=IF(COLUMN(B2)=2,'Sheet1'!B2,IF('Sheet1'!B2+SUM(A2:$B2)>='Sheet1'!$A2,'Sheet1'!$A2-SUM(A2:$B2),'Sheet1'!B2))