• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the minimum every line, and summarize.

Peter Bartholomew

Well-Known Member
Rather than 'too clever by half' formulas, why not support my request for a function that will divide a two-dimensional array into a collection of rows or of columns prior to any aggregation?

In this case
= MIN( BYROW( ArrayName ) )
would give a column array containing the minima.

That array could simply be embedded within the SUM to read
= SUM( MIN( BYROW( ArrayName ) ) )

This will get more important with dynamic arrays because the usual strategy of using relative references will need to accommodate dynamic spill regions.

In any case, it is not mathematically sound to request a summation over an array A(I,J) without specifying which index to sum over. In my experience, the Excel assumption that you want both is the least useful.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/36151087-aggregate-2d-arrays-by-row-or-by-column-especiall

Other examples might include
= AND( BYROW( BooleanArray ) )
to test for a record that matches a set of criteria or
= SUM( BYCOLUMN( PeriodicData ) )
to sum financial data over a variable period.
 

Peter Bartholomew

Well-Known Member
I have only just seen the revamped website so finished up browsing some old material. With modern Dynamic Arrays I would hope to see array formulas taught as basic Excel rather than as esoteric solutions of last resort.

Since the solution I suggested in my February post is not going to happen anytime soon, a solution to the problem as it stood in 2016
= SUM(A, B,-Δ) / 2
where 'Δ' (Greek capital delta) refers to
= ABS(A-B)
 
Top