Input has Null and 0 Values in column - Help with the Column update

Question

I have columns with NULL and 0 values, and I need to Total Marks to reflect the Marks as below. Note 1) there will either Maths or Science marks (never both) and 2) If the status is Deleted it will reflect as 0 in the input but I want the total to reflect

Input:

StatusMaths Science Total MarksGood100[Null][Null]Good100[Null][Null]Good[Null]250[Null]Good200[Null][Null]Good[Null]450[Null]Good[Null]500[Null]Good[Null]600[Null]Good[Null]750[Null]Good[Null]100[Null]Good0500500Good7500750Good6000600Deleted75000Deleted05000Good0500500Good7500750Good6000600

Output:

StatusMaths Science Total MarksGood100[Null]100Good100[Null]100Good[Null]250250Good200[Null]200Good[Null]450450Good[Null]500500Good[Null]600600Good[Null]750750Good[Null]100100Good0500500Good7500750Good6000600Deleted7500750Deleted0500500Good0500500Good7500750Good6000600

BS_THE_ANALYST · Accepted Answer

Quite an interesting one @EmilAlteryx01.

If you just want to add the columns across the rows, it will return null if there's a null in there.

However, the summarize tool behaves as you would expect if you have nulls (it will ignore them and perform the addition): if you were to pivot your data and then summarize. I think the same holds true with pivoting your data (transpose) and then cross-tabbing it; I'd use a record ID before this transformation, though. Note: these routes are dynamic which accounts for more columns needing to be added overtime.

If you didn't want to pivot your data (nor did you want to replace your null values with 0s) you could use this formula below: (this seems to handle nulls which is interesting)

Simply taking the average and then adjusting it by multiplying it by the number of columns used. Note: this can still be dynamic, but requires the use of a batch macro to make it dynamic.

there's many routes to tackle this. I normally go for the pivoting option but this could be problematic for big data. It just depends on your situation.

All the best,

BS