# Iterative Excel Function to compare against value.

#### schielrn

##### Well-known Member
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​

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Eric W

##### MrExcel MVP
Something like this:

Book1 (version 1).xlsb
ABCDEFG
1Original balanceDay 1Day 2Day 3Day 4# of days before <=0# of full days
2100020040060010032
340020040060010021
410000200400600100No Negative Balance4
Sheet39
Cell Formulas
RangeFormula
G2:G4G2=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

##### Well-known Member
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.

Replies
3
Views
85
Replies
12
Views
189
Replies
0
Views
149
Replies
24
Views
520
Replies
13
Views
277

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back