Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a Formula
Hi!
Try this: =LOOKUP(100^10,A1:Z1) Biff "sadman49" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a Formula
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. "Biff" wrote: Hi! Try this: =LOOKUP(100^10,A1:Z1) Biff "sadman49" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a Formula
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 "sadman49" wrote in message ... 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. "Biff" wrote: Hi! Try this: =LOOKUP(100^10,A1:Z1) Biff "sadman49" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a Formula
Yes i can see that now, thanks all round, easy once you know lol
"T. Valko" wrote: 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 "sadman49" wrote in message ... 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. "Biff" wrote: Hi! Try this: =LOOKUP(100^10,A1:Z1) Biff "sadman49" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a Formula
You're welcome. Thanks for the feedback!
Biff "sadman49" wrote in message ... Yes i can see that now, thanks all round, easy once you know lol "T. Valko" wrote: 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 "sadman49" wrote in message ... 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. "Biff" wrote: Hi! Try this: =LOOKUP(100^10,A1:Z1) Biff "sadman49" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
NEED HELP in Writing a FORMULA in colum D | Excel Worksheet Functions | |||
Help writing a lookup formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |