Writing a Formula

[Guest]

I'm trying to writing a simple Formula which will only read from a cell which
a last input was made.
Let's start from A1 thou to Z1, I have entered 237 in A1, 345 in B1, 453 in
C1 and so on, i'm up to cell J1 and have entered 123, I just want the last
input to show, how?
Hop you all can help

 

[Biff]

Hi!

Try this:

=LOOKUP(100^10,A1:Z1)

Biff

 

[Guest]

That's superb biff, just what i wanted, now can i twist your arm a little,
could you explain this in a litlle more detail, i understand the =lookup and
A1:Z1, what is the 100^10, i've never seen that, i tend to use =index stuff a
lot.

 

[T. Valko]

100^10 = 100,000,000,000,000,000,000

100 to the 10th power

As you can see, that's a huge number. The way that Lookup works is if the
lookup_value (100^10) is greater than any number in the lookup_array (A1:Z1)
the result of the formula will be the LAST value that is less than the
lookup_value. Since there is a very good chance that you won't have any
numbers approaching the value of 100^10 the formula returns the LAST number
in the lookup_array.

Basically, 100^10 is just an arbitrary number that is "guaranteed" to be
greater than any number in the lookup_array. In reality, all you need for
the lookup_value is a number that is 1 greater than any number in the
lookup_array. Suppose the range of numbers was:

10;15;20;19;30

=LOOKUP(31,A1:E1) would work and return 30.

Another way to do it would be:

=LOOKUP(MAX(A1:E1)+1,A1:E1) this would also work and return 30.

If you know for CERTAIN that the maximum number in your lookup_array will
NEVER be greater than a certain value you can use a more "realistic"
lookup_value.

Biff

 

[Guest]

Yes i can see that now, thanks all round, easy once you know lol

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Yes i can see that now, thanks all round, easy once you know lol