output max/min from another worksheet

[deuce]

I am requesting the creation of a formula that will output the max and min of the last 20 days from another worksheet, the problem is that the datasheet is automatically updated every 1 minute and filled with data going downwards. The data columns are C,D,E,F.

Is there a way to do this?

thanks and regards,
deuce

 

[Aladin Akyurek]

I am requesting the creation of a formula that will output the max and min of the last 20 days from another worksheet, the problem is that the datasheet is automatically updated every 1 minute and filled with data going downwards. The data columns are C,D,E,F.

Is there a way to do this?

thanks and regards,
deuce

Which sheet has the data?

The max of which column?

 

[deuce]

hi thanks for your response, Sheet1 has the data and the reference columns are C:F so it would be the max and min of of C:F

thanks
deuce.

 

[deuce]

I would specify the number of days and the formula would look from the latest row that contains the data.

 

[Aladin Akyurek]

I would specify the number of days and the formula would look from the latest row that contains the data.

For C, D, E, and F each separately or together?

 

[T. Valko]

I am requesting the creation of a formula that will output the max and min of the last 20 days from another worksheet, the problem is that the datasheet is automatically updated every 1 minute and filled with data going downwards. The data columns are C,D,E,F.

Is there a way to do this?

thanks and regards,
deuce

Will there ALWAYS be 20 rows of data? What if there isn't?

Is the data entered in a contiguous block (no empty rows within the data area)?

 

[deuce]

For C, D, E, and F each separately or together?

Together taken as 1 group/range of cells.

 

[deuce]

Will there ALWAYS be 20 rows of data? What if there isn't?

Is the data entered in a contiguous block (no empty rows within the data area)?

There are 28800 rows and 2000 rows are added every day, the latest data is added going downwards.


hope this helps.

deuce.

 

[Aladin Akyurek]

Together taken as 1 group/range of cells.

A1: 20 (Last N days parameter)

B1: (for the Max value)

=MAX(OFFSET(INDEX(Sheet1!C:C,MATCH(9.99999999999999E+307,Sheet1!C:C)),0,0,-MIN(COUNT(Sheet1!C:C),A1),COLUMNS(C:F)))

C1: (for the Min Value)

=MIN(OFFSET(INDEX(Sheet1!C:C,MATCH(9.99999999999999E+307,Sheet1!C:C)),0,0,-MIN(COUNT(Sheet1!C:C),A1),COLUMNS(C:F)))

 

[deuce]

There is another function I need to do but this deals with averaging, in Sheet2 column A I have the list going down in the same way but I need the average of the past 20 days instead of the min or max. what would i do for this?