Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
On Sheet1, I will have a column of item numbers, with each item having
perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Ed,
Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Bob - Sorry for the delay in the reply. I got caught by some work and then
it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Ed, that would work if you used say
=AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Thanks for staying with me on this, Bob. It's quite possible I have an idea
stuck sideways in my head and need it kicked free! What I'm looking at are several of these ranges stacked under each other. My first named range may cover rows 3-20, the second 24-30, etc. I wanted to see if there was a formula syntax that would work in any column, picking up the column number and intersecting that with the named range. If the user decides to add or remove rows from the range, I have code that will redefine the range limits. But given this possibility, I thought it better to use the range name in the formula than to have to run across 20 columns adjusting the cell limits of the function. So if the user deletes 4 rows from Range1 (rows 3-20), it now covers rows 3-16, and my formula in B21 that did say $B$3:$B$20 is now in B17. Ditto for the other 10 or 15 or whatever columns. Thus, I thought it best to use the name, rather than cell limits. Am I going about this the wrong way? Ed "Bob Phillips" wrote in message ... Ed, that would work if you used say =AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
ED,
The formula I gave you =AVERAGE($B$3:OFFSET(B20,-1,0)) But then again, even if you use =AVERAGE($B$3:$B$20) this should be okay even deleting rows. Have you tried it, and if so, what problems did you get? I have to say you're solution (appears) is more complex than seems necessary. -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Thanks for staying with me on this, Bob. It's quite possible I have an idea stuck sideways in my head and need it kicked free! What I'm looking at are several of these ranges stacked under each other. My first named range may cover rows 3-20, the second 24-30, etc. I wanted to see if there was a formula syntax that would work in any column, picking up the column number and intersecting that with the named range. If the user decides to add or remove rows from the range, I have code that will redefine the range limits. But given this possibility, I thought it better to use the range name in the formula than to have to run across 20 columns adjusting the cell limits of the function. So if the user deletes 4 rows from Range1 (rows 3-20), it now covers rows 3-16, and my formula in B21 that did say $B$3:$B$20 is now in B17. Ditto for the other 10 or 15 or whatever columns. Thus, I thought it best to use the name, rather than cell limits. Am I going about this the wrong way? Ed "Bob Phillips" wrote in message ... Ed, that would work if you used say =AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Bob: The formula
=AVERAGE($B$3:OFFSET(B20,-1,0)) does work, even with adding and deleting rows. Thank you. I have to say you're solution (appears) is more complex than seems necessary. And I'm sorry if I'm making this much more convoluted than necessary. But if I have this: What I'm looking at are several of these ranges stacked under each other. My first named range may cover rows 3-20, the second 24-30, etc. for four ranges of 20 columns each, then won't I need to go in and determine the top and bottom rows of each range, then loop 80 times setting formulas with the correct cell references? I thought if there was a simple syntax that would pick up the column number of the active cell and intersect that column with the name of the range I'm working with, there would be much less chance for error, as it would be the same syntax repeated for each cell that needs a formula. Too much like work? Ed "Bob Phillips" wrote in message ... Ed, that would work if you used say =AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Ed,
It still looks complex to me. You have to have named ranges that define the start and end rows, so where is the difference? Could you not add some identifier to say column A for each named range, and do a SUMIF on that value? -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob: The formula =AVERAGE($B$3:OFFSET(B20,-1,0)) does work, even with adding and deleting rows. Thank you. I have to say you're solution (appears) is more complex than seems necessary. And I'm sorry if I'm making this much more convoluted than necessary. But if I have this: What I'm looking at are several of these ranges stacked under each other. My first named range may cover rows 3-20, the second 24-30, etc. for four ranges of 20 columns each, then won't I need to go in and determine the top and bottom rows of each range, then loop 80 times setting formulas with the correct cell references? I thought if there was a simple syntax that would pick up the column number of the active cell and intersect that column with the name of the range I'm working with, there would be much less chance for error, as it would be the same syntax repeated for each cell that needs a formula. Too much like work? Ed "Bob Phillips" wrote in message ... Ed, that would work if you used say =AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
Bob, it looks like I'm asking questions that aren't as meaningful as I
thought, so the answers don't seem to make sense to me. I think I need to sit back and take a better look at this and get a better handle on what I need to do. Then maybe I can ask better questions, and get a better grasp on both the problem and the answers, rather than having both of us chase this thing around in circles. Thanks for all your input. You've given me some things to chew on over the holidays. Because I'm doing all this at work, I may not be back 'til after the New Year, so I hope you and yours have a very happy holiday season. Ed "Bob Phillips" wrote in message ... Ed, It still looks complex to me. You have to have named ranges that define the start and end rows, so where is the difference? Could you not add some identifier to say column A for each named range, and do a SUMIF on that value? -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob: The formula =AVERAGE($B$3:OFFSET(B20,-1,0)) does work, even with adding and deleting rows. Thank you. I have to say you're solution (appears) is more complex than seems necessary. And I'm sorry if I'm making this much more convoluted than necessary. But if I have this: What I'm looking at are several of these ranges stacked under each other. My first named range may cover rows 3-20, the second 24-30, etc. for four ranges of 20 columns each, then won't I need to go in and determine the top and bottom rows of each range, then loop 80 times setting formulas with the correct cell references? I thought if there was a simple syntax that would pick up the column number of the active cell and intersect that column with the name of the range I'm working with, there would be much less chance for error, as it would be the same syntax repeated for each cell that needs a formula. Too much like work? Ed "Bob Phillips" wrote in message ... Ed, that would work if you used say =AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for "intersection of this col & this range"?
And to you Ed. See you in the New Year.
Bob "Ed" wrote in message ... Bob, it looks like I'm asking questions that aren't as meaningful as I thought, so the answers don't seem to make sense to me. I think I need to sit back and take a better look at this and get a better handle on what I need to do. Then maybe I can ask better questions, and get a better grasp on both the problem and the answers, rather than having both of us chase this thing around in circles. Thanks for all your input. You've given me some things to chew on over the holidays. Because I'm doing all this at work, I may not be back 'til after the New Year, so I hope you and yours have a very happy holiday season. Ed "Bob Phillips" wrote in message ... Ed, It still looks complex to me. You have to have named ranges that define the start and end rows, so where is the difference? Could you not add some identifier to say column A for each named range, and do a SUMIF on that value? -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob: The formula =AVERAGE($B$3:OFFSET(B20,-1,0)) does work, even with adding and deleting rows. Thank you. I have to say you're solution (appears) is more complex than seems necessary. And I'm sorry if I'm making this much more convoluted than necessary. But if I have this: What I'm looking at are several of these ranges stacked under each other. My first named range may cover rows 3-20, the second 24-30, etc. for four ranges of 20 columns each, then won't I need to go in and determine the top and bottom rows of each range, then loop 80 times setting formulas with the correct cell references? I thought if there was a simple syntax that would pick up the column number of the active cell and intersect that column with the name of the range I'm working with, there would be much less chance for error, as it would be the same syntax repeated for each cell that needs a formula. Too much like work? Ed "Bob Phillips" wrote in message ... Ed, that would work if you used say =AVERAGE(B1:B19 test) where test is the named range. You can't use the whole column as this formula will be in B20, so you will get a circular reference. But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19). If it is because you don't know what cell the formula is in, and therefore where the last cell to be averaged is, just use =AVERAGE($C$1:OFFSET(C20,-1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Bob - Sorry for the delay in the reply. I got caught by some work and then it was time to log off. From what I saw of the AVERAGE worksheet function Help, your syntax might not work, if I understand it (not saying that I do, mind you). What I'm looking at is (for example) a named range covering A3:F19. I want to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the intersection of that column with the named range. So the formula in B20 would pick up only the values in both Column B and the named range, ignoring anything in rows 1 and 2. That same formula in C20 could pick up only Col. C as it intersects the range. I'm using XL2000. Can you recommend something I should be looking at to construct this? Ed "Bob Phillips" wrote in message ... Ed, Do you mean something like =AVERAGE(G1:G9 G5:I6) -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... On Sheet1, I will have a column of item numbers, with each item having perhaps 20 columns of numbers following. I need to separate these items into groups, and take the average of all the numbers in that group in each column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15, the averages will go into row 16 across all the columns. Since the lengths of these groups can vary, it was my thought to use VBA to create a named range for each group. Then I could find the last row of the range, insert a new row, and run across the row in the required cells to insert a formula to average the numbers above that belong to the named range. Where I'm stuck is how to reference the intersection of the particular column and the named range. So in B11 (for my example above), I would have a formula that says "=AVERAGE (intersection of this column and the named range for Group1)". It seems like it should be easy, but I can't get my brain out of neutral! Any kick-starts would be appreciated. Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Syntax to "OR" 3 "ISERROR" conditions | Excel Worksheet Functions | |||
what is syntax for if(between range of dates,"Q1","Q2")? | Excel Worksheet Functions | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |