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 i tried the first and the last one and i get a #value! error.what next?

 

[Aladin Akyurek]

yes i tried the first and the last one and i get a #value! error.what next?

=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))

requires that you have defined Lrow as stipulated earlier.

Also, you should not have any error value in the ranges the formula looks at,

 

[deuce]

in defining the name in the name manager i selected the scope as sheet1 is this correct or the scope should be the entire workbook?

 

[Aladin Akyurek]

in defining the name in the name manager i selected the scope as sheet1 is this correct or the scope should be the entire workbook?

If the data is on Sheet1, the definition of Lrow will refer to that sheet (see post #24). So defined, Lrow is accessible from everywhere in the current workbook.

 

[deuce]

here is what i tried now, sheet1 contains the raw external data which is updated from another workbook.

C12 is on sheet 2 and on which I need to apply the sum product formula.

When i did some modification and typed in the value of c12 from sheet2 into J12 sheet1 then the cell that contains the formula returns the value of FALSE, note that i changed the term from C12 to refer to J12.

i don't know what i wrong.

 

[Aladin Akyurek]

here is what i tried now, sheet1 contains the raw external data which is updated from another workbook.

C12 is on sheet 2 and on which I need to apply the sum product formula.

When i did some modification and typed in the value of c12 from sheet2 into J12 sheet1 then the cell that contains the formula returns the value of FALSE, note that i changed the term from C12 to refer to J12.

i don't know what i wrong.

It is harder to help when the lay-out keeps changing. Try the set up I discussed first before adapting it to your wb.

 

[deuce]

hi, i was not able to respond during the week.

Basically the formulas being provided for the sumproduct function don't work, I just get a 0 result and using the other sum product formulas using named ranges don't work either as I get a false result or some other error.

I will explain it again. The formula would be kept on the same sheet with the rows containing the data.

1) Using the very last row from the bottom, count 7200 rows upwards from the latest row and within this range of 7200 rows apply the formula noted below. However please note that rows are added going downwards every minute of the day and because of that I have to keep on updating the ranges too frequently I find myself spending all my time adjusting cell ranges only.

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


hope a fix can be created for this problem of mine.

thanks and regards,
deuce.

 

[Aladin Akyurek]

hi, i was not able to respond during the week.

Basically the formulas being provided for the sumproduct function don't work, I just get a 0 result and using the other sum product formulas using named ranges don't work either as I get a false result or some other error.

I will explain it again. The formula would be kept on the same sheet with the rows containing the data.

1) Using the very last row from the bottom, count 7200 rows upwards from the latest row and within this range of 7200 rows apply the formula noted below. However please note that rows are added going downwards every minute of the day and because of that I have to keep on updating the ranges too frequently I find myself spending all my time adjusting cell ranges only.

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


hope a fix can be created for this problem of mine.

thanks and regards,
deuce.

What do you have in $D$47025:$D$54225, in $E$47025:$E$54225, and in J7?

 

[deuce]

numbers

 

[Aladin Akyurek]

numbers

D consists of numbers, E consists of numbers, and J7 houses a number, right?