Excel Formula for First Instance of Something in Row, Then Top Value in That Column?

June 23, 2011 4:32 AM

Excel question (debt management/budgeting): what formula gets me the first instance of something in a row, and then how I can pull the date from the top of that column? More inside.

I'm looking to write a formula that returns the first instance in which a range of cells add up to zero. Each column is a paycheck, and if those range of cells add up to zero, it means I'm debt-free. The top cell of said column would be the date that I would become debt-free.

My goal is to basically have the spreadsheet present to me in a prominent place that date -- this way, if any budget adjustments make it go further out, it'll give me pause.

So ... is there a formula that will return the cell value of the first instance of something in a row -- and, then, is there a way to go from there to get the value of the top cell in that cell's column?

Thanks, everyone!

posted by WCityMike to computers & internet (8 answers total)

1) Add a row *above* your data.

From here out, I'm going to write a solution assuming your data is in cells A2:F10, and that your dates are in A11:F11. You'll need to adjust accordingly.

2) Enter the following formula in each cell (A1, B1, C1...) of the new row:

=MAX(A2:A10)

3) Set the formula of any cell (outside A1:F11) to:

=HLOOKUP(0,A1:F11,11,FALSE)

That last formula is "horizontal lookup" -- it means, "look for the first 0 you can find in the top row of A1:F11, then return the value that's in the 11th row of that column."
posted by foursentences at 5:13 AM on June 23, 2011

I think this is what you want:

The function OFFSET(B6,0,COUNTIF(19:19 etc.) has 2 important parts.

The B6 is the first cell that has dates in it, and the 19:19 part is the row number where your running total is kept.

Google spreadsheet
posted by selton at 5:18 AM on June 23, 2011

foursentences, I've tried it and it isn't working: it just gives me a zero value (or a "1/1/1904" value as a date). selton, I tried yours as well, and couldn't seem to get to quite work either.

Part of the problem might be that the dates are on the top in the budget, and that there's a lot of data that's not part of the equation between the dates and the range I'm looking at (my normal everyday monthly bills, as opposed to the money that goes towards debt repayment).

So it's A1-BN1 containing the dates, then A2 through BN30 that contains other material, then A31-BN36 that contains the range of data which I'd like to check to see if totals zero.

I'm sorry -- I'm even considered the "Excel guru" in my office but can't quite figure out what's going wrong here.
posted by WCityMike at 7:46 AM on June 23, 2011

WCityMike would it be possible for you to upload to Google Docs (or similar elsewhere) a copy of your spreadsheet with the private financial info turned into less private example data ?
posted by selton at 8:24 AM on June 23, 2011

Sorry I couldn't do it until the evening, but it wasn't a good idea to do it while at work. I've hopefully anonymized it sufficiently; here it is on my Dropbox.
posted by WCityMike at 7:49 PM on June 23, 2011

WCityMike,

I think I've done it (google spreadsheet) but I had to add another row at the bottom.

If I've summed the wrong things in that row then amend that sum until you get zero where you would want to get zero. But I can't figure out a way of doing this without having some kind of running total to check against.

I put the formula in cell A1 and it returns the date of the first zero or above value in that new row.

Oh I only trimmed off some of the columns because google can't handle more than a certain number & kept rejecting the upload.
posted by selton at 4:11 AM on June 24, 2011

Thanks, selton! I appreciate it!
posted by WCityMike at 1:24 AM on June 25, 2011

Just a minor point, but positive numbers (if they existed:) would throw that off. An alternative way is as below, match returns the location of the first 0 in row 34, the index then returns the corresponding value in row 1:

=INDEX(1:1,MATCH(0,34:34,0))
posted by Boobus Tuber at 10:35 AM on June 26, 2011