ExcelBanter - FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA (https://www.excelbanter.com/excel-worksheet-functions/193796-finding-last-entered-number-row-data.html)

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

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....

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

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

On Jul 5, 7:19*pm, Morton Detwyler <Morton
wrote:
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....

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

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

"Pete_UK" wrote:

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

On Jul 5, 7:19 pm, Morton Detwyler <Morton
wrote:
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....

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

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

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

"Rick Rothstein (MVP - VB)" wrote:

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" <Morton wrote in
message ...
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....

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

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

On Jul 5, 9:44*pm, Morton Detwyler
wrote:
Thank you so much....it works perfectly! *Can I ask why the expression
"10^10" is used in the formula?

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA

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

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

"Rick Rothstein (MVP - VB)" wrote:

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" <Morton wrote in
message ...
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....