How to pull the rightmost, non-zero cell value in a workbook?

[Jennifer]

Hi Everyone,

See subject above. In column A, I have a long list of properties. Columns
B through W are dates, and the data listed below shows various percentages.
Not all of the cells are populated, as I leave them blank until I receive the
data.

In column V, I am trying to pull the most recent data (i.e. the rightmost
cell related that is NOT blank). I have tried everything: SUMIF, RANK,
ISNUMBER. ISNUMBER worked, but I would have to update the formula each week,
which is a pain.

Is there a faster way to do this? This is kind of hard to explain in
writing, so please let me know if you'd like to see my spreadsheet.

 

[T. Valko]

To return the *last* (rightmost) numeric value in a range:

=LOOKUP(1E100,B2:W2)

 

[Jennifer]

Hi Biff,

That is awesome; it worked. But I'd like to understand the formula, too -
can you explain what the 1E+100 does?

 

[CLR]

(This, expanded out to column "W" for your case), will return the rightmost
TEXT or numbers in a row...........

=INDIRECT(LOOKUP(COUNTA(A2:J2),{1,2,3,4,5,6,7,8,9,10},{"A","B","C","D","E","F","G","H","I","J"})&ROW())

Vaya con Dios,
Chuck, CABGx3

 

[Mike H]

Jennifer

If Biff will excuse me answering,

1E+100 is a number in scientific notation larger than will be found in the
range so it causes the formula to find the largest number. Try the formula
=LOOKUP(10,B2:W2)

it will find the largest number up to 10 and ignore any larger ones.


Mike

 

[T. Valko]

The way LOOKUP works is...

If *every* value in the range is less than the lookup_value (1E100) then the
formula returns the *last* value in the range that is less than the
lookup_value.

To make sure that we do in fact get the last value we use a lookup_value
that is guaranteed to be greater than any value in the range. So, we use an
arbitrary HUGE number for the lookup_value.

1E100 is a HUGE number. It's 1 followed by 100 zeros. Chances are pretty
good that you don't have any numbers that big in your range so the formula
returns the desired result, the last number in the range.

Technically, all you really need for a lookup_value is a number greater than
any number in your range. Consider this, say you were working with bowling
scores. The highest possible bowling score is 300 so no number in your range
will be greater than 300. So, in that case a lookup_value of 301 is all that
is needed.

When people post these types of questions they ususally don't tell us how
big the numbers are that they're dealing with so when we reply, to be on the
safe side, we use a HUGE lookup_value.

 

[Sandy Mann]

=LOOKUP(10,B2:W2)

it will find the largest number up to 10 and ignore any larger ones.

Not quite. It will only do so if the numbers are in ascending order,
otherwise you may get some other when there is a higher value which is still
lower then 10 further in the series.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mark]

Is there a way to return the rightmost non-zero number? My spreadsheet
requires zeroes rather than blanks so this function doesn't work. Thanks!

 

[T. Valko]

As long as the range *doesn't contain* the logical value TRUE...

=LOOKUP(1E100,1/(A1:J1),A1:J1)