Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Assuming column B contains only the downloaded numbers,
and that there are no non-numeric entries in the list, then =COUNT(B:B) tell you how many numbers there are (including the average). So you can get the average from another sheet with the formula: =OFFSET(Sheet1!B1,COUNT(Sheet1!B:B)-1,0) (change Sheet1 to the name of your worksheet) 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Dave,
Try ... =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1) HTH "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Hi Toppers
Thanks for your response. I changed E1:E100 to the named range I inserted for the whole array and changed B1:B100 to the named range containing my look up value 'Total Average', then changed the look up value from 1 to 2 and that's done the trick nicely. Many thanks Does the * just signify that anything could be in front or after my Match value of Total Average? "Toppers" wrote: Dave, Try ... =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1) HTH "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Hi Andrew
Thanks for this. It did not quite work how I wanted because the data is a filtered list and the average comes from the unfiltered list. But there are two other suggetsions here that do the trick. Thanks Dave "Andrew Taylor" wrote: Assuming column B contains only the downloaded numbers, and that there are no non-numeric entries in the list, then =COUNT(B:B) tell you how many numbers there are (including the average). So you can get the average from another sheet with the formula: =OFFSET(Sheet1!B1,COUNT(Sheet1!B:B)-1,0) (change Sheet1 to the name of your worksheet) 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Yes. The "*" is a 'wildcard ' but isn't required if your text starts with
"Total Average" but I inserted it to give a more generic solution. And thanks for the feedback. "DaveM" wrote: Hi Toppers Thanks for your response. I changed E1:E100 to the named range I inserted for the whole array and changed B1:B100 to the named range containing my look up value 'Total Average', then changed the look up value from 1 to 2 and that's done the trick nicely. Many thanks Does the * just signify that anything could be in front or after my Match value of Total Average? "Toppers" wrote: Dave, Try ... =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1) HTH "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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
Thanks for the * explanation
My pleasure Dave "Toppers" wrote: Yes. The "*" is a 'wildcard ' but isn't required if your text starts with "Total Average" but I inserted it to give a more generic solution. And thanks for the feedback. "DaveM" wrote: Hi Toppers Thanks for your response. I changed E1:E100 to the named range I inserted for the whole array and changed B1:B100 to the named range containing my look up value 'Total Average', then changed the look up value from 1 to 2 and that's done the trick nicely. Many thanks Does the * just signify that anything could be in front or after my Match value of Total Average? "Toppers" wrote: Dave, Try ... =INDEX(E1:E100,MATCH("*Total Average*",B1:B100,),1) HTH "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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match
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 |
#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 |
Reply |
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 |