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

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

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
>
>

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.

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

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

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

Hope this helps!

In article <(E-Mail Removed)>,
gimiv <(E-Mail Removed)> 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

joeu2...@hotmail.com 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"))

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

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

In article <(E-Mail Removed)>,
gimiv <(E-Mail Removed)> 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?

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