Iterative Excel Function to compare against value.

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.

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
Joined
Aug 18, 2015
Messages
11,812
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.
 
Solution

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top