Formula for the last column value
 

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

Formula for the last column value
 

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Ahmed Khan" wrote:

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

Formula for the last column value
 

Try this formula...

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

--
Rick (MVP - Excel)


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

Formula for the last column value
 

Rick Rothstein;220394 Wrote:
Try this formula...

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

--
Rick (MVP - Excel)


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

'This link' (http://www.xldynamic.com/source/xld.LastValue.html)
provides all kinds of possibilities


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60650

Formula for the last column value
 

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" wrote:

Try this formula...

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

--
Rick (MVP - Excel)


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

Formula for the last column value
 

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.

--
Rick (MVP - Excel)


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

Try this formula...

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

--
Rick (MVP - Excel)


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

Formula for the last column value
 

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


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

Try this formula...

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

--
Rick (MVP - Excel)


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