I want to have a threshold value in one cell (A1) and take it as a reference for adding cells.
Suppose I have
A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6
I want to add cells based on A1(Threshold).
If A1 is 10, it would add A2:A5, sum = 13
If A1 is 9, it would add A2:A4, sum = 9
The SUM formula is
=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:5),1))
But I have a problem when the sum cannot be reached in interval set in the ROW formula.
In this exsample, if you set ROW to ROW(1:5) and set A1 to 14, you will get 19. So the formula keeps adding even when max ROW is reached.
How can I stop this?
/Asger
111 Answer
If you only have A1 as threshold and then 5 cells with value, you could use this formula in A7
=IF(A2>=A1,A2,IF(SUM(A2:A3)>=A1,SUM(A2:A3),IF(SUM(A2:A4)>=A1,SUM(A2:A4),IF(SUM(A2:A5)>=A1,SUM(A2:A5),SUM(A2:A6)))))It's not very elegant (and not feasibly scalable to much larger sets of data) but should do the trick
It does behave as requested in the OP as it stands right now (13 or 9)
1
