Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
=VLOOKUP(MAX($A:$A),$A:$A,1)
This function is meant to provide the value displayed in the lower- most/last active cell in column A. It is provided as a solution to someone who has a list of figures in a spreadsheet, the last cell of which is the most up to date figure available and needs to be displayed elsewhere on the sheet, especially if the last active cell is constantly changing. However, I thought MAX( ) would display the largest figure in the column - not the figure in the largest row number occupied. But the function does actually work - it displays whatever is in the final row occupied of column A; even if the last row is the smallest figure in the column! Can anyone provide an explanation of why? Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
Steve,
It does not return the last value in column A, if that value is not the largest (latest) value in column A.. HTH, Bernie MS Excel MVP wrote in message ... =VLOOKUP(MAX($A:$A),$A:$A,1) This function is meant to provide the value displayed in the lower- most/last active cell in column A. It is provided as a solution to someone who has a list of figures in a spreadsheet, the last cell of which is the most up to date figure available and needs to be displayed elsewhere on the sheet, especially if the last active cell is constantly changing. However, I thought MAX( ) would display the largest figure in the column - not the figure in the largest row number occupied. But the function does actually work - it displays whatever is in the final row occupied of column A; even if the last row is the smallest figure in the column! Can anyone provide an explanation of why? Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
wrote...
=VLOOKUP(MAX($A:$A),$A:$A,1) If this worked as you're assuming it does, it'd return the same result as MAX($A:$A). If you want the largest value in col A, just use the MAX call. This function is meant to provide the value displayed in the lower- most/last active cell in column A. *It is provided as a solution to someone who has a list of figures in a spreadsheet, the last cell of which is the most up to date figure available and needs to be displayed elsewhere on the sheet, especially if the last active cell is constantly changing. If you want to display the bottommost number value in col A, try =LOOKUP(9.99999999999999E+307,A:A) However, I thought MAX( ) would display the largest figure in the column - not the figure in the largest row number occupied. *But the function does actually work - it displays whatever is in the final row occupied of column A; even if the last row is the smallest figure in the column! MAX does return the largest number value in its arguments. It's your VLOOKUP call above that's flawed. Since you omit the optional 4th argument, VLOOKUP *expects* that col A is sorted in ascending order. If col A isn't sorted, VLOOKUP will usually return *INCORRECT* results. Try =VLOOKUP(MAX($A:$A),$A:$A,1,0) but, again, it's unnecessary since it would return the same result as =MAX($A:$A). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
If that formula is returning the correct result it's just "dumb luck".
It sounds like you want the *last* numeric entry from column A. Try this: =LOOKUP(1E100,A:A) To show you that the other formula doesn't work properly try entering these numbers in A1:A5 - 62,68,10,97,3. =VLOOKUP(MAX($A1:$A5),$A1:$A5,1) Result = 97 which is not the last numeric value in the range. -- Biff Microsoft Excel MVP wrote in message ... =VLOOKUP(MAX($A:$A),$A:$A,1) This function is meant to provide the value displayed in the lower- most/last active cell in column A. It is provided as a solution to someone who has a list of figures in a spreadsheet, the last cell of which is the most up to date figure available and needs to be displayed elsewhere on the sheet, especially if the last active cell is constantly changing. However, I thought MAX( ) would display the largest figure in the column - not the figure in the largest row number occupied. But the function does actually work - it displays whatever is in the final row occupied of column A; even if the last row is the smallest figure in the column! Can anyone provide an explanation of why? Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
I'm sorry - It does return the last value unless you limit the range to a specific row range of the
column - I'm not sure why, but it is likely to be related to how VLOOKUP manages entire column ranges. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Steve, It does not return the last value in column A, if that value is not the largest (latest) value in column A.. HTH, Bernie MS Excel MVP wrote in message ... =VLOOKUP(MAX($A:$A),$A:$A,1) This function is meant to provide the value displayed in the lower- most/last active cell in column A. It is provided as a solution to someone who has a list of figures in a spreadsheet, the last cell of which is the most up to date figure available and needs to be displayed elsewhere on the sheet, especially if the last active cell is constantly changing. However, I thought MAX( ) would display the largest figure in the column - not the figure in the largest row number occupied. But the function does actually work - it displays whatever is in the final row occupied of column A; even if the last row is the smallest figure in the column! Can anyone provide an explanation of why? Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
Thank you, everyone, for your prompt replies. I think I understand
now why it only *appeared* to be working, but if you pick the right combination of numbers, it soon becomes apparent that it doesn't. I did wonder, since it appeared to work for me but the colleague I did it for reported that it did not work for her, so I suspected something was up. However, T.Valko's suggestion of =LOOKUP(1E100,A:A) does exactly what I need, so essentially it's problem solved in this case. Thank you for the information. Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this function supposed to work?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP wrote in message ... Thank you, everyone, for your prompt replies. I think I understand now why it only *appeared* to be working, but if you pick the right combination of numbers, it soon becomes apparent that it doesn't. I did wonder, since it appeared to work for me but the colleague I did it for reported that it did not work for her, so I suspected something was up. However, T.Valko's suggestion of =LOOKUP(1E100,A:A) does exactly what I need, so essentially it's problem solved in this case. Thank you for the information. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need a function that will work using multiple work books and sheet | Excel Worksheet Functions | |||
Chart filling in dates that aren't supposed to be there | Charts and Charting in Excel | |||
Excel If statement that is supposed to return a "" instead returns a 0 | Excel Worksheet Functions | |||
how do i get the mid function to work with a zero | Excel Worksheet Functions | |||
When I publish a web page that's supposed to be interactave it be. | Excel Discussion (Misc queries) |