Help! Minimum Excluding zeros across multiple sheets

[kingart69]

Hello all - I want a minimum value EXCLUDING Zeros. This formula:

=SMALL(A1:A10,1+COUNTIF(A1:A10,0))

Would work great if I was working with one worksheet - alas, I am
not. Maybe I'm just messing up syntax, but I simply cannot tweak it
to work across sheets.

Just for refererence - My max formula of course works fine: =MAX
('1:14'!L35)

I have 14 Sheets, each simply numbered, and the cell I want is L35 on
each. how can I get the minimum excluding zeros?

thanks all in advance for your help.

 

[Peo Sjoblom]

Try this


=SMALL('1:14'!L35,1+SUMPRODUCT(COUNTIF(INDIRECT("'"&{1;2;3;4;5;6;7;8;9;10;11;12;13;14}&"'!L35"),0)))



This part

{1;2;3;4;5;6;7;8;9;10;11;12;13;14}

needs to be a list of all the sheets that are included, you can also put the
sheet names in for instance a cell range
like I1:I14 and use



=SMALL('1:14'!L35,1+SUMPRODUCT(COUNTIF(INDIRECT("'"&I1:I14&"'!L35"),0)))


--


Regards,


Peo Sjoblom

 

[Harlan Grove]

Try this

=SMALL('1:14'!L35,1+SUMPRODUCT(COUNTIF(INDIRECT("'"&{1;2;3;4;5;6;7;8;9;10;11;12;13;14}&"'!L35"),0)))

....

Volatile functions unnecessary for literal 3D references. This could
be done with

=SMALL('1:14'!L35,INDEX(FREQUENCY('1:14'!L35,0),1)+1)

Actually, this could be done just using

=SMALL('1:14'!L35,FREQUENCY('1:14'!L35,0)+1)

but the formula returns an array, so not as useful if the SMALL call
were instead part of a bigger formula.

 

[kingart69]

The first one worked GREAT - thank you very much...

I kept getting a REF error when using the one with the range. Was
that supposed to be an 'i' (letter i) in front of the sheet ranges?

Thanks again...

 

[Peo Sjoblom]

Yes I1 to I14, maybe I wasn't the best range to choose
this is better and cannot be misunderstood

H1:H14


Although you might want to look at Harlan's solution, he is correct that
a non volatile formula is to be preferred when possible.

--


Regards,


Peo Sjoblom

 

[kingart69]

Just wanted to say thanks again to both....I ended up using Harlan's
- =SMALL('1:14'!L35,FREQUENCY('1:14'!L35,0)+1) and it works like a
champ. Although I have to admit - I don't necessarily understand that
formula or WHY it works - My experience with arrays is quite limited.
So, I'm glad i asked for help. thanks again, guys!

Todd