minimum along rows

H

hochungwa

Hi,

Is it possible to sum up the minimums of each row in one line commmand?

E.g.

A B
1 5 6
2 2 0
3 1 3

Ans: 5 + 0 + 1

I've tried

{=sum(min(a1:a3,b1:b3))}

but it doesn't work.

Thanks
 
B

Bruno Campanini

Hi,
Is it possible to sum up the minimums of each row in one line commmand?
E.g.
A B
1 5 6
2 2 0
3 1 3
Ans: 5 + 0 + 1
I've tried
{=sum(min(a1:a3,b1:b3))}
but it doesn't work.
Thanks
Click to expand...

You have 2 numbers in column A and one in B.
Why Ans: 5 + 0 + 1?
Shouldn't be 1 + 0 + 1?

Bruno
 
H

hochungwa

Sorry for misleading, the first column indicate the row number.
Simply, I rewrite it as follows:

A B
5 6
2 0
1 3
: :

Expected result: 5 + 0 + 1 + ...

Bruno Campanini 寫é“：
 
H

Harlan Grove

[email protected] wrote...
Is it possible to sum up the minimums of each row in one line commmand?

E.g.

A B
1 5 6
2 2 0
3 1 3

Ans: 5 + 0 + 1
Click to expand...
....

The simplest way to handle your example data (or any 2-column
comparison) would be to use an array formula like

=SUM(IF(A1:A3<B1:B3,A1:A3,B1:B3))

Getting exotic, if you wanted the sum of the minimum values from each
row in the multiple (>2) column range Rng, you could use a formula like

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng))
 
B

Bruno Campanini

Harlan Grove said:
The simplest way to handle your example data (or any 2-column
comparison) would be to use an array formula like

=SUM(IF(A1:A3<B1:B3,A1:A3,B1:B3))

Getting exotic, if you wanted the sum of the minimum values from each
row in the multiple (>2) column range Rng, you could use a formula like

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng))
Click to expand...

The second formula doesn't work with me; I get #DIV/0!
What's wrong?

Bruno
 
H

Harlan Grove

Bruno Campanini wrote...
The second formula doesn't work with me; I get #DIV/0!
What's wrong?
Click to expand...

If you have any blank cells in Rng, you'd need to change the formula to

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng&""))
 
B

Bruno Campanini

Harlan Grove said:
Bruno Campanini wrote...

If you have any blank cells in Rng, you'd need to change the formula to

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng&""))
Click to expand...

I didn't have any blank cells in Rng.
But now the new formula works fine.

Many thanks
Bruno
 
R

Robert_Steel

Harlan
I have tried to work out how your formula works and am struggling
=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng&""))
Click to expand...

So far I think it creates an array the size of the range that identifies
(1 or 0) the lowest number (or if n multiples uses a fraction 1/n)
This is then Sumproducted with the original array.

Please could you explain how the two COUNTIF(OFFSET()) functions are
constructed and how they work

many thanks
RES
 
H

Harlan Grove

[email protected] wrote...
....
So far I think it creates an array the size of the range that identifies
(1 or 0) the lowest number (or if n multiples uses a fraction 1/n)
This is then Sumproducted with the original array.

Please could you explain how the two COUNTIF(OFFSET()) functions are
constructed and how they work
Click to expand...

The key is the OFFSET calls. They return what are effectively arrays of
range references, in this case arrays of references to each row in Rng.
That's due to the 2nd argument which evaluates to an array of serial
numbers from 0 to N-1 where N is the number of rows in Rng. COUNTIF,
SUMIF, N and T seem to be the only functions that can handle such
arrays. COUNTIF and SUMIF return array results as if their (scalar)
criteria argument had been applied to each range in the array of range
references.

The first COUNTIF call returns an array of nonnegative numbers in which
zero corresponds to the max value (the value for which there are no
values strictly larger). The equality test after boolean to numeric
conversion then has ones for max values and zeros for smaller values.
The second COUNTIF call in the denominator returns the number of max
values.

As for the interaction of OFFSET-generated arrays of ranges and an
array criteria argument to COUNTIF, it seems to work when the arrays
conform. The return value is the same size as the criteria argument and
is equivalent to applying each separate range to the entire criteria
array.
 
R

Robert_Steel

Harlan
thanks for taking the time to explain this.
there are a few new elements for me to use. My initial efforts to solve
the op's question had lead me down OFFSET with an array call. However, as
you point out, MIN does not work.
Therefore, I now understand the use of the two COUNTIFs to mimic this
action.
I did solve the problem with a brute force UDF but I do like the idea of
using worksheet functions when I can.
 
Top