| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Thanks Ron
You just switched the light on! "Ron Coderre" wrote: Regarding =LOOKUP(10^99,Sheet2!B:B) LOOKUP has a nice little quirk.... When the search_value is larger than any other value in the lookup range, it simply returns the last item in the list that is the same type as the lookup value (text vs numeric). In our case, that number is the Total Average that you are looking for. To ensure that there could not possibly be a match, many people use the largest numeric value that Excel can handle (9.9999999999E-307). IMHO that's only necessary in some scientific applications (astronomy for example), but for my purposes, 10 to the 99th power is guarranteed to be larger than any number I'll be looking for, plus it requires less typing and is cleaner looking. To find the last text value in Col_A, you'd use this variation: =LOOKUP(REPT("z",255),A:A) Does that help? *********** Regards, Ron XL2002, WinXP "DaveM" wrote: Hi Ron This also works thank you. But I am not too sure how the formula works - I don't understand what it is telling Excel to do. What does '10^99' mean? I presume this means go to the last value in that column but I don't understand why it works and before I use the formula it would be nice to know if there are any limitations to its use. Thanks Dave "Ron Coderre" wrote: Try this: With: Your list in columns A:B on Sheet2 NO other items in A:B except for the list Then.... In a cell on Sheet1: =LOOKUP(10^99,Sheet2!B:B) That formula returns the last numeric item from Col_B on Sheet2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DaveM" wrote: Hi, I have a 2 columns of data that is downloaded from an extrernal source. The list varies in length. At the bottom of the entries in column B is a cell that calculates the average of all the entries above. The cell to the left is titled 'Total Average (xxx)' where xxx is the number of entries. I need to link to the Total Average figure from another worksheet. I have been using the Index, Match function elsewhere. But because xxx varies this is not possible. Can I somehow use the Left function with Index Match such that only Total Average is the Matched variable? And if so how do I write the formula for this? Or can somebody suggest an alternative method? Thanks Dave |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using AutoFilter to Sort Data Pulled in by INDEX / MATCH | Excel Worksheet Functions | |||
| Match Index | Excel Worksheet Functions | |||
| Match or Index Question | Excel Worksheet Functions | |||
| Match & Index | Excel Worksheet Functions | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Threaded Mode
