how to do calculations using undetermined cells

**bioform** · 05-18-2010, 09:33 AM

I have been using excel for a long time but never going deeper than using SUM commands. I tried reading some guides on excel functions but none of them seem to solve my problem so I hope you guys can help me out.

I am researching a trading strategy for stocks where I want to calculate the average returns of a simple strategy of buying and selling at arbitrary ratios. Basically I want a formula that automatically find the time at which the value crosses a certain low point and again when it crosses a high point and records down both. Something along the lines of =IF(OR(a1>2, a1<0),LOOKUP,...) etc. Obviously this one doesn't work, I only want the time of the start and the end recorded, not everything in between too. I could manually filter out the extra values except I have 550,000 numbers to deal with.

I hope I didn't make it sound too complicated. Any help is more than welcome. Thank you.

The title is a bit misleading, I hope that's ok.

**6StringJazzer** · 05-18-2010, 01:07 PM

What data do you have and what columns is it in?

When you say "crosses a certain low point" and a "high point" do mean specific predetermined numbers to use for those points, or do you mean the minimum and maximum values?

You probably need to do something to find the row you want, like a VLOOKUP for either the predetermined low point, or the MIN, depending on which you need. However, VLOOKUP has two "modes." One is where it expects data (in this case the trade price) to be sorted in ascending order, and it will find the largest value that does not exceed your target value (last argument is TRUE or omitted). The other mode is looking for an exact match, and it's not necessary for the data to be sorted (last argument is FALSE).

This would work if the price is in a column and the time of trade is in another column somewhere to the right. Otherwise you have to do some other gymnastics such as using MATCH.

I will trade you more advice for more details.

**bioform** · 05-19-2010, 12:08 AM

I have ratio data down multiple columns for different stocks and date on a column on the left, I can move it to somewhere else if that makes it easier.

The high and low points are made up and we want to test the effect of different values.

I'm not sure VLOOKUP works. Naturally I can't sort the price in any order and the buy and sell points are probably not going to have the exact number I look for (ie. I buy at 2, and the stock crosses from 1.9 to 2.1). It's better than nothing though, and I might be able to manipulate the decimals so they are exact matches.

Let's say the date is on column A and stock ratios on column B and C.

Is that enough details for more advise? Thank you.

**6StringJazzer** · 05-19-2010, 07:40 PM

Looking for value crossings when numbers are not sorted cannot be done by VLOOKUP. My approach to this would be to use a "helper" column. For each value, it would have formula to see if the low point or high was crossed getting to this value. If so it would repeat the value, otherwise it would return a blank.

This example shows the actual price but could be modified to show the low/high value being sought.

I'm still not sure what you want to do with this data once identified so I just attached a very simple example to illustrate the general approach.