Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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...
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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...





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
minimum value excluding zero mkh Excel Discussion (Misc queries) 4 April 7th 08 02:35 PM
Return the minimum number in a range excluding zero Jive Excel Worksheet Functions 3 November 8th 07 02:41 PM
finding minimum value excluding zero bookman3 Excel Discussion (Misc queries) 6 January 31st 07 04:31 AM
Finding Minimum Value in series, excluding zero values [email protected] Excel Worksheet Functions 5 January 30th 07 09:21 PM
average of several cells excluding the minimum Ashley32 Excel Discussion (Misc queries) 1 March 10th 06 07:30 PM


All times are GMT +1. The time now is 10:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"