Find the last number entered

  • Thread starter Thread starter old coach
  • Start date Start date
O

old coach

data
A
1-41
2-19
3-12
4-80
5-36
numbers continue to be added in the column above.
If I add another number, I want it to be placed in another column i.e
k50.
when i entered 12 in column A3 it also placed it in k50.
when i entered 80 in A4 it replaced the 12 in k50 with 80, etc.
how can i do this
 
Rick, could you explain the logic behind this formula? or provide some
link where I could read about it...
TIA
 
Here is the formula again...

=LOOKUP(2,1/(A1:A65535<>""),A:A)

The LOOKUP function will attempt to find the first argument (2), in the
array of values found in the second argument. Let's look at the denominator
of that second argument... it is a logical expression... each cell in the
specified range will be compared to the empty string ("").... if it is
**not** equal to it, TRUE is generated and if it is equal to it, FALSE is
generated. Whenever TRUE or FALSE is used in a numerical calculation, Excel
converts them to 1 and 0, respectively, before performing the math operation
involving them. The math operation, in this case, is to divide the result
from the logical expression into 1. Since the only two possible values are 1
and 0, an array of 1/1 and 1/0 is generated. The 1/1 will evaluate to 1;
however, the 1/0 division will produce a #DIV/0! error because you cannot
divide a number by 0 and get a valid result. So the LOOKUP will attempt to
find the 2 from the first argument in the array of 1s and #DIV/0! errors
from the second argument. If the LOOKUP formula cannot find the first
argument in the array of values from the second argument,it uses the largest
value in the array that is less than or equal to first argument and, if
there are ties for this largest value, it uses the last of them for its
match. The largest such value will, in the case of our array, will be a 1
and the last of them will always be generated by the last piece of data
since it will be the last cell not equal to the empty string.

Rick
 
Back
Top