Multi-row formula - if statements and averages

Hello,

I have a dataset that is sorted in ascending order by account and by KM.

If there is a null for Year, I'd like the output to be the average between the last entered year before the null, the the next entered year after the null(s). If a new account code does not have any Year entires at the beginning, the output should take the first year entry that occurs.

Please see below an example of my problem and what the desired output is. Can anyone suggest anything? I have attached the below in spreadsheet form.

Thank you!!

Bianca

Bianca Question_Alteryx Community.xlsx

Datasets

Accepted answers

estherb47

Hi @bwat2435

You know you've posted a challenging question when there's a long delay in response.

I don't know if this is the best or most efficient, but it works.

Basically, a long IF/THEN/ELSEIF statement in the first Multi Row formula tool creates a field called column that will be used to tag which rows should be averaged. There were a few challenges, such as figuring out the first value within an account if it's null, and how to deal with numbers that are used in 2 separate averages - where a year is flanked by null above and below (chose to create a field with the last number from the previous group and the first number from the next).

Text to columns creates new rows for the overlap, and a filter removes rows that won't be used in the Average calculations. Then another Multi Row formula tool to determine a grouping for each set of 1 through n on the column field.

Summarize creates the average calculations. That's joined back to the original dataset, and a formula tool replaces nulls in the Year column with the appropriate average value.

Let me know if that helps you.