Challenge #3: Running Averages

[kat]

Handled this with a macro, but would've loved to turn it into a single macro... The awesome macro logic is from CReW macros!

Spoiler

Update after looking at the solution: Went way complicated... Forgot about that simple transpose trick 🙂 

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

[Marek]

Hi All,

Through trial and error I finally came up to the solution that surprised me a bit :).

The pattern is that we need to apply two (similar) calculations to a few sets of data (grouped by HP Category).

After cleaning NULL values I applied multi-row formula tool 12 times and it worked fine, 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.

[ChrisB]

my solution

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

[jzondervan]

Found my solution. This link helped me a lot!

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

[willstom1]

Here is my solution:

Spoiler

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

[yavdaanalytics]

Spoiler

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

[MattDuncan]

Not as elegant as others solutions but it got the result I was looking for!

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

[bblock]

Mine is slightly different than the solution provided.  I used an extra column called avg_name to determine whether it is a 3mo or 6 mo and then did a union before my crosstab.

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

[OllieClarke]

Here's my solution

Spoiler

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

[vinutha]

Interesting one. Used a CrossTab, 2 Summarize and a Transpose.

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