Reference formula identified as Zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am referencing cells in Sheet1 to pick up values from cells in Sheet2. Cells in Sheet2 are filled every few minutes with numerical data from a web query. Sheet1 rows are filled downwards row by row, as new data is received in Sheet2.

In Sheet1, I need the latest moving value to appear at the bottom of each column, Taking the sample array in A1:A4, I have tried

OFFSET(A1, MATCH(MAX(A1:A4)+1,A1:A4,1)-1,0)

at the bottom of each column.

However, the referencing formulas sitting in unfilled intervening cells in the lower part of columns are recognised as zeros by this formula. So the answer becomes zero always. Is there a way I could get the formula to ignore these formulas sitting in the cells and pick up the most recently received row value ? My values are between 1 and 1000.

Thanks for any help.
 
...
...
In Sheet1, I need the latest moving value to appear at the bottom of each
column, Taking the sample array in A1:A4, I have tried

OFFSET(A1,MATCH(MAX(A1:A4)+1,A1:A4,1)-1,0)

at the bottom of each column.
Click to expand...

For MATCH(x,y,1) to work, range y must be sorted in ascending order, so A4 would
always be the maximum value, so your formula above is always going to be
equivalent to =A4 if A1:A4 is in ascending order. If it's not, then the MATCH
call's return value isn't reliable.
However, the referencing formulas sitting in unfilled intervening cells in the
lower part of columns are recognised as zeros by this formula. So the answer
becomes zero always. Is there a way I could get the formula to ignore these
formulas sitting in the cells and pick up the most recently received row
value ? My values are between 1 and 1000.
Click to expand...

I'm going to guess you want either

=INDEX(A1:A4,MATCH(MAX(A1:A4),A1:A4,0))

or

=LOOKUP(9.99999999999999E+307,A1:A4)
 
Harlan Grove said:
...
...

For MATCH(x,y,1) to work, range y must be sorted in ascending order, so A4 would
always be the maximum value, so your formula above is always going to be
equivalent to =A4 if A1:A4 is in ascending order. If it's not, then the MATCH
call's return value isn't reliable.


I'm going to guess you want either

=INDEX(A1:A4,MATCH(MAX(A1:A4),A1:A4,0))

or

=LOOKUP(9.99999999999999E+307,A1:A4)
Click to expand...

Thanks Harlan,

INDEX will not work for me and sorting numbers will not be possible. So I tried your LOOKUP suggestion. This works very similar to the OFFSET formula I am using.
I have not spotted a difference in the result between them, yet.

However, the problem persists. How I can build in to one of these two formulae, to ignore the zeros in the intervening cells that show up as a 'zero' when it references to Sheet2 blank cells.

Reply much appreciated.
 
EricL said:
However, the problem persists. How I can build in to one of these
two formulae, to ignore the zeros in the intervening cells that
show up as a 'zero' when it references to Sheet2 blank cells.
Click to expand...

Try

=1/LOOKUP(9.99999999999999E+307,1/A1:A4)

Two divisions intentional.
 
Back
Top