Excel average of values in row if cell value contains column names-CodePudding

I have a table:

       A              B               C        D        E
       Identifier     Reference       DK       NO       SE
1      DK-NO          5               20       30       40
2      DK-SE          15              20       30       40
3      DK-NO-SE       20              20       30       40

Now, what I want to do is calculate the average difference between the of the values identified in column "Identifier" and the value in "Reference", i.e., the first value being: AVERAGE(C1-B1;D1-B1) = AVERAGE(15;25) = 20, second row being AVERAGE(C2-B2;E2-B2) = AVERAGE(5,15) = 10 the third AVERAGE(C3-B3;D3-B3;E3-B3;) = AVERAGE(0;10;20) = 10, and so on.

Preferably a solution that can be used in power query.

CodePudding user response：

Here is how you can do it using Power Query.

You use List.Accumulate to gather the relevant values; then average them and subtract the Reference.

Please read the code comments and follow the Applied Steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

//set the data types
    colTypes = List.Zip({Table.ColumnNames(Source), {Text.Type} & List.Repeat({Int64.Type},Table.ColumnCount(Source)-1)}),
    #"Changed Type" = Table.TransformColumnTypes(Source, colTypes),

//add Index column to identify the relevant (row)
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//calculate the averages using List.Accumulate to gather the factors
    #"Added Custom" = Table.AddColumn(#"Added Index", "Average Diffs", each let 
            cols = Text.Split([Identifier],"-"),
            vals = List.Accumulate(cols,
                {},
                (state,current)=> state & { Record.Field(#"Added Index"{[Index]},current)})
        in 
            List.Average(vals) - [Reference]),

//Delete the Index Column
    result = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    result

If you have Windows Excel with XMATCH you could also use this formula in a column added to the same table: (note that you do need to refer to the table name in certain parts of the formula)

=AVERAGE(INDEX(Table47[@],, XMATCH(FILTERXML("<t><s>" & SUBSTITUTE([@Identifier],"-","</s><s>") & "</s></t>","//s"),Table47[#Headers])))-[@Reference]

CodePudding user response：

You can use this formula =AVERAGE(INDIRECT(ADDRESS(ROW(B3),MATCH(LEFT(B3,2),$2:$2,0)))-C3,INDIRECT(ADDRESS(ROW(B3),MATCH(RIGHT(B3,2),$2:$2,0)))-C3)

CodePudding user response：

You have the PowerQuery solution (@Ron Rosenfeld).

For a spreadsheet formula, you can use LET() to create a mask of 1's and 0's, and use this to filter the columns and create the average

eg for cell F2, this formula:

=LET(mask,IF(ISERROR(FIND(C$1:E$1,$A2)),0,1),(SUMPRODUCT(mask,C2:E2)/SUM(mask))-$B2)

Should allow you to add extra columns, and new 2-character codes.