Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro help
My task is is pretty simple, but complicated to explain, so bear with me. I
am taking a column of data (column a), and taking the average of every 5 data points (column b), starting at a1. In column c, I am taking the average of every 5 data points in column b, starting with b5. I would like to create a macro that will do this for me, but I am having trouble figuring out how to do it. See below for my example. a b c 1. 1 2. 2 3. 3 4. 4 5. 5 3 6. 6 4 7. 7 5 8. 8 6 9. 9 7 10. 10 8 5 11. 11 9 6 12. 12 10 7 13. 13 11 8 14. 14 12 9 15. 15 13 10 16. 16 17. 17 18. 18 19. 19 20. 20 can anyone help? i'll explain better if you need me to. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro help
scott wrote:
My task is is pretty simple, but complicated to explain, so bear with me. I am taking a column of data (column a), and taking the average of every 5 data points (column b), starting at a1. In column c, I am taking the average of every 5 data points in column b, starting with b5. I would like to create a macro that will do this for me, but I am having trouble figuring out how to do it. See below for my example. a b c 1. 1 2. 2 3. 3 4. 4 5. 5 3 6. 6 4 7. 7 5 8. 8 6 9. 9 7 10. 10 8 5 11. 11 9 6 12. 12 10 7 13. 13 11 8 14. 14 12 9 15. 15 13 10 16. 16 17. 17 18. 18 19. 19 20. 20 can anyone help? i'll explain better if you need me to. No macro necessary, as far as I can tell. I think you meant to start column C with row 9 (the first five values in column B are in rows 5 through 9). B5=AVERAGE(A1:A5) C9=AVERAGE(B5:B9) Copy each formula down as needed. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro help
Yes, I agree that that formula works. However, for simplicity I only used
a1:a20. I am doing this for every day between January 1, 2001 and July 31, 2008. So by having a macro I would be saving a tremendous amount of time. You can see that I will be using a large number of cells; do you have any other suggestions? "Glenn" wrote: scott wrote: My task is is pretty simple, but complicated to explain, so bear with me. I am taking a column of data (column a), and taking the average of every 5 data points (column b), starting at a1. In column c, I am taking the average of every 5 data points in column b, starting with b5. I would like to create a macro that will do this for me, but I am having trouble figuring out how to do it. See below for my example. a b c 1. 1 2. 2 3. 3 4. 4 5. 5 3 6. 6 4 7. 7 5 8. 8 6 9. 9 7 10. 10 8 5 11. 11 9 6 12. 12 10 7 13. 13 11 8 14. 14 12 9 15. 15 13 10 16. 16 17. 17 18. 18 19. 19 20. 20 can anyone help? i'll explain better if you need me to. No macro necessary, as far as I can tell. I think you meant to start column C with row 9 (the first five values in column B are in rows 5 through 9). B5=AVERAGE(A1:A5) C9=AVERAGE(B5:B9) Copy each formula down as needed. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro help
On Aug 19, 4:11 pm, scott wrote:
Yes, I agree that that formula works. However, for simplicity I only used a1:a20. I am doing this for every day between January 1, 2001 and July 31, 2008. So by having a macro I would be saving a tremendous amount of time. You can see that I will be using a large number of cells; do you have any other suggestions? Well, since it is the same formula every time, you just have to copy it to all the other cells. This can be done for thousands and thousands of cells if necessary in just a few seconds. Esp if you know how to move and select ranges with the keyboard. CTRL-SHIFT-UP and so forth. Makes everything go faster, really. If it is exactly like the above, starting in B5 (don't use it above row 5), use this formula instead: =IF(A1="","",AVERAGE(A1:A5)) That can be copy/pasted to an easy-to-select rectangle block of cells and be done in an instant. And it will give your staggered look, regardless of how many columns you need. No doubt a macro can also do something like this fast, but you also have to write a new one each time you have a slightly different task. I find good use of the keyboard (skip the mouse, it slows you down) to be very fast. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro help
scott wrote:
My task is is pretty simple, but complicated to explain, so bear with me. I am taking a column of data (column a), and taking the average of every 5 data points (column b), starting at a1. In column c, I am taking the average of every 5 data points in column b, starting with b5. I would like to create a macro that will do this for me, but I am having trouble figuring out how to do it. See below for my example. a b c 1. 1 2. 2 3. 3 4. 4 5. 5 3 6. 6 4 7. 7 5 8. 8 6 9. 9 7 10. 10 8 5 11. 11 9 6 12. 12 10 7 13. 13 11 8 14. 14 12 9 15. 15 13 10 16. 16 17. 17 18. 18 19. 19 20. 20 can anyone help? i'll explain better if you need me to. "Glenn" wrote: No macro necessary, as far as I can tell. I think you meant to start column C with row 9 (the first five values in column B are in rows 5 through 9). B5=AVERAGE(A1:A5) C9=AVERAGE(B5:B9) Copy each formula down as needed. scott wrote: Yes, I agree that that formula works. However, for simplicity I only used a1:a20. I am doing this for every day between January 1, 2001 and July 31, 2008. So by having a macro I would be saving a tremendous amount of time. You can see that I will be using a large number of cells; do you have any other suggestions? Can't see how you would save any significant time. Look at "Fill data within a row or column" in the help file. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions |