Iterative Excel Function to compare against value.

[schielrn]

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.

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​

 

[Eric W]

Something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	Original balance	Day 1	Day 2	Day 3	Day 4	# of days before <=0	# of full days
2	1000	200	400	600	100	3	2
3	400	200	400	600	100	2	1
4	10000	200	400	600	100	No Negative Balance	4
Sheet39

Cell Formulas
Range	Formula
G2:G4	G2	=IFERROR(MATCH(A2,SUBTOTAL(9,OFFSET(B2,0,0,1,COLUMN(B2:E2)-COLUMN(B2)+1))),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

I can probably tweak it to match your output.

 

[schielrn]

Thank you very much for this. It got me on the right path. I tweaked it partially to:

=IFERROR(XMATCH(A2,SUBTOTAL(9,OFFSET(A2,0,1,1,COLUMN(B2:E2)-1)),1),"No Negative Match")

This way it returned the No Negative Match.