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]

yes exactly.

 

[Aladin Akyurek]

yes exactly.

What is the exact value in J7?

What is the name of the sheet housing all these numbers?

What are the results of:

=ISNUMBER($D$47025)

and

=ISNUMBER($E$47025)

 

[deuce]

hi those are sales figures. i cannot post them here. sheet name is sheet1. both the isnumber functions come out as true.

 

[Aladin Akyurek]

hi those are sales figures. i cannot post them here. sheet name is sheet1. both the isnumber functions come out as true.

I didn't ask you post the data, just that criterion value of J7. If

=ISNUMBER(J7)

is TRUE. That is good enough.

We count every record where a D-value >= J7 and the corresponding value E-value <= J7 in the last 7200 records, right?

 

[deuce]

well the formula i posted does everything correctly only problem i am trying to save the time of having to adjust to match the range of the latest 7200 cells.

 

[Aladin Akyurek]

well the formula i posted does everything correctly only problem i am trying to save the time of having to adjust to match the range of the latest 7200 cells.

Well, I have already provided you a solution.

Here another one:

=SUMPRODUCT(
--(OFFSET(INDEX(D:D,MATCH(9.99E+307,D:D)),0,0,-7200)>=J7)
--(OFFSET(INDEX(E:E,MATCH(9.99E+307,D:D)),0,0,-7200)<=J7))

is effectively equivalent to

=SUMPRODUCT(--($D$47025:$D$54225>=J7),--($E$47025:$E$54225<=J7))

but one that will include any newly added records.

Note 1. The formula will produce an error if there aren't at least 7200 records available.
Note 2. The figure 9.99E+307 is the truncated version of 9.99999999999999E+307.
Note 3. The use of D:D twice is intentional.

 

[deuce]

what would the difference be in output results of this formula compared to mine?

 

[Aladin Akyurek]

what would the difference be in output results of this formula compared to mine?

It will include all newly added records...

 

[deuce]

hi well it appears to be working only that the number appeared 87 times but the formula outputted 7287 counts, how do we address this?

 

[Aladin Akyurek]

hi well it appears to be working only that the number appeared 87 times but the formula outputted 7287 counts, how do we address this?

Missing the list separator between the 2 terms...

=SUMPRODUCT(
--(OFFSET(INDEX(D:D,MATCH(9.99E+307,D:D)),0,0,-7200)>=J7),
--(OFFSET(INDEX(E:E,MATCH(9.99E+307,D:D)),0,0,-7200)<=J7))