|
averaging particular blocks of data
hi, I have 50 consecutive numbers in column A. I want to find the average of any required consecutive block, ie rows 3 to 11, or rows 8 to 51. The numbers of the start and end row are in cells D1 and E1, and change according to data on another sheet. I prefer a formula solution rather than macros, if it can be done. I am trying to understand indirect and address functions as I suspect they may be involved. thanks Robert -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=560642 |
averaging particular blocks of data
robert111 wrote: hi, I have 50 consecutive numbers in column A. I want to find the average of any required consecutive block, ie rows 3 to 11, or rows 8 to 51. The numbers of the start and end row are in cells D1 and E1, and change according to data on another sheet. I prefer a formula solution rather than macros, if it can be done. I am trying to understand indirect and address functions as I suspect they may be involved. thanks Robert -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=560642 Hi Robert, Maybe you can use something like this: =AVERAGE(INDIRECT("A"&D1&":A"&E1)) Regards, Bondi |
averaging particular blocks of data
Where you want the average to show up, enter this formula:
=AVERAGE(INDIRECT(D1):INDIRECT(E1)) INDIRECT() takes the address of a cell that contains yet another address and uses the contents to determine where to really go. So if you had A1 in D1 you end up starting at A1 for your average values. Another way of looking at INDIRECT - you want to send a letter to someone(Bill), but you don't know their address. But you know that another friend, Andy, does know the address, so you go to Andy and ask him for Bill's address, which he gives to you. "robert111" wrote: hi, I have 50 consecutive numbers in column A. I want to find the average of any required consecutive block, ie rows 3 to 11, or rows 8 to 51. The numbers of the start and end row are in cells D1 and E1, and change according to data on another sheet. I prefer a formula solution rather than macros, if it can be done. I am trying to understand indirect and address functions as I suspect they may be involved. thanks Robert -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=560642 |
averaging particular blocks of data
many thanks to both of you -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=560642 |
averaging particular blocks of data
I believe the formula that Don Guillett put up will do it for you - I didn't
realize, until I looked at his, that you'd said you just had row numbers in D1 and E1. I'd let myself get distracted by the word 'address' and thought the whole thing was in each of those. "robert111" wrote: many thanks to both of you -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=560642 |
averaging particular blocks of data
glad to help
-- Don Guillett SalesAid Software "robert111" wrote in message ... many thanks to both of you -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=560642 |
| All times are GMT +1. The time now is 01:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com