Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average to date
Hi,
I have a number of items that I track monthly costs on in rows 7 to 21. In columns C to N are the months Jan to Dec. I would like to average the costs to a specified month. As this month can change It is entered in Cell O6. At present I match the month specified in O6 and add that to 66 to get the character value of the column I am interested in. Then I use indirect to create the reference I require the average to be done on. vis:=AVERAGE(INDIRECT("C7:"&CHAR(MATCH(O6,C5:N5,0) +66)&"7")) Now I want to copy this down to the 10 rows below. So that it works correctly I will need to create references to the rows as well as the columns. This can be done with some more indirect functions but I was hoping that there was an easier way. Any suggestions will be appreciated. Regards. Sean |
#2
|
|||
|
|||
Average to date
Hi!
Try this: =AVERAGE(C7:INDEX(C7:N7,MATCH(O$6,C$5:N$5,0))) Biff "Sean Bartleet" wrote in message ... Hi, I have a number of items that I track monthly costs on in rows 7 to 21. In columns C to N are the months Jan to Dec. I would like to average the costs to a specified month. As this month can change It is entered in Cell O6. At present I match the month specified in O6 and add that to 66 to get the character value of the column I am interested in. Then I use indirect to create the reference I require the average to be done on. vis:=AVERAGE(INDIRECT("C7:"&CHAR(MATCH(O6,C5:N5,0) +66)&"7")) Now I want to copy this down to the 10 rows below. So that it works correctly I will need to create references to the rows as well as the columns. This can be done with some more indirect functions but I was hoping that there was an easier way. Any suggestions will be appreciated. Regards. Sean |
#3
|
|||
|
|||
Average to date
Biff,
Thanks for the formula, it works great. I would appreciate some explanation as to how the formula works. I have never seen a formula where a formula is used as part of an range reference. I understand the INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)). This would normally return the value of the cell for the specified month. It would seem that when used as part of a range reference that it returns the cell reference and not the value. Is this documented anywhere? I can get this to work with an If function but not the Vlookup function. Is this a limitation of the Vlookup function? Thanks again for the help. Sean "Biff" wrote in message ... Hi! Try this: =AVERAGE(C7:INDEX(C7:N7,MATCH(O$6,C$5:N$5,0))) Biff "Sean Bartleet" wrote in message ... Hi, I have a number of items that I track monthly costs on in rows 7 to 21. In columns C to N are the months Jan to Dec. I would like to average the costs to a specified month. As this month can change It is entered in Cell O6. At present I match the month specified in O6 and add that to 66 to get the character value of the column I am interested in. Then I use indirect to create the reference I require the average to be done on. vis:=AVERAGE(INDIRECT("C7:"&CHAR(MATCH(O6,C5:N5,0) +66)&"7")) Now I want to copy this down to the 10 rows below. So that it works correctly I will need to create references to the rows as well as the columns. This can be done with some more indirect functions but I was hoping that there was an easier way. Any suggestions will be appreciated. Regards. Sean |
#4
|
|||
|
|||
Average to date
Hi!
I understand the INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)). This would normally return the value of the cell for the specified month. It would seem that when used as part of a range reference that it returns the cell reference and not the value. That is correct. Is this documented anywhere? I'm sure it is "somewhere" but I've never seen it myself. I learned it from this group! 90% of what I know I've learned right here! I can get this to work with an If function but not the Vlookup function. Is this a limitation of the Vlookup function? I've never tried (or needed) to use that method in a lookup (that I can recall). How are you trying to implement it? Biff "Sean Bartleet" wrote in message ... Biff, Thanks for the formula, it works great. I would appreciate some explanation as to how the formula works. I have never seen a formula where a formula is used as part of an range reference. I understand the INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)). This would normally return the value of the cell for the specified month. It would seem that when used as part of a range reference that it returns the cell reference and not the value. Is this documented anywhere? I can get this to work with an If function but not the Vlookup function. Is this a limitation of the Vlookup function? Thanks again for the help. Sean "Biff" wrote in message ... Hi! Try this: =AVERAGE(C7:INDEX(C7:N7,MATCH(O$6,C$5:N$5,0))) Biff "Sean Bartleet" wrote in message ... Hi, I have a number of items that I track monthly costs on in rows 7 to 21. In columns C to N are the months Jan to Dec. I would like to average the costs to a specified month. As this month can change It is entered in Cell O6. At present I match the month specified in O6 and add that to 66 to get the character value of the column I am interested in. Then I use indirect to create the reference I require the average to be done on. vis:=AVERAGE(INDIRECT("C7:"&CHAR(MATCH(O6,C5:N5,0) +66)&"7")) Now I want to copy this down to the 10 rows below. So that it works correctly I will need to create references to the rows as well as the columns. This can be done with some more indirect functions but I was hoping that there was an easier way. Any suggestions will be appreciated. Regards. Sean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
search for latest date | Excel Worksheet Functions | |||
52 week average based on date | Excel Discussion (Misc queries) |