Excel Question - How to find a price in a price series

Position your cursor in the cell at the top of the column in which you want to find the data point. Go to the Data menu at the top of Excel and select Filter, then AutoFilter. A dropdown button will appear in your spreadsheet in the cell you selected (and possibly other cells in the same row as well). Press the button and search for the value you wish to locate in the drop down box. Select it. All the rows containing that value will appear, all other rows will be hidden.

You can have Excel automatically count the rows by placing this formula above the cell with the button in it: =Subtotal(3,Range)

The word Range in the formula means to supply the range of the cells you wish to total. If your sheet has data in rows 5 through 500 in column B, you would type in B5:B500 in place of the word 'Range'.
 
Thanks for the suggestion Bdixon.

While your technique works, it requires oneto process each row one at a time to find the desired result.

I'd rather have Excel do all that work for me automatically, one row at a time.

Thanks again for your time and assistance.

-- M
 
Thanks for the suggestion.

I was able to figure out how to do it though a combination of the MATCH, OFFSET, and MIN functions by searching for all relevant prices all in one row. Resulted in a huge spreadsheet, but good enough for now.

Thanks everyone.

-- M
 
Hey --

For all you Excel heads - I am too tired to think out a nifty array formula or functions that could do this - VBA is my bag -- here's the solution (I believe) in Excel XP - probably work down to Excel 97 as it isn't too fancy.

The N value goes in J1 and you press "Run". The code will put in the row of the first occurence of either + or - N points from the price in Column C. You can load your Time and Price data in Columns A and B, respectively.

Peace, Out zzzzzzzzzz.
 

Attachments

MMM:

I decided that I was going to tackle this without VBA - just to prove that I still RULE when it comes to Excel. this uses array formulas that need to be copied down as you add data ...
I also have this sheet on Manual Calc -- F9 to get it to work if you add more data and then copy the formula down.

Check it out - I left the VBA solutions in C so that I could verify the same results ... btw 999 means no number found that met the conditions.


Hope this helps you make your trades better ....


Peace.

:cool:
 

Attachments

You must log in or register to reply here.
Back
Top