英文:
How to calculate cell values in excel based on the criteria
问题
我正在尝试在Excel中构建逻辑，根据负载条件来更新值。
我已经附上了一个示例，其中第4行应在第3行的单元格总和>=负载值时更新。然后，该逻辑应该取差值，即总数 - 负载，然后重新计算其他单元格。
任何帮助将不胜感激。一直在尝试构建这个逻辑，但一直没有理清头绪。
尝试编写公式，但未获得所需的输出。
英文:
I am trying to a build the logic in Excel where the value gets updated based on the Load criteria .
I have attached an example where row 4 should update once the total of the cells in row 3 is >= Load value. The logic should then take the difference i.e Total - Load and then recalculate for other cells
Any help will be really appreciated. Been trying to build this however not getting my head around .
Tried to write the formulas however not getting the desired output .
答案1
得分: 4
=LET(load, B3, SCAN(0, C3:L3, LAMBDA(a, b, IF(a >= load, MOD(a + b, load), a + b))))
= (C1# >= B3) * B3
=LET(load, B3, (SCAN(0, C3:L3, LAMBDA(a, b, IF(a >= load, MOD(a + b, load), a + b))) >= load) * load)
If two consecutive pays can be greater than the load, as in your later added example, this complicates things and we should have a different approach:
=LET(load, B2,
pay, C2:L2,
first, TAKE(pay, , 1),
REDUCE(IF(first >= load, load, 0), SEQUENCE(, COUNTA(pay) - 1, 2),
LAMBDA(a, b,
LET(c, SUM(INDEX(pay, , SEQUENCE(, b - 1)) - a),
d, c + INDEX(pay, , b),
HSTACK(a, IF(d >= load, load, 0))))))
英文:
You could use SCAN combined with MOD in C1:
=LET(load,B3,SCAN(0,C3:L3,LAMBDA(a,b,IF(a>=load,MOD(a+b,load),a+b))))
and in C4 use: =(C1#>=B3)*B3
Or in one go, without the need of C1:
=LET(load,B3,(SCAN(0,C3:L3,LAMBDA(a,b,IF(a>=load,MOD(a+b,load),a+b)))>=load)*load)
If two consecutive pays can be greater than the load, as in your later added example, this complicates things and we should have a different approach:
=LET(load, B2,
pay, C2:L2,
first, TAKE(pay,,1),
REDUCE( IF(first>=load,load,0), SEQUENCE(,COUNTA(pay)-1,2),
LAMBDA( a, b,
LET( c, SUM(INDEX(pay,,SEQUENCE(,b-1))-a),
d, c + INDEX(pay,,b),
HSTACK( a, IF(d>=load,load,0))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论