Help! Fill in blanks column by averaging left and right values

Question

Hi All,

New to the community!

Hoping I could get some help on the below problem.

I'm trying to fill in the blank cells in the below illustrative excel.

I want to fill in the cells using a weighted average of the "before" and "after" monthly data.

E.g., For Item 1 -

Month 3 = 50% * Month 2 + 50% * Month 5

Month 4 = 50% * month 3 + 50% * Month 5

Similar for Item 2, etc. except there are different "blank" time periods for each item.

Example.xlsx

atcodedog05 · Accepted Answer

Hi @AlteryxUser32

Here is how you can do it. Since the next value is not always in the next row. We need to down to up fill.

Workflow:

1. Using record id to set row id. Unique key for row.

2. Using transpose tool to convert months columns to rows.

3. Sorting the months on descending. Since only top to down fill is possible we need to reverse order to mimic down to top.

4. Using multi-row formula tool doing next value fill.

5. Join back using join tool.

6. Using multi-row formula to calculate if blank avg of prev value and next value.

7. Using crosstab to bring it back to table.

8. Using dynamic rename to fix the names.

If this not the expected output please provide the expected output so that we can work towards it.

Hope this helps : )

danilang · Accepted Answer

Hi @AlteryxUser32

A clever solution from @atcodedog05.  It does indeed allocation the  difference in a geometric series as you stated.  1/2 the difference is allocated to the first null, 1/4 to the second null, 1/8 to the third, etc.  If you want a different distribution, you need to take a different approach.

This workflow calculates the number of missing values and the range to be allocated between them.  The final Multi-Row tool is where you can determine how you'd like them allocated.  The data going into this tool contains

1) the total amount to be distributed to each missing value,

2) the number of missing values in this gap

3) the ordinal value of each missing value, 1st, 2nd, 3rd, etc

Using these 3 values you can distribute the missing values in almost any way imaginable.  I've done it linearly, so the missing values are evenly distributed.

To get the geometric distribution as in @atcodedog05's solution, replace the final formula with

if isnull([Value]) then
   [Row-1:NewValue]+[BracketingDiff]/(pow(2,[ConsecutiveBlanks]))
else
   [Value]
Endif

Dan