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]

This formula works to give the average of the latest 20 records?. it is the one i am using now.

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

The min and max formula works also.

Now the sum product function is the one to complete for the time being the formula gives the correct result but i want it to use/examine the latest 7200 rows.

thanks and regards

deuce.

 

[Aladin Akyurek]

This formula works to give the average of the latest 20 records?. it is the one i am using now.

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

The min and max formula works also.

Now the sum product function is the one to complete for the time being the formula gives the correct result but i want it to use/examine the latest 7200 rows.

thanks and regards

deuce.

Note that C10 and C11 implements calculations in order to specify the exact reference for the SumProduct formula...

In C10 enter:

=MATCH(9.999999999999999E+307,$E:$E)

In C11 enter:

=MIN(ROW($E$27)+7200,$C$10)

Now invoke...

=SUMPRODUCT(
    --($E$27:INDEX($E:$E,$C$11)>=C12),
    --($F$27:INDEX($F:$F,$C$11)<=C12))

 

[deuce]

can it be done in such a way that the formula gives the results in the cell in which it is entered without the use of helper columns? I am running out of visible space. Additionally i forgot to mention that the reference range of cells are on sheet 1 not on the same sheet.

sorry for the error.

thanks and regards,
deuce.

 

[Aladin Akyurek]

can it be done in such a way that the formula gives the results in the cell in which it is entered without the use of helper columns? I am running out of visible space. Additionally i forgot to mention that the reference range of cells are on sheet 1 not on the same sheet.

sorry for the error.

thanks and regards,
deuce.

Just two helper cells makes the calculation more efficient in case you need to copy the formula down...

Define BigNum by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=9.99999999999999E+307

It's:

Either...

C10:

=MATCH(BigNum,Sheet1!$E:$E)

C11:

=MIN(ROW(Sheet1!$E$27)+7200,$C$10)

Then:

=SUMPRODUCT(
--(Sheet1!$E$27:INDEX(Sheet1!$E:$E,$C$11)>=C12),
--(Sheet1!$F$27:INDEX(Sheet1!$F:$F,$C$11)<=C12))

Or...

=SUMPRODUCT(
--(Sheet1!$E$27:INDEX(Sheet1!$E:$E,MIN(ROW(Sheet1!$E$27)+7200,MATCH(BigNum,Sheet1!$E:$E))))>=C12),
--(Sheet1!$F$27:INDEX(Sheet1!$F:$F,MIN(ROW(Sheet1!$E$27)+7200,MATCH(BigNum,Sheet1!$E:$E))))>=C12))

Or, even better...

Define also Lrow as referring to:

=MATCH(BigNum,Sheet1!$E:$E)

and invoke:

=SUMPRODUCT(
--(Sheet1!$E$27:INDEX(Sheet1!$E:$E,MIN(ROW(Sheet1!$E$27)+7200,Lrow)))>=C12),
--(Sheet1!$F$27:INDEX(Sheet1!$F:$F,MIN(ROW(Sheet1!$F$27)+7200,Lrow)))>=C12))

 

[deuce]

hi thanks for responding, the formulas look intimidating but i will test them out.

 

[Aladin Akyurek]

hi thanks for responding, the formulas look intimidating but i will test them out.

If you like to, we can work with named ranges. Such would make the formulas shorter and more readable.

Edit: In the last 2 SumProduct formulas, the second term must carry <=, not >=.

 

[T. Valko]

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

OK, just to make sure we understand.

Let's assume this is your data:

Book1
	C	D	E	F
2	78	35	29	48
3	41	29	65	25
4	83	50	29	44
5	88	100	34	90
6	10	90	71	69
7	23	80	1	18
8	13	20	71	22
9	51	5	42	36
10	58	37	24	22
11	56	99	46	14
12	7	76	25	69
13	2	11	38	15
14	21	44	76	96
15	63	40	59	34
16	10	17	37	84
17	29	62	67	21
18	80	96	98	39
19	87	7	80	55
20	45	79	45	42
21	48	50	67	99
22	15	11	95	37
23	26	32	8	25
24	55	6	40	89
25	9	47	49	70
26	8	100	4	51
Sheet1

I have highlighted the last 20 rows.

Is the highlighted area the range of cells you want to evaluate?

From this area, the last 20 rows, you want the MAX, MIN and AVERAGE?

 

[deuce]

hi valko, we have progressed from min max average to sum product formulas.

 

[deuce]

hi aladin, the sum product formula that you provided returns a count of 0 meaning it is not giving the correct results. what can we do to fix this problem?

 

[Aladin Akyurek]

hi aladin, the sum product formula that you provided returns a count of 0 meaning it is not giving the correct results. what can we do to fix this problem?

Did you try this...

=SUMPRODUCT(
--(Sheet1!$E$27:INDEX(Sheet1!$E:$E,MIN(ROW(Sheet1!$E$27)+7200,Lrow)))>=C12),
--(Sheet1!$F$27:INDEX(Sheet1!$F:$F,MIN(ROW(Sheet1!$F$27)+7200,Lrow)))<=C12))

or something else?