Minimum non-zero within array formula

Hi all,

I have a load of (multiply duplicated) categories (in col G) and values for them (in col P). Each category has more than one value, but they should be in roughly the same ball-park. I want to rank the categories in a pivot table, by value, not alphabetically. So I wrote this formula:

{=MAX(P$2:P$10*(J$2:J$10=2)*(G$2:G$10=G2))}
(I also am only interested in entries for which there is a "2" in column J as shown)

This takes the category, finds all instances of the same category in col G and returns the max of all of them - so each row now has a "max value for this category" field.

This is all great, but what would work a lot better would be grouping them by the minimum value in the column, but of course when I do this I get a zero (from any one of the hundreds of non-matches).

I tend to get non-zero mins using small and countif but can't conceive how I would squeeze that into this formula.

Any help much appreciated.

CC

(x,y) to all intents and purposes is the same as (x*y) but using , as opposed to * can help in some cases where looking to sum a range that may not always contain numerics...

say A1:A3 equals A,B,A and B1:B3 = 1,2,X

This would generate an error:

=SUMPRODUCT((A1:A3="A")*(B1:B3))

Whereas this would not.

=SUMPRODUCT((A1:A3="A"),B1:B3)

Think of it like SUM(B1:B3) would = 3 but B1+B2+B3 would return error...

Does that help in anyway ?

See Bob's page on Sumproduct -- although yours is not a sumproduct you can see the reasoning behind the way calcs are structured.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The MIN will only fire against those rows/values in P2:P10 where the prior conditions have been met (in the same row). So if any value in J2:J10 <> 2 the associated value in P2:P10 is ignored in the MIN test -- the same is true if any value in G2:G10 <> G2 or any value in P2:P10 = 0 (using Bob's original). The MIN therefore is only the MIN of those values in P where the tests in J, G & P have been met.

Cheeky,

The IF in =IF(arraycondition, array) returns the corresponding value in array when arraycondition is met, and FALSE otherwise. So it might return, for example,

{FALSE, 0.1, FALSE, FALSE, 5, 12.2, ...}

Wrapping that expression with certain functions that ignore logical values (e.g., MIN, MAX, AVERAGE), and it evaluates only the numbers.

If you watch the formula evaluate using the Auditing toolbar, it will become crystal clear.