Help! Minimum Excluding zeros across multiple sheets

  • Thread starter Thread starter kingart69
  • Start date Start date
K

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.
 
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
 
Peo Sjoblom said:
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)))
Click to expand...
....

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