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

 

[deuce]

sorry the latest 20 days not the past 20 days.Dont know if saying it like this would make adifference but instead of max and min i would require the average

thanks.

 

[Aladin Akyurek]

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?

sorry the latest 20 days not the past 20 days.Dont know if saying it like this would make adifference but instead of max and min i would require the average

thanks.

First things first: Do the suggested formulas give you the required Max and Min values?

By the way, these formulas pick out the 20 latest records from the data area.

 

[deuce]

hi, the data is in 1 minute increments so we have to analyze for the max of the latest 20 days with saturdays and sundays being holidays.

 

[Aladin Akyurek]

hi, the data is in 1 minute increments so we have to analyze for the max of the latest 20 days with saturdays and sundays being holidays.

Care to post a small sample of 10 rows from C to F but also including the column housing the dates and pretend that we want max/min/average of the last 5 days?

 

[T. Valko]

sorry the latest 20 days not the past 20 days.Dont know if saying it like this would make adifference but instead of max and min i would require the average

thanks.

It can make a big difference.

Is this what you want:

The MAX, MIN and AVERAGE of the 20 *LAST* (bottom-most) entries from columns C:F ?

And, there are no empty rows of data within the data range, right? (this is a very important question to have answered!!!)

 

[deuce]

It can make a big difference.

Is this what you want:

The MAX, MIN and AVERAGE of the 20 *LAST* (bottom-most) entries from columns C:F ?

And, there are no empty rows of data within the data range, right? (this is a very important question to have answered!!!)

Yes, this is exactly what I want (but you need to tell me how to modify the formula if the columns to analyze have changed). Sorry I have not been able to reply earlier as I was having technical difficulties.

thanks and regards,
deuce

 

[deuce]

Hi, After some searching I got the average function sorted out.

{=AVERAGE(LARGE(Sheet!A:A,ROW(INDIRECT("1:" & 20))))}

I seems to isolate the last 20 rows for min and max too.

I will test the formulas and come back if I need help on this one.

 

[Aladin Akyurek]

Hi, After some searching I got the average function sorted out.

{=AVERAGE(LARGE(Sheet!A:A,ROW(INDIRECT("1:" & 20))))}

I seems to isolate the last 20 rows for min and max too.

I will test the formulas and come back if I need help on this one.

This AVERAGE formula averages (a) 20 largest values in the reference, not (b) the last 20 values of the reference.

If (b) would be the intent (see your initial post in this thread), the following regular formula...

=AVERAGE(OFFSET(INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A)),0,0,-MIN(COUNT(Sheet1!A:A),20)))

If you don't need to check whether there are 20 data points...

=AVERAGE(OFFSET(INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A)),0,0,-20))

Adjust the sheet name and the reference to suit.

 

[deuce]

Hi, Aladin, Thanks for the response. Here is where I cannot adjust the range. The same problem exists.

This formula looks at the data reference cells and counts the amount of times C12 has occurred in the reference range of cells it gives the correct results according to me.

Basically what I want to do is check how many times C12 has occurred but using the last 7200 cells as a reference, more rows are added everyday so I have to keep on adjusting the ranges everyday which is time consuming. an example of the formula I am using.

=SUMPRODUCT(--($E$27:$E$15003>=C12),--($F$27:$F$15003<=C12))


thanks and regards,
deuce.

 

[Aladin Akyurek]

Hi, Aladin, Thanks for the response.

Looks like it's hard to pin down which problem we are addressing... We have tried:

1) Max/Min values calculated on the last 20 records of a multi-column reference (column C to F).

2) You posted above an AVERAGE formula which averages 20 largest values from a reference, while you seem to think that it does so for the last 20 values in the reference.

I guess what follows is a different problem, not a comment on (1) and (2).

Here is where I cannot adjust the range. The same problem exists.

This formula looks at the data reference cells and counts the amount of times C12 has occurred in the reference range of cells it gives the correct results according to me.

Basically what I want to do is check how many times C12 has occurred but using the last 7200 cells as a reference, more rows are added everyday so I have to keep on adjusting the ranges everyday which is time consuming. an example of the formula I am using.

=SUMPRODUCT(--($E$27:$E$15003>=C12),--($F$27:$F$15003<=C12))

...

This SumProduct formula yields a count of all records where a value in E27:E15003 is greater than or equal to C12 while a corresponding value in F27:F15003 is less than or equal to C12. Is this what you have in mind?