Hello
I am hoping that one of the excel gurus can help!
I have a list of daily prices for a number of products. I am trying to find a way to work out, for the last value in each column, when this value is the highest since and lowest since.
So to explain further:
Column B has dates
Column C to I has the values, one column per product
Column J is blank, and columns K-T has some calcs, graphs and summary data.
The data currently runs to row ~1350, but they are not complete series, there are some blanks (public holidays etc).
What I would like is if the data ends at, row 1357, to have in K1357 "highest since dd/yy/mm" and K1358 "lowest since dd/yy/mm"; referring to the data in col C, the same in col L for data in D, etc.
I have tried a number of combinations of vlookups, if/match etc and not managed to get a result. Any help would be much appreciated
thanks
-- Mike
Not really sure I understand - either one of the two results will surely be the prior date ?
Might be an idea to post a sample file to better illustrate requirements / scenarios etc...K1357: =IF(C1357<=MIN(C$2:C1356),"n/a",LOOKUP(2,1/(C$2:C1356>C1357),$B$2:$B1356)) copied to Q1357 K1358: =IF(C1357>=MAX(C$2:C1356),"n/a",LOOKUP(2,1/(C$2:C1356<C1357),$B$2:$B1356))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Is this on the lines you are thinking?
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Please see attached sample.
MIN or MAX won't work, because all I want is the MOST RECENT instance of a price higher or lower than today's - there may have been higher or lower values in the past, but these are not relevant for my purposes.
It is also right to note that only the Highest_Since or Lowest_Since should give a meaningful result, but for simplicity I wanted to find a formula that would work when I drag it down
Please see the attached example - this is a simplified version of the actual sheet, with the kind of result I am after (inserted manually)
I have also shown, highlighted in colour, what the result would have been for a given date in the past.
Thanks again for any help
-- Mike
The LOOKUP construct illustrated previously should suffice.
I won't pretend to follow your expected results in the sample but suffice to say that the general premise of the LOOKUP approach is that it will return the Date associated with the last test result that holds true.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks