Challenge #3: Running Averages

[Suzu]

Here is my solution.

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[roblen]

I decided to use a slightly different approach which some people may find useful (maybe someone has already done it :-)) - Instead of taking the closes valid row in the Multi-Row Formula tool for Average calculation, I used IF statement to ie. calculate Feb average based on Jan and Feb only for 2009 (initial year). That's why some averages for 2009 are slightly different. I did something similar at work because of business requirements.

Please see the formula for r6mo below. I also used ToNumber() to handle nulls.

IF [Month]=1  AND [Year]=2009
THEN [Value]
ELSEIF [Month]=2  AND [Year]=2009 THEN Average([Row-1:Value],[Value])
ELSEIF [Month]=3  AND [Year]=2009 THEN Average([Row-2:Value],[Row-1:Value],[Value])
ELSEIF [Month]=4  AND [Year]=2009 THEN Average([Row-3:Value],[Row-2:Value],[Row-1:Value],[Value])
ELSEIF [Month]=5  AND [Year]=2009 THEN Average([Row-4:Value],[Row-3:Value],[Row-2:Value],[Row-1:Value],[Value])
ELSE Average(ToNumber([Row-5:Value]),ToNumber([Row-4:Value]),ToNumber([Row-3:Value]),ToNumber([Row-2:Value]),ToNumber([Row-1:Value]),ToNumber([Value]))
ENDIF

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[datamonkey]

My solution - I'm assuming the issue with the 6-mo averages in the target output is a genuine bug based on the other messages here

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[jbranaum]

Here is my solution.

Funny enough I started out on this one with about 20 multirow formulas, one for each column doing a 3 month calc, and again for each doing a 6 month calc.  Then I stumbled upon Transpose which made it way less complicated for me.  Definitely took me a few trys to get this one.....

2 calculations instead of 20.........

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[Patrick007]

My challenge #3 solution.

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[ewelch531]

I learned a lot on this one...

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[TonyM]

transpose, multi-row for 3 and 6 month moving averages, crosstab back

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[BPurcell2]

I got pretty OCD here, I opened a can of worms and used the CReW Test Macro, requiring me to change a few field names, account for rounding errors, and further examine the 6 month error others have identified.  I broke open the Test macro to identify all fields that weren't within 1%, and the were all of the 6 month average fields for 2009 Months 1-5.

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[alejandini]

Learned a good amount with this one as well

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

[SamDesk]

Spoiler

Sam 🙂

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.