Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
I would like to find a formula that allows me to sum or average numbers from
a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Is this what you mean?
=SUM(B:B)-SUMIF(A:A,""&A2+D2,B:B) -- __________________________________ HTH Bob "Zorro" wrote in message ... I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Don
Thank you for your reply. Using the formula that you have given me returns the date on which value from the left-hand column. I think I may not have explained what I am trying to do very well so by way of clarification: I record data on a regular basis but not every day. What I want to be able to do is to average the values recorded for the last 30 days. Obviously, if I had a value in for every sequential day, just averaging the last 30 rows would be easy. The problem is that the number of rows to be averaged keeps changing depending on how many days in the last 30 have a value recorded against them. Sorry if my original question was misleading. I would be grateful for any help you can give. Many thanks "Don Guillett" wrote: try this idea AVERAGE(a2:OFFSET(a2,d2,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Zorro" wrote in message ... I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Bob
Thank you for your reply. When I use this formula, it seems to sum a defined number of rows but does not take account of the actual date values. I think I may have explained my problem badly so by way of clarification: I record data on a regular basis but not every day. What I want to be able to do is to average the values recorded for the last 30 days. Obviously, if I had a value in for every sequential day, just averaging the last 30 rows would be easy. The problem is that the number of rows to be averaged keeps changing depending on how many days in the last 30 have a value recorded against them so I need a way of calculating how many rows of data should be averaged. Sorry if my original question was misleading. I would be grateful for any help you can give. Many thanks "Bob Phillips" wrote: Is this what you mean? =SUM(B:B)-SUMIF(A:A,""&A2+D2,B:B) -- __________________________________ HTH Bob "Zorro" wrote in message ... I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Zorro wrote:
I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks For SUM: =SUMPRODUCT(($A$2:$A$100=A2)*($A$2:$A$100<A2+$D$2 ),$B$2:$B$100) For Average: =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Glenn
Thank you for your reply. When I use this formula, it does not seem to take account of the actual date values. I think I may have explained my problem badly so by way of clarification: I record data on a regular basis but not every day. What I want to be able to do is to average the values recorded for the last 30 days. Obviously, if I had a value in for every sequential day, just averaging the last 30 rows would be easy. The problem is that the number of rows to be averaged keeps changing depending on how many days in the last 30 have a value recorded against them so I need a way of calculating how many rows of data should be averaged. Sorry if my original question was misleading. I would be grateful for any help you can give. Many thanks "Glenn" wrote: Zorro wrote: I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks For SUM: =SUMPRODUCT(($A$2:$A$100=A2)*($A$2:$A$100<A2+$D$2 ),$B$2:$B$100) For Average: =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Glenn wrote:
Zorro wrote: I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks For SUM: =SUMPRODUCT(($A$2:$A$100=A2)*($A$2:$A$100<A2+$D$2 ),$B$2:$B$100) For Average: =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2)) Sorry, I inserted a column for Average (and deleted a row) between copying these formulas. Assuming Date, Value, Sum, Average and Days in A:E (and only a single value in E2 for Days), the correct formulas, which can be copied down the columns, should be as follows: =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99) =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Zorro wrote:
Glenn Thank you for your reply. When I use this formula, it does not seem to take account of the actual date values. Did you actually try them (or the corrected versions I also posted), or just decide they won't work based upon visual inspection? If you did try them, please describe the actual data and results so we can get to the bottom of the problem. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Don
Thank you for your reply and apologies for any confusion I may have caused. In answer to your questions, I meant calendar days and cell D2 contains the number of days over which I want to calculate the sum or average so that, for example, by putting 30 in that cell I would calculate a 30 day average and by putting 7 in the cell I would calculate a seven day average. I am setting out below an example of some real data to which I would like to apply this. In the period 12 October, 2008 to 1 December, 2008 I have calculated a 30 day rolling average manually to show the results that I would expect. I have not done so for the other numbers. To explain this further: the value of 320 showed against 12 October is the average of all the values going back to 29 September which is the first value and is less than 30 days before 12 October The value of 294 against December 1 is the average of the four values going back to 9 November which is the earliest date within 30 days before 1 December Once again my apologies for any confusion I may have caused and I would be very grateful for any help you can give. Many thanks Date Value Rolling Average Days 29 September 2008 200 30 02 October 2008 207 05 October 2008 480 08 October 2008 221 10 October 2008 398 12 October 2008 416 320 09 November 2008 254 335 10 November 2008 509 393 30 November 2008 207 323 01 December 2008 207 294 02 December 2008 207 14 December 2008 505 16 December 2008 590 20 December 2008 650 22 December 2008 650 24 December 2008 645 04 January 2009 484 11 January 2009 487 12 January 2009 513 15 January 2009 502 "Don Guillett" wrote: When you say the last 30 days do you mean 30 days or 30 calendar days? What did cell d2 have to do with it? perhaps a clearer explanation along with before/after examples would be nice. -- Don Guillett Microsoft MVP Excel SalesAid Software "Zorro" wrote in message ... Don Thank you for your reply. Using the formula that you have given me returns the date on which value from the left-hand column. I think I may not have explained what I am trying to do very well so by way of clarification: I record data on a regular basis but not every day. What I want to be able to do is to average the values recorded for the last 30 days. Obviously, if I had a value in for every sequential day, just averaging the last 30 rows would be easy. The problem is that the number of rows to be averaged keeps changing depending on how many days in the last 30 have a value recorded against them. Sorry if my original question was misleading. I would be grateful for any help you can give. Many thanks "Don Guillett" wrote: try this idea AVERAGE(a2:OFFSET(a2,d2,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Zorro" wrote in message ... I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Do you mean the last 30 days from today?
Try this =SUMPRODUCT(--(A2:A200<=TODAY()),--(A2:A200TODAY()+D2),B:B) -- __________________________________ HTH Bob "Zorro" wrote in message ... Bob Thank you for your reply. When I use this formula, it seems to sum a defined number of rows but does not take account of the actual date values. I think I may have explained my problem badly so by way of clarification: I record data on a regular basis but not every day. What I want to be able to do is to average the values recorded for the last 30 days. Obviously, if I had a value in for every sequential day, just averaging the last 30 rows would be easy. The problem is that the number of rows to be averaged keeps changing depending on how many days in the last 30 have a value recorded against them so I need a way of calculating how many rows of data should be averaged. Sorry if my original question was misleading. I would be grateful for any help you can give. Many thanks "Bob Phillips" wrote: Is this what you mean? =SUM(B:B)-SUMIF(A:A,""&A2+D2,B:B) -- __________________________________ HTH Bob "Zorro" wrote in message ... I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Glenn
Thank you for this. I think we are nearly there. The formula that you have given me seems to contain a typo in that it references cell E2. When I correct that to D2 it does add up a 30 day range of values but it does so going forward rather than backwards so that, for example, if you have a range of values recorded between 1 December and 25 December, the entry against 1 December will add up all of values between 1 December and 25 December whereas the value against 25 December will just be that value assuming that 25 December is the last value recorded. I would be grateful if you can tell me how I should modify the formula so that it calculates the other way round. Once again many thanks for your help: it really is very much appreciated. "Glenn" wrote: Glenn wrote: Zorro wrote: I would like to find a formula that allows me to sum or average numbers from a column of data over a number of days to be entered in a separate cell. The data consists of a "date" column with not all days included and an associated column of values for example: A B C D Date Value Rolling average Days for average 1/12/2008 150 30 10/12/2008 183 1/1/2009 100 1/5/2009 200 1/6/2009 130 1/10/2009 150 What I would like to be able to do this to calculate an ongoing average for each row based on a value (e.g. 30 as shown in column D of the example above) entered in another cell. I have tried to do this using the sumif function but, although it works if I use an absolute number in the criteria field, as soon as I try to reference a value contained in a cell it returns the value of zero. I would be very grateful for any help with this. Many thanks For SUM: =SUMPRODUCT(($A$2:$A$100=A2)*($A$2:$A$100<A2+$D$2 ),$B$2:$B$100) For Average: =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2)) Sorry, I inserted a column for Average (and deleted a row) between copying these formulas. Assuming Date, Value, Sum, Average and Days in A:E (and only a single value in E2 for Days), the correct formulas, which can be copied down the columns, should be as follows: =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99) =SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2)) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Zorro wrote:
Don Thank you for your reply and apologies for any confusion I may have caused. In answer to your questions, I meant calendar days and cell D2 contains the number of days over which I want to calculate the sum or average so that, for example, by putting 30 in that cell I would calculate a 30 day average and by putting 7 in the cell I would calculate a seven day average. I am setting out below an example of some real data to which I would like to apply this. In the period 12 October, 2008 to 1 December, 2008 I have calculated a 30 day rolling average manually to show the results that I would expect. I have not done so for the other numbers. To explain this further: the value of 320 showed against 12 October is the average of all the values going back to 29 September which is the first value and is less than 30 days before 12 October The value of 294 against December 1 is the average of the four values going back to 9 November which is the earliest date within 30 days before 1 December Once again my apologies for any confusion I may have caused and I would be very grateful for any help you can give. Many thanks Date Value Rolling Average Days 29 September 2008 200 30 02 October 2008 207 05 October 2008 480 08 October 2008 221 10 October 2008 398 12 October 2008 416 320 09 November 2008 254 335 10 November 2008 509 393 30 November 2008 207 323 01 December 2008 207 294 02 December 2008 207 14 December 2008 505 16 December 2008 590 20 December 2008 650 22 December 2008 650 24 December 2008 645 04 January 2009 484 11 January 2009 487 12 January 2009 513 15 January 2009 502 Put this in C2 and copy down as needed. Adjust the 100's to include all of your data. =SUMPRODUCT(($A$2:$A$100<=A2)*($A$2:$A$100A2-$D$2),$B$2:$B$100)/SUMPRODUCT(($A$2:$A$100<=A2)*($A$2:$A$100A2-$D$2)) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Zorro wrote:
Glenn Thank you for this. I think we are nearly there. The formula that you have given me seems to contain a typo in that it references cell E2. When I correct that to D2 it does add up a 30 day range of values but it does so going forward rather than backwards so that, for example, if you have a range of values recorded between 1 December and 25 December, the entry against 1 December will add up all of values between 1 December and 25 December whereas the value against 25 December will just be that value assuming that 25 December is the last value recorded. I would be grateful if you can tell me how I should modify the formula so that it calculates the other way round. Once again many thanks for your help: it really is very much appreciated. Corrected (again) in another reply. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total over a find number of days
Glenn
Many thanks: problem solved! Many thanks also to Don and Bob for their help as well. "Glenn" wrote: Zorro wrote: Don Thank you for your reply and apologies for any confusion I may have caused. In answer to your questions, I meant calendar days and cell D2 contains the number of days over which I want to calculate the sum or average so that, for example, by putting 30 in that cell I would calculate a 30 day average and by putting 7 in the cell I would calculate a seven day average. I am setting out below an example of some real data to which I would like to apply this. In the period 12 October, 2008 to 1 December, 2008 I have calculated a 30 day rolling average manually to show the results that I would expect. I have not done so for the other numbers. To explain this further: the value of 320 showed against 12 October is the average of all the values going back to 29 September which is the first value and is less than 30 days before 12 October The value of 294 against December 1 is the average of the four values going back to 9 November which is the earliest date within 30 days before 1 December Once again my apologies for any confusion I may have caused and I would be very grateful for any help you can give. Many thanks Date Value Rolling Average Days 29 September 2008 200 30 02 October 2008 207 05 October 2008 480 08 October 2008 221 10 October 2008 398 12 October 2008 416 320 09 November 2008 254 335 10 November 2008 509 393 30 November 2008 207 323 01 December 2008 207 294 02 December 2008 207 14 December 2008 505 16 December 2008 590 20 December 2008 650 22 December 2008 650 24 December 2008 645 04 January 2009 484 11 January 2009 487 12 January 2009 513 15 January 2009 502 Put this in C2 and copy down as needed. Adjust the 100's to include all of your data. =SUMPRODUCT(($A$2:$A$100<=A2)*($A$2:$A$100A2-$D$2),$B$2:$B$100)/SUMPRODUCT(($A$2:$A$100<=A2)*($A$2:$A$100A2-$D$2)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of days in a running period | Excel Worksheet Functions | |||
Can I calculate the number of days taken off in a running period | Excel Worksheet Functions | |||
Running total of hours based on last 7 days | Excel Worksheet Functions | |||
Add a particular number as a running total | Excel Worksheet Functions | |||
running total for last 7 calender days | Excel Discussion (Misc queries) |