Finding Minimum Excluding the zero

[thong127]

Hello All,

I want to find the Minimum of my Humidity Reading But I want to exclude the zero in my computation.

How can I do that. Please find attached file that Im working on.

Thank you.

[Olly]

In C25:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy to D25.

[jasoncw]

Try this array formula:

=MIN(IF(C1:C24>0,C1:C24,9999))

I used 9999 just to have a number that will be larger than your minimum above zero. Since this is an array formula, you have to confirm it with Ctrl+Shift+Enter.

[AlKey]

Here is another way to get min and max
Enter formula in C25 and drag formula to D25 and then down

Please Login or Register  to view this content.

v	A	B	C	D
1	IP E4/F3 Hum	10/10/2016 20:00	35.7	21.6
2	IP E4/F3 Hum	10/10/2016 20:05	35.8	21.6
3	IP E4/F3 Hum	10/10/2016 20:10	35.9	0
4	IP E4/F3 Hum	10/10/2016 20:15	35.9	0
5	IP E4/F3 Hum	10/10/2016 20:20	35.9	0
6	IP E4/F3 Hum	10/10/2016 20:25	36	21.4
7	IP E4/F3 Hum	10/10/2016 20:30	36	21.3
8	IP E4/F3 Hum	10/10/2016 20:35	0	21.3
9	IP E4/F3 Hum	10/10/2016 20:40	0	21.2
10	IP E4/F3 Hum	10/10/2016 20:45	0	21.2
11	IP E4/F3 Hum	10/10/2016 20:50	36.2	21.2
12	IP E4/F3 Hum	10/10/2016 20:55	36.2	21.2
13	IP E4/F3 Hum	10/10/2016 21:00	36.3	21.1
14	IP E4/F3 Hum	10/10/2016 21:05	36.3	21.1
15	IP E4/F3 Hum	10/10/2016 21:10	36.3	21
16	IP E4/F3 Hum	10/10/2016 21:15	36.4	21
17	IP E4/F3 Hum	10/10/2016 21:20	36.4	21
18	IP E4/F3 Hum	10/10/2016 21:25	36.4	20.9
19	IP E4/F3 Hum	10/10/2016 21:30	36.4	20.9
20	IP E4/F3 Hum	10/10/2016 21:35	0	20.8
21	IP E4/F3 Hum	10/10/2016 21:40	36.5	0
22	IP E4/F3 Hum	10/10/2016 21:45	36.5	20.8
23	IP E4/F3 Hum	10/10/2016 21:50	36.6	20.7
24	IP E4/F3 Hum	10/10/2016 21:55	36.5	20.8
25		Min	35.7	20.7
26		Max	36.6	21.6

[thong127]

Hi AlKey,

I would appreciate if you can Explain to me your formula. What is the used of 15?

Thank you so much.

[AlKey]

Within AGGREGATE there are 19 functions. Function 15 is SMALL and 14 is LARGE. SMALL and LARGE can serve as alternatives for MIN and MAX respectively. I used 15-(ROWS(C$1:C1)-1) to subtract 0 and than 1. By dragging formula down ROW() part that will change 15 to 14 or changing from MIN to MAX.

[thong127]

Hi Alkey,

Thanks you for the explanation...And

Thank you to you to you all guys for your formulas. but I prefer AlKey because I need to be an automatic Because I have 13 worksheets.

Thanks everyone!

[Tony Valko]

The MIN(IF / MAX(IF formulas are more efficient.

[thong127]

Hi Tony,

Thank you for Chiming in. In my situation How can you please teach me on how to use the Min IF and Max If formulas.

Thank you.

[Tony Valko]

Not sure what you're asking.

The array formula in post #2 will find the min value excluding 0s.

Enter it in C25 and copy it across to D25.

It is faster to calculate (more efficient) compared to the AGGREGATE version.

[thong127]

Hi Tony,

Thank you for pointing me. Do I need to use Ctrl SHift Enter every time if Im going to use it to my 13 worksheets?

Thanks.

[Tony Valko]

Yes, array formulas are entered with the key combination of CTRL, SHIFT, ENTER.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

[thong127]

Hi Tony,

Thank you so much for the info. I will take a note on that.

[thong127]

Hi Alkey,

Sorry I forgot to ask you, What is the function of 6 in the equation?

Thanks.

[AlKey]

Hi Alkey,

Sorry I forgot to ask you, What is the function of 6 in the equation?

Thanks.

Sorry, I forgot to mention this: 6 is to ignore error values.

[thong127]

Thanks AlKey,

[thong127]

Hi AlKey,

Thank you and COrrect me If Im wrong

=AGGREGATE(15-(ROWS(C$1:C1)-1),6,C$1:C$24/(C$1:C$24<>0),1) for Minimum
=AGGREGATE(14-(ROWS(C$1:C1)-1),6,C$1:C$24/(C$1:C$24<>0),1) for Maximum

Thanks.

[AlKey]

Hi AlKey,

Thank you and COrrect me If Im wrong

=AGGREGATE(15-(ROWS(C$1:C1)-1),6,C$1:C$24/(C$1:C$24<>0),1) for Minimum
=AGGREGATE(14-(ROWS(C$1:C1)-1),6,C$1:C$24/(C$1:C$24<>0),1) for Maximum

Thanks.

No. You only need one formula. It will do minimum and maximum. When you enter formula in C25 formula will return Min and when you drag formula down it will return Max. See post #6

or if you want to use two formulas

=AGGREGATE(15,6,C$1:C$24/(C$1:C$24<>0),1) for Minimum
=AGGREGATE(14,6,C$1:C$24/(C$1:C$24<>0),1) for Maximum

[Tony Valko]

You're welcome. Thanks for the feedback!