Excluding Zeros in Min formulas

[cdnqte]

I am trying to calculate usage based on the highest and lowest number in a given range of data. I need the min to exclude zeros; so if the lowest number is 0 and the second lowest is 15, I need the Min formula to pull the number 15.

I am sure this is a simple process but I just can seem to get it!

Any help would be appreciated!

Thanks,

 

[Aladin Akyurek]

I am trying to calculate usage based on the highest and lowest number in a given range of data. I need the min to exclude zeros; so if the lowest number is 0 and the second lowest is 15, I need the Min formula to pull the number 15.

I am sure this is a simple process but I just can seem to get it!

Any help would be appreciated!

Thanks,

=MIN(IF(Range,Range))

which you need to confirm with control+shift+enter (not just with enter).

 

[Tazguy37]

Try this (array enter with control-shift-enter):

=MIN(IF(A1:A10>0,A1:A10))

and adjust the range to suit. Hope that helps!

 

[cdnqte]

This is what I have, which looks the same as what you have posted:

=MIN(IF($A$3:$A$64925=A9,$K$3:$K$64925))

However, I need to take the second lowest number if the lowest is zero, this doesn't do that. So, I don't need the second lowest number all of the time, only if the lowest is 0. If the lowest is not 0 then it is fine.

Does that make any sense?

 

[Barry Katcher]

ADVERTISEMENT

If you have no negative numbers in your range, try this:

Book1
	A	B	C	D
1	0
2	1
3	2
4	3
5
6
7	1
Sheet2

The formula in A7 is =IF(MIN(A1:A4)=0,SMALL(A1:A4,2),MIN(A1:A4))

 

[Aladin Akyurek]

This is what I have, which looks the same as what you have posted:

=MIN(IF($A$3:$A$64925=A9,$K$3:$K$64925))

However, I need to take the second lowest number if the lowest is zero, this doesn't do that. So, I don't need the second lowest number all of the time, only if the lowest is 0. If the lowest is not 0 then it is fine.

Does that make any sense?

Look the same but they are not...

Why do you refer to A9 in $A$3:$A$64925? Try to use a different cell for the condition/criterion e.g., L3, then invoke:

=MIN(IF(($A$3:$A$64925=L3)*$K$3:$K$10,$K$3:$K$64925,""))

followed by control+shift+enter.

 

[cdnqte]

The reference to A9 because the sheet is laid out as follows:

From our buisness system I have puilled service history by unit and then sorted it by unit number. Within that data there are about 50 unit numbers that may have 10 work orders against each one. So, using conditional formatting, I have all of the duplicate data hidden (make, model, s/n) with the exception of the first record. Each individual service work order is displayed though so everything appears grouped by unit number. From the service data, I need to calculate the usage based on highest and lowest meter readings on each unit and show it with the general data as usage. For this reason, I have to first identify all of the readings against an individual unit, check to find the lowest number and if it is zero, then find the second lowest number. If it were one unit the previous formula would work, but because there is multiple criterion, i am fumbling with this formula.

I tried to include a copy of the sheet hoping that seeing it will make more sense but I am having trouble getting the sheet into my post. I think I am just struggling with everything today!

Hopefully this makes some sense and I look forward to ANY help!!!