Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minimum value excluding zero | Excel Discussion (Misc queries) | |||
Return the minimum number in a range excluding zero | Excel Worksheet Functions | |||
finding minimum value excluding zero | Excel Discussion (Misc queries) | |||
Finding Minimum Value in series, excluding zero values | Excel Worksheet Functions | |||
average of several cells excluding the minimum | Excel Discussion (Misc queries) |