Previous Topic Next Topic

Inserting offset cells values into formula

Closed
trying hard - Mar 9, 2010 at 04:19 PM
trying hard Posts 1 Registration date Tuesday March 9, 2010 Status Member Last seen March 10, 2010 - Mar 10, 2010 at 07:25 AM
Hello,

I am trying to sort through technical stock data to test entry and exit conditions, I have set this up so that when the signal turns from SELL to BUY it chooses the appropriate price and when it shifts the other way it also chooses the appropriate price, in the interim it returns a value H. Since I have long lists of price data, I need a way to select the value in the column next to SELL and subtract the previous BUY value and place the net result in the column next to the SELL price. I have attempted VLOOKUP but got confused with the conditions required to select only the appropriate numerical value. I suspect that I need a Macro but am not advanced enough to write the code purely from scratch. Sample data below. Any suggestions appreciated. Thanks

SELL H
SELL H
SELL 14.95
BUY H
BUY H
BUY H
BUY H
BUY H
BUY H
BUY H
BUY H
BUY 15.07
SELL H
SELL H

2 replies

Answer 1 / 2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 9, 2010 at 09:06 PM
how does this 14.95 and 15.07 obtained? perhaps you have to give a longer data. where does it start
0
Answer 2 / 2
trying hard Posts 1 Registration date Tuesday March 9, 2010 Status Member Last seen March 10, 2010
Mar 10, 2010 at 07:25 AM
A formula determines whether conditions are met in the price data (Columns B - E) and in a number of calculated technical factors (Columns F-I). The BUY/SELL (Column J) is determined by =IF() condition based on individual values, averages, and other formulas based on the other data in the columns to the right of J. I tried to paste it in with formulas, but it is too long and looked even more confusing. Once the BUY or SELL is chosen, Column K asks: =IF(J194<>J195,B193,"H") or for example, when the lower cell below J195 <> J194 (BUY <> SELL) then it chooses a price from one of the Columns B-E, if they are equal (BUY=BUY) it chooses "H" (this is arbitrary, call it HOLD, to facilitate filtering and sorting). The problem is finding the last BUY in column J that has a selected price in column K rather than an "H" and pairing the SELL/BUT in that fashion. With daily data running back through 1998 and my desire to test multiple criteria, the BUY/SELL column and the Price/H column are the ones I wish to focus on. Hope this added clarity to the question. Please let me know if you need more. Thanks.
0