Hi all,
I have the problem of finding average of a range, that is specified by the numeric value, instead of using index value. For example, to find an average of a range A1:C1, then AVERAGE(A1:C1) would do the job.
However, specifying the parameters using numeric variables - >
AVERAGE(Range(Cells(1,1),Cells(3,1))) would not do it.
What is the correct way?
Any help is appreciated. Thanks!
Kim
it would be
AVERAGE(B1, B2, B3, B4, B5) 'etc..
Basically, you just need the cell name to reference the values.
Alternatively, if it resides in another sheet:
AVERAGE(Sheet!B1, Sheet!B2) 'etc..
Thank you.
But I cannot use the indexing like B1, B2. Actually the situation is like this:
Hours 1am 2am 3am 4am 5am
1 2 3 4 5
The inputs of this program is the hours, for example, from 3am to 4am.
Then, the average will calcuate (3+4)/2 ...
Since I dont know what the user will input, I cannot use A1,B1 .. or index ...
I was thinking since Range(Cells(1,3),Cells(1,4)) refers to A3:A4, then
Average(" Range(Cells(1,3),Cells(1,4))") should work but it doesnt ...
It should be a variation of the above ... but I dont know what that is.
Any help !! Thanks!!
Kim
Hi Kim,
Using your data as an example, and assuming that your data starts at A1, like this...
1am 2am 3am 4am 5am
1 2 3 4 5
...here are a couple of possibilities:
=AVERAGE(IF(($A$1:$E$1>=$G$1)*($A$1:$E$1<=$H$1),A2:E2))
entered using CTRL+SHIFT+ENTER
=AVERAGE(INDEX(A2:E2,MATCH($G$1,$A$1:$E$1,0)):INDEX(A2:E2,MATCH($H$1,$A$1:$E$1,0)))
Where G1 contains your first input (lower time limit) and H1 contains your second input (higher time limit).
With both of these formulas, you can enter them in F2 and copy down as far as you need to, if you have more data which continues down your spreadsheet.
Hope this helps!
Don’t have an account yet? Register yourself now and be a part of our community!
