schielrn
Well-known Member
- Joined
- Apr 4, 2007
- Messages
- 6,941
I have been trying to figure out if this can be done easier and in case the list grows to more than 4 cells. Currently we are using Ifs() to determine where a range of cells subtracted from the original cell is 0 or negative:
=IFS(A2<=SUM(B2),1,A2<=SUM(B2:C2),2,A2<=SUM(B2:D2),3,A2<=SUM(B2:E2),4,1=1,"No Negative Balance")
I was trying to figure out a way to build out an array of each sum. This was my thoughts to it, but it doesn't actually create 4 summed values:
=MIN(IF(A2<=SUM(OFFSET(A2,0,1,0,ROW(1:4))),ROW(1:4)))
But I have also tried to think about how to handle "No Negative Balances"
I don't think this is possible, but thought I would pose it to see if it actually is.
=IFS(A2<=SUM(B2),1,A2<=SUM(B2:C2),2,A2<=SUM(B2:D2),3,A2<=SUM(B2:E2),4,1=1,"No Negative Balance")
I was trying to figure out a way to build out an array of each sum. This was my thoughts to it, but it doesn't actually create 4 summed values:
=MIN(IF(A2<=SUM(OFFSET(A2,0,1,0,ROW(1:4))),ROW(1:4)))
But I have also tried to think about how to handle "No Negative Balances"
I don't think this is possible, but thought I would pose it to see if it actually is.
Original balance | Day 1 | Day 2 | Day 3 | Day 4 | # of days before <=0 |
1000 | 200 | 400 | 600 | 100 | 3 |
400 | 200 | 400 | 600 | 100 | 2 |
10000 | 200 | 400 | 600 | 100 | No Negative Balance |