New Formula Column - Sum Function

MShroff · ‎04-10-2023

Hello,

The Sum function in New Formula Column -> Combine uses the formula of col1+col2+..... This does not work when there are cells with missing data. Can this function instead be implemented as sum(col1, col2, ....) to address this? Alternatively, can this be provided as a separate option in New Formula Column?

It's currently confusing in that the name ("Sum") of the command implies the sum(col1, col2, ...) approach but that's not how it's been implemented.

Thanks and regards

Mehul Shroff

Raaed · ‎04-11-2023

/* Import Excel file: num data.xlsx */ 
Data Table( "Sheet1" );
Open(
	"/D:/students/لمى/num data.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
); 
/* Change column formula: sum */ 
Data Table( "Sheet1" ):sum << Set Formula(
	Sum(
		:q1,
		:q2,
		:q3,
		:q4,
		:q5,
		:q6,
		:q7,
		:q8,
		:q9,
		:q10,
		:q11,
		:q12,
		:q13,
		:q14,
		:q15,
		:q16,
		:q17,
		:q18,
		:q19,
		:q20,
		:q21,
		:q22,
		:q23,
		:q24
	)
);

hogi · ‎04-12-2023

@MShroff wrote:
Can this function instead be implemented as sum(col1, col2, ....)

Please not that the current behavior is also a cool feature - I guess in most of the cases a user is just interested in the sum if ALL summands are non-missing; therefore, returning a sum with missing summands could also be misleading.

One can even use the current behavior as a check if all entries in all columns are filled:

if the sum is missing, one of the values was missing.

@MShroff wrote:
Alternatively, can this be provided as a separate option in New Formula Column?

That's a good idea.

By the way - if you just want to "merge" multiple columns to a single one along the idea:
take the entry that is not missing

... one can use New Formula Column/Combine/Maximum.
I use it now and then to combine multiple columns to a single one.