Friday, March 30, 2018

Excel and L1-norms

In an Excel spreadsheet I am studying (actually generating an optimization model from the formulas in the spreadsheet) I see the following construct:

=SUMIF(X,">0")-SUMIF(X,"<0")
where X is a range. This looks like a strange construct. It is meant to implement the sum of absolute values\[||x||_1=\sum_i |x_i|\] also known as the L1-norm. The L2-norm \[||x||_2 = \sqrt{\sum_i x_i^2}\] is easy to do in Excel:

=SQRT(SUMSQ(X))

Strangely, there is no obvious (that is, obvious to me) formula to do the L1-norm. Excel is a large program, with tons of options: there is almost always some workaround available. In [1] a list is provided.

The  L\(\infty\)-norm defined by\[||x||_{\infty}=\max_i |x_i|\]has similar issues: there is no built-in formula for this.

Here is summary:



The =SUM(ABS(X)) and =MAX(ABS(X)) formulas do not give what we want. It is somewhat surprising that =SUMPRODUCT(ABS(X)) is doing the right thing (although the name of the function is now misleading: we dropped the PRODUCT part of the functionality). The array formulas [2] seem to be most intuitive.

References