Help! Minimum Excluding zeros across multiple sheets
 

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.

Help! Minimum Excluding zeros across multiple sheets
 

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

wrote in message
...
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.

Help! Minimum Excluding zeros across multiple sheets
 

"Peo Sjoblom" wrote...
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.

Help! Minimum Excluding zeros across multiple sheets
 

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...

Help! Minimum Excluding zeros across multiple sheets
 

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

wrote in message
...
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...

Help! Minimum Excluding zeros across multiple sheets
 

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