20 Day look back from last entry

[HajdukZD]

I have a sheet in my attached spreadsheet with columns of dates, day, day and time, price in column A, B, C, D, E.

How do I make a formula in a cell in the sheet "Lookback" that will look at the last entry in the columns, update automatically everytime I add data, and tell me the
high and low of the last 20 days/40 days/60 days?

Thank you in advance and please do not hesitate to ask if I did not explain this clearly enough.

Thanks again.

Ivan

[AliGW]

It would help if you provided some expected results and annotations in the workbook.

[Glenn Kennedy]

High and low of WHICH column?

Are there ALWAYS measurements EVERY day? It seems that some days are msssing. So is 20 days 20 days with data or (for example) 18 days with data and two days, within the date range, with no data?

Are the data in ascending date/time order? They seem to be... but there are FUTURE dates and out-of-sequence dates near the end.

THINK about what you are giving us and provide a complete explanation, please.

[HajdukZD]

Hi Glenn,

It would be the high and low of the price column.

There are not measurements everyday. It would be 20 days with data.

Data should be in ascending time order. This spreadsheet got passed back and forth a bit I didn't notice the out of date order in the end. it doesn't matter.

Missing days do not matter, there is no data from Friday 6:00 pm to Sunday 6:00 pm and other holidays.

We are looking for the lowhigh made in the last 20 days from the last entry.

Is this explanation complete?

Thanks

[Glenn Kennedy]

Yes. However, your raw data were a mess. Past dates have date/times and NO price. I assume that is a result of being "passed back and forth". I deleted those rows.

A helper is needed (column A) to return unique dates in descending order:


=IFERROR(INDEX('IDATA (2)'!A:A,AGGREGATE(14,6,ROW('IDATA (2)'!$A$2:$A$1000)/(('IDATA (2)'!$A$2:$A$1000<>"")*(COUNTIF(A$1:A1,'IDATA (2)'!$A$2:$A$1000)=0)),1)),"")

copied down. Adjust the range as needed, but don't go mad and make them whole columns... unless you have close to 1,000,000 rows.

Set the number of days in E1 and use MAXIFS and MINIFS:

=MAXIFS('IDATA (2)'!E2:E10000,'IDATA (2)'!A2:A10000,">="&INDEX(A:A,E1+1))

[HajdukZD]

Thanks Brett,

I can see what you mean by stating the problem better.

I am trying to piece together your logic and it's my mistake again for not putting the page referencing the IDATA.

I am attempting to match the forumla in the page I have as "LOOK"

The column N is the unique dates and the columns with the 60, 40, 20 day high and lows are in columns B - G

I can't get it to work. I am using excel 2019......is that why?

Can you take a look again please?

Thanks,

[Glenn Kennedy]

Brett??

Anyhow.

the results may look odd, but if you recall I had deleted a lot of incoherent data in IDATA(2).

see file. If this is not yet correct, amend your file to show AT LEAST one set of expected results that CAN be derived from the available data.

[HajdukZD]

OMG I apologize Glenn.........excel forum isn't letting me upload a file with the full data........there are only around 26,000 data points per year.........it's saying the file is too large
the problem is I have to reference data from other files I make one per year......