Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #583 Jun 08, 2012 www.energyefficientbuild.com.
user Chematronix said:
Formula: how to obtain the closest match of a cell in its same column?
Howdy,
I want to find the closest match of a value in its same column. Specifically, I've a column of Times (say, appointments), and I want another of Time Before Next Appointment. For this I need to find the next appointment relative to the current row, and subtract the current time to obtain the difference. The tricky part is, the sheet is not sorted by Time, so I can't simply subtract the current value from next row's.
Seems VLOOKUP should help, but it also would require sorting by Time; also, since I'm searching for a value in its same column, it returns that very same value. Is there an easy way to duplicate the target range, exclude the current row from it, and sort it by Time?
Or perhaps there's another easy way to accomplish this simple calculation? Get the range as an array, sort the values and search for one that is bigger?
Here's my test sheet, feel free to add columns with new methods:
https://docs.google.com/
I'm going to check scripting next, as hopefully that will provide much more flexibility (not to mention readability) than clunky formulas. There must be a way to trigger a function to update a column every time a certain range is edited, right?
Any help is appreciated.
---------------------------------------------------------------------------------------
following is a solution to the problem
user Chematronix said:
Formula: how to obtain the closest match of a cell in its same column?
Howdy,
I want to find the closest match of a value in its same column. Specifically, I've a column of Times (say, appointments), and I want another of Time Before Next Appointment. For this I need to find the next appointment relative to the current row, and subtract the current time to obtain the difference. The tricky part is, the sheet is not sorted by Time, so I can't simply subtract the current value from next row's.
Seems VLOOKUP should help, but it also would require sorting by Time; also, since I'm searching for a value in its same column, it returns that very same value. Is there an easy way to duplicate the target range, exclude the current row from it, and sort it by Time?
Or perhaps there's another easy way to accomplish this simple calculation? Get the range as an array, sort the values and search for one that is bigger?
Here's my test sheet, feel free to add columns with new methods:
https://docs.google.com/
I'm going to check scripting next, as hopefully that will provide much more flexibility (not to mention readability) than clunky formulas. There must be a way to trigger a function to update a column every time a certain range is edited, right?
Any help is appreciated.
---------------------------------------------------------------------------------------
following is a solution to the problem
