Calculating AVERAGE, based on multiple rules from other columns

[Badi_Complain]

Good day everyone!

I’m looking to calculate all the different averages for a Column’s subsets, based on 3 rules for other columns. In Excel I could easily achieve this via “AVERAGEIFS” function, but I can’t seem to find an alternative in KNIME. In the dummy data, it could be easily achieved using “GroupBy” and manually setting up rules - but this would get exponentially harder with each added option within the columns.

Below is a set of dummy data. I would like to get the average Income for each different subset. In Excel it would look something like: AVERAGEIFS(Salary;Type column;this row’s Type;Country column;this row’s Country;F/M column;this row’s F/M)

[JPollet]

Hi @Badi_Complain and welcome here,
You can try with Pivot node perhaps (Group on nothing, pivot on type country and manual aggregate with mean). I don’t really know if it is the exact Excel equivalent but pivoting is a powerful method.

Best,
Joel

[Badi_Complain]

@JPollet thank you for the assistance, I was able to sort it out using the Pivot:

• added all necessary Columns to the Groups (in the dummy data: Type, Country, F/M)
• Pivot by any other data, didn’t have affect on my outcome
• Aggregated by the Column that has the numbers I wanted to use for calculation (in the dummy data: Income and aggregation method “Mean”)

Execute this node, and use the middle output port, which is the “Group Totals”.

What I’ve found to be useful addition is a “Missing Value” node, to ensure the success of later calculations

[JPollet]

Fine !
This is not the exact equivalent of averageifs but solves some problems.

Best,
Joel