Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculate a 30-day moving average based on the last x number of entries and date


Hello, I have a worksheet that has all weekday dates in column 1 and
values in column 2. I want to create a 30-day moving average based on
the last (non-zero) value in the column 2. Since every month has a
different amount of days, I want it to search the date that has the
last value (since I don't get a chance to update it daily) and go back
thirsty days from that date and give an average of all the column 2
values skipping and values that are null or zero.

Any ideas?

Thanks,

Gimi


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculate a 30-day moving average based on the last x number of entries and date


Someone will probably have something better. However, here is a
possibility in the meantime.

I'm assuming Row 1 has your titles and Column A refers to your Column 1
and Column B to Column 2.

I'd put the following function in Column C: (Find the last date with a
value)
=A2 (For Cell C2)
=IF(B3<0,A3,C2) (For the rest)

I'd put the following function in Column D:
=(SUMIF($A$2:A2,"<="&C2,$B$2:B2)-SUMIF($A$2:A2,"<"&C2-29,$B$2:B2)) /
(COUNTIF($A$2:A2,"<="&C2)-COUNTIF($A$2:A2,"<"&C2-29))

If this isn't what you meant, you'll have to explain some more.

Scott

gimiv Wrote:
Hello, I have a worksheet that has all weekday dates in column 1 and
values in column 2. I want to create a 30-day moving average based on
the last (non-zero) value in the column 2. Since every month has a
different amount of days, I want it to search the date that has the
last value (since I don't get a chance to update it daily) and go back
thirsty days from that date and give an average of all the column 2
values skipping and values that are null or zero.

Any ideas?

Thanks,

Gimi



--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=558670

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default Calculate a 30-day moving average based on the last x number of en

Assume your last row is 1000
Then your average of the value in the second column for the last 30 days
would be:

=Average(Offset(B1000,0,0,-30,1))


"gimiv" wrote:


Hello, I have a worksheet that has all weekday dates in column 1 and
values in column 2. I want to create a 30-day moving average based on
the last (non-zero) value in the column 2. Since every month has a
different amount of days, I want it to search the date that has the
last value (since I don't get a chance to update it daily) and go back
thirsty days from that date and give an average of all the column 2
values skipping and values that are null or zero.

Any ideas?

Thanks,

Gimi


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 418
Default Calculate a 30-day moving average based on the last x number of entries and date

gimiv wrote:
Hello, I have a worksheet that has all weekday dates in column 1 and
values in column 2. I want to create a 30-day moving average based on
the last (non-zero) value in the column 2. Since every month has a
different amount of days, I want it to search the date that has the
last value (since I don't get a chance to update it daily) and go back
thirsty days from that date and give an average of all the column 2
values skipping and values that are null or zero.


The solution might be a lot simpler than you might think. But your
description leaves me with several questions, so I am not sure. Does
the following paradigm work for you?

Assume your data starts in B2. The first 30 days of data are in
B2:B31, some cells of which might be zero presumably because you "did
not get a chance to update it daily". It appears that you want the
following average, entered into C31 perhaps:

=sumif(B2:B31,"<0") / countif(B2:B31,"<0")

If you copy that down the column, the range will automatically be a
moving 30-day period; for example, B3:B32, B4:B33, etc. Thus, it
creates a trailing simple moving average, ignoring cells with zero.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Calculate a 30-day moving average based on the last x number of entries and date

Assuming that Column B contains the data, try...

=AVERAGE(IF(ROW(B2:B1000)=LARGE(IF(B2:B1000,ROW(B 2:B1000)),30),IF(B2:B10
00,B2:B1000)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
gimiv wrote:

Hello, I have a worksheet that has all weekday dates in column 1 and
values in column 2. I want to create a 30-day moving average based on
the last (non-zero) value in the column 2. Since every month has a
different amount of days, I want it to search the date that has the
last value (since I don't get a chance to update it daily) and go back
thirsty days from that date and give an average of all the column 2
values skipping and values that are null or zero.

Any ideas?

Thanks,

Gimi



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 418
Default Calculate a 30-day moving average based on the last x number of entries and date

wrote:
gimiv wrote:
values skipping and values that are null or zero.

[....]
=sumif(B2:B31,"<0") / countif(B2:B31,"<0")


I just realized that you said skipping cells that are zero __or_null__.
In that case, you might want:

=sumif(B2:B31,"<0") / (counta(B2:B31) - countif(B2:B31,"=0"))

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculate a 30-day moving average based on the last x number of entries and date


However, so far none of these have worked. More specifically, My moving
average formula will reside on another worksheet and should change
every time I add a new row. I want to avoid a static calculation that I
have to re-reference every time.

Thanks again,

Gimiv


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculate a 30-day moving average based on the last x number of entries and date


gimiv Wrote:
However, so far none of these have worked. More specifically, My moving
average formula will reside on another worksheet and should change
every time I add a new row. I want to avoid a static calculation that I
have to re-reference every time.

Thanks again,

Gimiv


On the sheet with the data (or elsewhere, depends on what you want),
put the following:

D1: Last Date
D2: =DMAX(A:B,"Date",E1:E2)

E1: Value
E2: 0

F1: Date
F2: ="<="&D2

G1: Date
G2: =""&D2-30

H1: 30-Day Average
H2: =DAVERAGE(A:B,"Value",E1:G2)

Then, on the sheet you want to know the 30-Day Average, just reference
this sheet's H2 cell.

Scott


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=558670

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Calculate a 30-day moving average based on the last x number of entries and date

In article ,
gimiv wrote:

However, so far none of these have worked.


1) Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER.

2) Are you receiving an error message or an incorrect result? If the
former, what type of error value are you getting?

More specifically, My moving
average formula will reside on another worksheet and should change
every time I add a new row. I want to avoid a static calculation that I
have to re-reference every time.


For this you can use a dynamic named range. Do you need help with this?
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculate a 30-day moving average based on the last x number of entries and date


For this you can use a dynamic named range. Do you need help with this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again for
your help guys.


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Calculate a 30-day moving average based on the last x number of entries and date

Assuming that Sheet1, Column B, starting at B2, contains the data, try
the following...

1) Define the following dynamic named range:

Insert Name Define

Name: Values

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Ok

Change the references accordingly.

2) Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=AVERAGE(IF(ROW(Values)=LARGE(IF(Values,ROW(Value s)),30),IF(Values,Value
s)))

Hope this helps!

In article ,
gimiv wrote:

For this you can use a dynamic named range. Do you need help with this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again for
your help guys.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Calculate a 30-day moving average based on the last x number of entries and date

Note to the OP:

If you have less than 30 values < 0 you'll get a #NUM! error.

Biff

"Domenic" wrote in message
...
Assuming that Sheet1, Column B, starting at B2, contains the data, try
the following...

1) Define the following dynamic named range:

Insert Name Define

Name: Values

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Ok

Change the references accordingly.

2) Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=AVERAGE(IF(ROW(Values)=LARGE(IF(Values,ROW(Value s)),30),IF(Values,Value
s)))

Hope this helps!

In article ,
gimiv wrote:

For this you can use a dynamic named range. Do you need help with this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again for
your help guys.



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Calculate a 30-day moving average based on the last x number of entries and date

Thanks Biff! Where do I send my cheque... <VBG

In article ,
"Biff" wrote:

Note to the OP:

If you have less than 30 values < 0 you'll get a #NUM! error.

Biff

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculate a 30-day moving average based on the last x number of entries and date


Biff Wrote:
Note to the OP:

If you have less than 30 values < 0 you'll get a #NUM! error.

Biff

"Domenic" wrote in message
...
Assuming that Sheet1, Column B, starting at B2, contains the data,

try
the following...

1) Define the following dynamic named range:

Insert Name Define

Name: Values

Refers to:


=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Ok

Change the references accordingly.

2) Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...


=AVERAGE(IF(ROW(Values)=LARGE(IF(Values,ROW(Value s)),30),IF(Values,Value
s)))

Hope this helps!

In article ,
gimiv wrote:

For this you can use a dynamic named range. Do you need help with

this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again

for
your help guys.


Wow, this worked perfectly. Hate to be a pain, but can you explain how
you went about the logic to achieve this statement or does that just
come with years and years of experience. I mean, to be able to identify
the problem and match it to the right complex formula?


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Calculate a 30-day moving average based on the last x number of entries and date

In article ,
gimiv wrote:

Wow, this worked perfectly. Hate to be a pain, but can you explain how
you went about the logic to achieve this statement or does that just
come with years and years of experience. I mean, to be able to identify
the problem and match it to the right complex formula?


Basically, I watch and learn from others who are more experienced. It's
amazing what one can learn by frequenting these newsgroups, forums,
etc...
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count/Sum data with date entries. KBW Excel Worksheet Functions 6 April 5th 06 07:32 PM
Counting distinct entries based on meeting month & year criteria jennifer Excel Worksheet Functions 3 February 9th 06 01:56 PM
counting date entries by month & year Di Excel Worksheet Functions 7 August 24th 05 08:39 PM
counting specified date entries dave Excel Worksheet Functions 2 November 11th 04 09:28 AM
Count data entries and date problem Gef Excel Worksheet Functions 5 November 4th 04 02:30 PM


All times are GMT +1. The time now is 05:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"