I am trying to optimize on an average in a spreadsheet. My result depends on the length of the average. I would like to be able to enter a value in a cell and change the length of an average.
For instance, if the value in the cell is 10, the formula would be =Average(A2:A11), but if the value in the cell is 20, the formula would change to =Average(A2:A21).
The goal is to have one cell that changes the length of all of the average formulas that "call" that cell. Is this possible?
Hi.
Assuming the cell which holds your choice is B1:
=AVERAGE(A2:INDEX(A:A,B1+1))
Regards
Another approach
B1 is choice cell.
Thanks
Please Login or Register to view this content.
Mark the thread as solved if you are satisfied with the answer.
In your first post under the thread tools.
Mahju
After working with this I realized that I mis-stated the question. It is a moving average that I am trying to use, so the range of cells move with each row. When I use the concepts above it calls a single stationary cell. What I need to call is a moving cell that gives me a 20 row moving average, or a 25 row moving average, or a 60 row moving average. Is that possible?
So how would you know where the average starts and ends?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I seem to be unable to upload an image. Column A has a list of daily prices. Column B is the 20 period moving average of those prices. So B1 shows the average of A1 through A21. B2 shows the average of A2 through A22... and so on. Column A has thousands of rows. I would like to be able to vary the length of the moving average with a number placed in say cell D1. If D1 is 20, it calculates a 20 period moving average. Does this answer the question? (I apologize for not wording my questions clearer. I am realizing how difficult it is to frame the question correctly.) I appreciate the help.
In B2 and copied/dragged down:
=AVERAGE(OFFSET(A2,0,0,$D$1,1))
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
we cant use images anyway, you would need to upload a sample workbook.
Anyway, try this...
B1=start position
B2=number of "rows"
C1=AVERAGE(INDEX(A:A,B1):INDEX(A:A,B1+B2))
=AVERAGE(OFFSET(A2,0,0,$D$1,1))
Works! Thank you all very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks