Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
I have 3 cols such that they look like this:
colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
If the values in A are the means for those periods, then in D3 use the
formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
The numeric values in colC are the ones to deal with i.e. take their average.
colA only contains what I have typed out i.e a date and the 3-month. The numeric values in colC correspond to the month values as defined by colB. My aim is to find the mean value of the predictor variable (i.e colC) during the past 6 months. And this mean value is to go into colD. I have many predictor variables and colC is only one of them. My problem is a forecasting problem. There is a 2nd question to my problem which instead of looking backwards it is looking ahead and taking the sum of the comming 3 month but probably it will be too confusing to do it at once. However after saying all that I acknowledge your help and am most thankful. Speedy "David Biddulph" wrote: If the values in A are the means for those periods, then in D3 use the formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
If your data values start in row 2 (with headers in row 1), then in D4 use
the formula =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) and copy down. You can modify the formula to cope with a different length of period and with looking forward not back. -- David Biddulph "Speedy" wrote in message ... The numeric values in colC are the ones to deal with i.e. take their average. colA only contains what I have typed out i.e a date and the 3-month. The numeric values in colC correspond to the month values as defined by colB. My aim is to find the mean value of the predictor variable (i.e colC) during the past 6 months. And this mean value is to go into colD. I have many predictor variables and colC is only one of them. My problem is a forecasting problem. There is a 2nd question to my problem which instead of looking backwards it is looking ahead and taking the sum of the comming 3 month but probably it will be too confusing to do it at once. However after saying all that I acknowledge your help and am most thankful. Speedy "David Biddulph" wrote: If the values in A are the means for those periods, then in D3 use the formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
Can you explain what your formula means. My data starts in C2 and in D8 I
have =average(c2:c7). In D9 is =average(c5:c10). In D10 =average(c8:c13) and so on. I want to average. Speedy "David Biddulph" wrote: If your data values start in row 2 (with headers in row 1), then in D4 use the formula =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) and copy down. You can modify the formula to cope with a different length of period and with looking forward not back. -- David Biddulph "Speedy" wrote in message ... The numeric values in colC are the ones to deal with i.e. take their average. colA only contains what I have typed out i.e a date and the 3-month. The numeric values in colC correspond to the month values as defined by colB. My aim is to find the mean value of the predictor variable (i.e colC) during the past 6 months. And this mean value is to go into colD. I have many predictor variables and colC is only one of them. My problem is a forecasting problem. There is a 2nd question to my problem which instead of looking backwards it is looking ahead and taking the sum of the comming 3 month but probably it will be too confusing to do it at once. However after saying all that I acknowledge your help and am most thankful. Speedy "David Biddulph" wrote: If the values in A are the means for those periods, then in D3 use the formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
The way you showed it in your example, D2 was "mean of 1930(j,a,s,o,n,d)"
and D3 was "mean of 1930(o,n,d),1931(j,f,m)", so I had assumed that D4 was where you wanted 1931 (jfm amj), which is your =average(c2:c7). If, instead of the way you had it laid out in your original question, you now want that in D8 instead of D4, then just change my formula from =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) to =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1)) and put it in D8. As for explanation of the formula, the functions used are all standard Excel functions, and Excel help explains the syntax and gives examples. AVERAGE() is one that you probably understand already, but OFFSET() and ROW() are both functions that you can find in Excel help. The simple story is that when placed in cell D8, the function OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1) will give you the range C2:C7, whereas in cell D9 the same function will give a range moved down three rows (because of the fact that the current row number referred to by ROW() has increased from 8 to 9), and now refers to the range C5:C10. Q.E.D. -- David Biddulph "Speedy" wrote in message ... Can you explain what your formula means. My data starts in C2 and in D8 I have =average(c2:c7). In D9 is =average(c5:c10). In D10 =average(c8:c13) and so on. I want to average. Speedy "David Biddulph" wrote: If your data values start in row 2 (with headers in row 1), then in D4 use the formula =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) and copy down. You can modify the formula to cope with a different length of period and with looking forward not back. -- David Biddulph "Speedy" wrote in message ... The numeric values in colC are the ones to deal with i.e. take their average. colA only contains what I have typed out i.e a date and the 3-month. The numeric values in colC correspond to the month values as defined by colB. My aim is to find the mean value of the predictor variable (i.e colC) during the past 6 months. And this mean value is to go into colD. I have many predictor variables and colC is only one of them. My problem is a forecasting problem. There is a 2nd question to my problem which instead of looking backwards it is looking ahead and taking the sum of the comming 3 month but probably it will be too confusing to do it at once. However after saying all that I acknowledge your help and am most thankful. Speedy "David Biddulph" wrote: If the values in A are the means for those periods, then in D3 use the formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
Hello David,
Thank you so much for the formula, it worked very well, now I just have to experiment around to suit my other requirements e.g previous 5-,4-,3- months and then ahead 3-,4-months etc. My problem with the XL help is they only give the simple examples. Because of that can I just ask you from your fomula: =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1)) what 0,6,1 at the end refer to. I think I know 6 refers to the 6 months to be averaged but I don't know how 0 and 1 contribute to the formula. I do acknowlege that I did not explain my problem that well. Thanks again for your great help. Speedy "David Biddulph" wrote: The way you showed it in your example, D2 was "mean of 1930(j,a,s,o,n,d)" and D3 was "mean of 1930(o,n,d),1931(j,f,m)", so I had assumed that D4 was where you wanted 1931 (jfm amj), which is your =average(c2:c7). If, instead of the way you had it laid out in your original question, you now want that in D8 instead of D4, then just change my formula from =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) to =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1)) and put it in D8. As for explanation of the formula, the functions used are all standard Excel functions, and Excel help explains the syntax and gives examples. AVERAGE() is one that you probably understand already, but OFFSET() and ROW() are both functions that you can find in Excel help. The simple story is that when placed in cell D8, the function OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1) will give you the range C2:C7, whereas in cell D9 the same function will give a range moved down three rows (because of the fact that the current row number referred to by ROW() has increased from 8 to 9), and now refers to the range C5:C10. Q.E.D. -- David Biddulph "Speedy" wrote in message ... Can you explain what your formula means. My data starts in C2 and in D8 I have =average(c2:c7). In D9 is =average(c5:c10). In D10 =average(c8:c13) and so on. I want to average. Speedy "David Biddulph" wrote: If your data values start in row 2 (with headers in row 1), then in D4 use the formula =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) and copy down. You can modify the formula to cope with a different length of period and with looking forward not back. -- David Biddulph "Speedy" wrote in message ... The numeric values in colC are the ones to deal with i.e. take their average. colA only contains what I have typed out i.e a date and the 3-month. The numeric values in colC correspond to the month values as defined by colB. My aim is to find the mean value of the predictor variable (i.e colC) during the past 6 months. And this mean value is to go into colD. I have many predictor variables and colC is only one of them. My problem is a forecasting problem. There is a 2nd question to my problem which instead of looking backwards it is looking ahead and taking the sum of the comming 3 month but probably it will be too confusing to do it at once. However after saying all that I acknowledge your help and am most thankful. Speedy "David Biddulph" wrote: If the values in A are the means for those periods, then in D3 use the formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO Average Previous values
The syntax of the OFFSET function is shown in Help. It says
"OFFSET(reference,rows,cols,height,width) Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value. Rows is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference). Cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference). Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number. Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number." -- David Biddulph "Speedy" wrote in message ... Hello David, Thank you so much for the formula, it worked very well, now I just have to experiment around to suit my other requirements e.g previous 5-,4-,3- months and then ahead 3-,4-months etc. My problem with the XL help is they only give the simple examples. Because of that can I just ask you from your fomula: =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1)) what 0,6,1 at the end refer to. I think I know 6 refers to the 6 months to be averaged but I don't know how 0 and 1 contribute to the formula. I do acknowlege that I did not explain my problem that well. Thanks again for your great help. Speedy "David Biddulph" wrote: The way you showed it in your example, D2 was "mean of 1930(j,a,s,o,n,d)" and D3 was "mean of 1930(o,n,d),1931(j,f,m)", so I had assumed that D4 was where you wanted 1931 (jfm amj), which is your =average(c2:c7). If, instead of the way you had it laid out in your original question, you now want that in D8 instead of D4, then just change my formula from =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) to =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1)) and put it in D8. As for explanation of the formula, the functions used are all standard Excel functions, and Excel help explains the syntax and gives examples. AVERAGE() is one that you probably understand already, but OFFSET() and ROW() are both functions that you can find in Excel help. The simple story is that when placed in cell D8, the function OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1) will give you the range C2:C7, whereas in cell D9 the same function will give a range moved down three rows (because of the fact that the current row number referred to by ROW() has increased from 8 to 9), and now refers to the range C5:C10. Q.E.D. -- David Biddulph "Speedy" wrote in message ... Can you explain what your formula means. My data starts in C2 and in D8 I have =average(c2:c7). In D9 is =average(c5:c10). In D10 =average(c8:c13) and so on. I want to average. Speedy "David Biddulph" wrote: If your data values start in row 2 (with headers in row 1), then in D4 use the formula =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) and copy down. You can modify the formula to cope with a different length of period and with looking forward not back. -- David Biddulph "Speedy" wrote in message ... The numeric values in colC are the ones to deal with i.e. take their average. colA only contains what I have typed out i.e a date and the 3-month. The numeric values in colC correspond to the month values as defined by colB. My aim is to find the mean value of the predictor variable (i.e colC) during the past 6 months. And this mean value is to go into colD. I have many predictor variables and colC is only one of them. My problem is a forecasting problem. There is a 2nd question to my problem which instead of looking backwards it is looking ahead and taking the sum of the comming 3 month but probably it will be too confusing to do it at once. However after saying all that I acknowledge your help and am most thankful. Speedy "David Biddulph" wrote: If the values in A are the means for those periods, then in D3 use the formula =(A1+A2)/2, and fill down If the values in A are the totals for those periods, then in D3 use the formula =(A1+A2)/6, and fill down -- David Biddulph "Speedy" wrote in message ... I have 3 cols such that they look like this: colA colB colC colD 1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d) 1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m) 1931jas 1931mar 3 .... 1931ond 1931apr 4 ..... where j is jan, f is feb, m is march and so on 1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till dec1930 I want to calculate the mean of the previous 6 months which is to become colD. Speedy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of previous values | Excel Discussion (Misc queries) | |||
Average based on the previous row | Excel Worksheet Functions | |||
Delete row containing all previous values. | New Users to Excel | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions |