FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

[Morton Detwyler]

I have a table of data that is used to input a product metric every month.
Product names are in column [A], and number headings are in Row [1] (i.e.
1,2,3,4.....). We do not use month names. Metrics are then entered into each
row horizontally, for the list of products. Because of certain minimum
rules, not all products receive a metric every month, so each product has a
varied amount of metrics. To the right of these metrics, is a column that
performs a calculation on only the "last" entered metric for each product.
I'd like my formula in this far right column to be able to look to the left
and find the "last" entered metric. I'm stumped as to how to do this. Any
help would be greatly appreciated. Thank you....

 

[Pete_UK]

Assuming your metrics can be entered in columns C to X, put this in a
cell to the right on row 2 to give you the last number entered in that
range:

=LOOKUP(10^10,C2:X2)

Adjust the range to suit, and then copy down.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Whatever column you put your formula in, use the column before it for the
end of the row range. For example, if put your formula in AA, then use this
formula to get the contents of the last filled in cell before Column AA...

=LOOKUP(2,1/(A2:Z2<>""),A2:Z2)

You can copy this formula down.

Rick

 

[Morton Detwyler]

Thank you so much....it works perfectly! Can I ask why the expression
"10^10" is used in the formula?

Assuming your metrics can be entered in columns C to X, put this in a
cell to the right on row 2 to give you the last number entered in that
range:

=LOOKUP(10^10,C2:X2)

Adjust the range to suit, and then copy down.

Hope this helps.

Pete

I have a table of data that is used to input a product metric every month..
Product names are in column [A], and number headings are in Row [1] (i.e.
1,2,3,4.....). We do not use month names. Metrics are then entered into each
row horizontally, for the list of products. Because of certain minimum
rules, not all products receive a metric every month, so each product has a
varied amount of metrics. To the right of these metrics, is a column that
performs a calculation on only the "last" entered metric for each product..
I'd like my formula in this far right column to be able to look to the left
and find the "last" entered metric. I'm stumped as to how to do this. Any
help would be greatly appreciated. Thank you....

 

[Morton Detwyler]

Thank you so much....it works perfectly! Can I ask why "/(A2:Z2<>"")" is
used in the formula?

Whatever column you put your formula in, use the column before it for the
end of the row range. For example, if put your formula in AA, then use this
formula to get the contents of the last filled in cell before Column AA...

=LOOKUP(2,1/(A2:Z2<>""),A2:Z2)

You can copy this formula down.

Rick

I have a table of data that is used to input a product metric every month.
Product names are in column [A], and number headings are in Row [1] (i.e.
1,2,3,4.....). We do not use month names. Metrics are then entered into
each
row horizontally, for the list of products. Because of certain minimum
rules, not all products receive a metric every month, so each product has
a
varied amount of metrics. To the right of these metrics, is a column that
performs a calculation on only the "last" entered metric for each product.
I'd like my formula in this far right column to be able to look to the
left
and find the "last" entered metric. I'm stumped as to how to do this. Any
help would be greatly appreciated. Thank you....

 

[Bob Phillips]

It gets the last entry and is used to cater for text as well as numeric.

If the data is just numeric, you can use the simpler

=LOOKUP(99^99,A2:Z2)

--
__________________________________
HTH

Bob

Thank you so much....it works perfectly! Can I ask why "/(A2:Z2<>"")" is
used in the formula?

Whatever column you put your formula in, use the column before it for the
end of the row range. For example, if put your formula in AA, then use
this
formula to get the contents of the last filled in cell before Column
AA...

=LOOKUP(2,1/(A2:Z2<>""),A2:Z2)

You can copy this formula down.

Rick

I have a table of data that is used to input a product metric every
month.
Product names are in column [A], and number headings are in Row [1]
(i.e.
1,2,3,4.....). We do not use month names. Metrics are then entered into
each
row horizontally, for the list of products. Because of certain minimum
rules, not all products receive a metric every month, so each product
has
a
varied amount of metrics. To the right of these metrics, is a column
that
performs a calculation on only the "last" entered metric for each
product.
I'd like my formula in this far right column to be able to look to the
left
and find the "last" entered metric. I'm stumped as to how to do this.
Any
help would be greatly appreciated. Thank you....

 

[Pete_UK]

10^10 is just a big number, and is easy to type - it should be larger
than the numbers expected, so it could be set to, say, 1000 if your
numbers will all be less than this.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Actually, you have to look at the entire 2nd argument, not just the divisor
from it. The second argument is 1/(A2:Z2<>""). The way the LOOKUP function
works, is it evaluates the second argument for each cell in the specified
range and produces an array of values to search trying to find the first
argument in that list. Since the denominator is a logical expression, it
will return a TRUE or FALSE result. Excel will convert TRUE to 1 and FALSE
to 0 when they are used in a calculation; hence the division into 1. These
divisions will produce an array of one's and #DIV/0! errors... the LOOKUP
function ignores errors when it does its search. So, what it will attempt to
do is find the first argument, the value 2, inside the array of returned
values, the 1's and #DIV/0! errors. As an aside, the LOOKUP function expects
the array of values in its second argument to be sorted, but since the only
active values are 1's, the sorting issue is a moot point. Okay, so what
happens when it LOOKUP can't find the value it is looking for in the list?
What it does is selects the last non-error element in the array and uses its
position as the index into the array of values in the third argument. Since
the array of value in the third argument is the contents of the same cells
as was used in the second argument. what gets returned from the LOOKUP
function is the contents of the last cell with something in it... that is
when the value of 1/(A2:Z2<>"") returns a 1, when something it in the cell.
Maybe the preceding can be technically "tighten up" a little bit more; but,
basically, that is what is happening in the formula I posted.

Rick

Thank you so much....it works perfectly! Can I ask why "/(A2:Z2<>"")" is
used in the formula?

Whatever column you put your formula in, use the column before it for the
end of the row range. For example, if put your formula in AA, then use
this
formula to get the contents of the last filled in cell before Column
AA...

=LOOKUP(2,1/(A2:Z2<>""),A2:Z2)

You can copy this formula down.

Rick

I have a table of data that is used to input a product metric every
month.
Product names are in column [A], and number headings are in Row [1]
(i.e.
1,2,3,4.....). We do not use month names. Metrics are then entered into
each
row horizontally, for the list of products. Because of certain minimum
rules, not all products receive a metric every month, so each product
has
a
varied amount of metrics. To the right of these metrics, is a column
that
performs a calculation on only the "last" entered metric for each
product.
I'd like my formula in this far right column to be able to look to the
left
and find the "last" entered metric. I'm stumped as to how to do this.
Any
help would be greatly appreciated. Thank you....