Inserting offset cells values into formulaClosed

Question

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

venkat1926 · Answer

how does this 14.95 and 15.07 obtained? perhaps you have to give a longer data. where does it start

trying hard · Answer

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.

Inserting offset cells values into formula

2 responses

Similar discussions

Subscribe To Our Newsletter!