Dynamic range bottom to top formula.

**witprod** · 04-19-2020, 11:06 PM

I am working on a buy/hold/sell indicator for the stock market. I have a tricky formula to code. What I need is to determine when the last order to buy or sell was. The newest dates are on the bottom. If yesterday was a buy then use that info to calculate the formula, but if it is a hold, then I need the range recorded until there was a buy or sell order. If it was a buy order then I need the max(dynamic range). If it was a sell order then I need the min(dynamic range).

So it should look like this.

	A	B	C	D
1	Buy/Sell	Close	ARC	Change Action
2	Buy	13	1	Buy (Calculated by formula)
3	Hold	13.5	1.2
4	Hold	13.7	1
6	?	12	1.1	Formula

Formula(If last change action was a Buy then IF(B5>Max(A2:A4)-C5),A6="Hold",A6="Sell") If it was a Sell order then IF(B5<Min(A2:A4)+C5,A6="Hold",A6="Buy") Also if it is a hold then leave D6 blank. I'm assuming it will need to be blank to use the counta function to figure out how far up to go when calculating the dynamic range. If not then this formula could take place in column A if you can figure out how to do it. I hope this is specific enough. If not ask me questions!

A2:A4 needs to be dynamically calculated based on how far it has to go to detect that change action.

**Kaper** · 04-20-2020, 04:20 AM

Hi,

If in one column you would have date and time of the event then finding newest one will be an easy task.

My sincere suggestion is: Please read the yellow banner at the top of the page. Or skip to extended version of it below:

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, etc. - please show them all, or at least indicate in text). The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.