Last Number in Cell

R

Richard

I need the last number in the cell to be sent to Sheet2
B1. For example, Sheet1 column G is always counting down
in Column G. How do I send the last entry in Column G to
Sheet2, B1
 
D

Domenic

Hi Richard,

Put the following formula in B1 in Sheet 2:

=LOOKUP(9.99999999999999E+307,Sheet1!G:G)

Hope this helps!
 
J

JE McGimpsey

One way, assuming there are no blanks in column G:

B1: =INDEX(Sheet1!G:G,COUNT(Sheet1!G:G))
 
B

Biff

An alternative:

=Lookup(Max(A:A)+1,Sheet1!G:G)

Nobody has yet come up with a good enough reason to use
such an absurdly large lookup value. I know, it's the
largest value a cell can hold. So? If you know that the
largest *possible* value in your data will not be greater
than say, 100, why use that really_big_number?

Biff
 
B

Biff

Geeezzz!

After that, I can't believe I fubar the reference!!!

An alternative should be:

=Lookup(Max(Sheet1!A:A)+1,Sheet1!G:G)

Biff
 
B

Biff

I think I better find something else to do tonight!

Alright, one last time:

=Lookup(Max(Sheet1!G:G:)+1,Sheet1!G:G)

Biff
 
J

JE McGimpsey

Don't know what you consider "good enough" but replacing a function, a
reference lookup and a math operation by a constant (which takes up no
more storage space than a single digit number) seems good enough for me.
 
B

Biff

Hi JE!

That is a good reason based on my example but that still
doesn't address the issue of using 9.99999999999999E+307
versus, say, 1000. Which is what I was pointing out.

Biff
 
J

JE McGimpsey

To me, they're just numbers. Since XL only has a precision of 15 decimal
digits, I guess it's not that hard for me to remember that
9.99999999999999E307 is the largest number in XL - and it's never
situational.

A number like 9.99999999999999E307 takes no more storage room than 1000.

It's similar to my using 256 in the Sheetname in a cell formula:
=MID(Cells("filename",A1),FIND("]",CELLS("filename",A1))+1,256).

Sheet names can only be 31 characters, but, for me, 256 is an easy
number to remember, that's significantly larger than necessary, and will
likely be valid for many future versions.
 
F

Frank Kabel

Hi
in addition to JE
this also makes your spreadsheet more robust. You know now that 1000 is
larger than the largest number in your file. But what happens if this
changes due to new user data?. So this really makes sure you won't come
into this situation and as JE pointed out it does not waste any more
memory :)
 
B

Biff

I guess it all boils down to one's personal preferences
and comfort level.

I've brought up this point a couple of times in the past.
For new users, it's probably just one of those "I don't
know how it works but it does", kind of things.

Biff
 
A

Aladin Akyurek

Biff said:
I guess it all boils down to one's personal preferences
and comfort level.

I've brought up this point a couple of times in the past.
For new users, it's probably just one of those "I don't
know how it works but it does", kind of things.
Click to expand...

I assume how the construct works is well-known to you.

9.99999999999999E+307, highly improbable to occur in data, is the largest
positive value known to Excel.

=LOOKUP(9.99999999999999E+307,Reference)

or

=MATCH(9.99999999999999E+307,Reference)

effect the desired computation in m steps by binary search. If you
substitute

MAX(Reference)+1 for 9.99999999999999E+307, we get:

m+n steps, where n is exactly the size of Reference.

m vs m+n would bias most people's preferences.
 
Top