Formula for the last column value

[Ahmed Khan]

Am using MS-2007. Pls provide me the formula to get the last column value.

c5 123
c6 126
c7 128
c8 124

c15

I want "C15" to automatically catch the value of the last column value in
the column (c8). Pls help me

 

[JBeaucaire]

As long as the data in column C is numeric, this formula will work:

=INDEX($C$1:$C$14,MATCH(9.99999999999999E+307,$C$1:$C$14))

Adjust the range to suit. If the values in the range will be text, you will
need this instead:

=INDEX($C$1:$C$14,MATCH(REPT("z",255),$C$1:$C$14))

 

[Rick Rothstein]

Try this formula...

=LOOKUP(2,1/(C1:C14<>""),C1:C14)

 

[Abe]

I tried your formula and of course it works. Just curious. What is the
significance of "2" in the formula?

Also the "1/". . .)?

 

[Rick Rothstein]

Here is the formula again...

=LOOKUP(2,1/(C1:C14<>""),C1:C14)

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.

 

[Abe]

Thanks for that detailed explanation, Rick.

I experimented with the first argument and noticed that "1" would work as
well. So would any positive number for that matter.

I also noticed that your formula would work whether the last cell is
populated by a value or text.

I remember seeing some similar formulae that used "999^99" or maybe JB's
"9.99999999999999E+307" supposedly because they represent some outrageously
large number that will never be exceeded by whatever . . . Simpler usually
works better for me.

Thanks again.

Abe

Here is the formula again...

=LOOKUP(2,1/(C1:C14<>""),C1:C14)

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.