T E Schmitz wrote: > > TerryJ wrote: >> >> >> T E Schmitz wrote: >> >>>I have a spreadsheet with 3 columns (DATE, HIGH, LOW). >>> >>>For each row I want to search the LOW column upwards in order to find >>>the first value for which >>>x[n-i] <= x[n] - 5 >>> >>>I am looking for >>>a) the difference DATE[n] - DATE[n-i] >>>b) the difference between x[n] and the highest HIGH value in rows n to n-i >>> >>> >>>Date High Low >>>28/02/07 6286 6166 >>>... >> >> >> I don't altogether follow but, for a similar situation, I use database >> functions. My date column is in ascending order so I use DMAX to find >> the >> last price at which a stock traded. For example: > > Well spotted, my date are stock prices, too, imported in descending > order from yahoo. As an aside: it would probably be easier importing the > data into an SQL database as that could be done via script. > > Of course, I could re-sort the data to have them in ascending order, > which would probably solve my lookup problem. > >> =VLOOKUP(DMAX(StockDayTable;"DATE";critVol);StockDayTable;5;0) . critVol >> is >> the criterion range: >> ... > > Many thanks for your input. > > -- > > Regards, > > Tarlika Elisabeth Schmitz > > If my tables were in date descending order, I would use DMIN in the first formula. I'm too lazy to try to work out what your formulas might do but the sort order may not matter. I use ascending order because my method was based originally on manual records and because I would have to insert a new row above the tables to insert new data. I use script to update my files but I don't want to put any more strain on the software than necessary. -- View this message in context: http://www.nabble.com/oocalc---find-value-bottom-%3Eup-tf3372550.html#a9409855 Sent from the openoffice - users mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
