LinkBack URL
About LinkBacks
Wonder if anyone can help - I want to average the last 32 entries in a column but I also want add entries at the bottom and let the formula update.
Cheers in anticipation
ATB
Can I do this at the bottom of the same column?
Wonder if anyone can help - I want to average the last 32 entries in a column but I also want add entries at the bottom and let the formula update.
Cheers in anticipation
ATB
Can I do this at the bottom of the same column?
Last edited by atbell99; 03-11-2008 at 02:26 AM.
Best bet is to used Assigned names.
Let's say your data is from B3 onward with nothing a header in B2 and nothing in B1 (need to know this to subtract it out later)
Under Define names, define Last32Then Add, OK.
Please Login or Register to view this content.
In C1 (anywhere but B1)This will only work if you don't have blanks however. Can you work with that?Please Login or Register to view this content.
ChemistB
If you do have nulls (blank cells) in your list, you might take a look at this post: http://www.mrexcel.com/forum/showthread.php?t=308177
You could adjust the formula ranges to suit your needs. To get the last 32, change the "-10" to "-32" and the "-9" to "-31". Fill the formula down more rows than are needed, so it will automatically include new entries (will show blank if nothing is in the first column).
I need to place the info in the same column at the bottom and also need to use the same formula for adjacent columnsOriginally Posted by ChemistB
Best bet is to used Assigned names.
Let's say your data is from B3 onward with nothing a header in B2 and nothing in B1 (need to know this to subtract it out later
Under Define names, define Last32Then Add, OK.Please Login or Register to view this content.
In C1 (anywhere but B1)This will only work if you don't have blanks however. Can you work with that?Please Login or Register to view this content.
ChemistB
Hi
If you use same column to place the average, it becomes a circular reference in a formula. A macro can help you. try pasting the following codes in the macro window (Alt f11)
run the macro. It will find the average of last 32 entries of each column and positions it in the cell after last one.Please Login or Register to view this content.
Ravi
First question, if you are constantly adding rows of data, how do you know where the bottom is? In cases like that, I try to put my averages at the top of the column but will still work either way. You need to modify the formula appropriately, eg.I need to place the info in the same column at the bottom
Please Login or Register to view this content.Naming additional columns are easier, you use the name for column B and modify it. For example, for column C name NColC defined asand also need to use the same formula for adjacent columnsThis offsets the cells you had in column B by 1 column.Please Login or Register to view this content.
ChemistB
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks