Average: 2nd argument as a variable in another cell av(c6:c(a3))
I bring in lists of various lengths to be averaged. My averaging forumlas are
already set up on the page. If the list is shorter than my predetermined column length, I get errors of DIV by zero. If they are longer, then not all are included. I want to count the length of the list and use the result as a variable in my averaging formula so the column length is always the same as the list I bring in. For example: AV(c6:c(a3)) where 'a3' is the count result of my list. The error I get is the #NAME? error. I am using Excel 2003. |
Average: 2nd argument as a variable in another cell av(c6:c(a3))
Tony --
I'm confident someone else will present something more elegant. In the meantime, here's a 'brute force' approach A B 1 1 =COUNT(A:A) 2 2 =ADDRESS(B1,A1) 3 3 =AVERAGE(A1:INDIRECT(B2)) 4 4 5 5 The result in B1 is 5 B2 $A$5 B3 3 Modify to fit. HTH "Tony the Tiger" wrote: I bring in lists of various lengths to be averaged. My averaging forumlas are already set up on the page. If the list is shorter than my predetermined column length, I get errors of DIV by zero. If they are longer, then not all are included. I want to count the length of the list and use the result as a variable in my averaging formula so the column length is always the same as the list I bring in. For example: AV(c6:c(a3)) where 'a3' is the count result of my list. The error I get is the #NAME? error. I am using Excel 2003. |
Average: 2nd argument as a variable in another cell av(c6:c(a3))
TtT,
=AVERAGE(OFFSET(C6,0,0,A3,1)) -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Tony the Tiger" <Tony the wrote in message I bring in lists of various lengths to be averaged. My averaging forumlas are already set up on the page. If the list is shorter than my predetermined column length, I get errors of DIV by zero. If they are longer, then not all are included. I want to count the length of the list and use the result as a variable in my averaging formula so the column length is always the same as the list I bring in. For example: AV(c6:c(a3)) where 'a3' is the count result of my list. The error I get is the #NAME? error. I am using Excel 2003. |
All times are GMT +1. The time now is 10:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com