# Excluding Zeros in Min formulas

#### cdnqte

##### Board Regular
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,

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

##### MrExcel MVP
cdnqte said:
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

##### MrExcel MVP
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

##### Board Regular
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

##### Well-known Member
If you have no negative numbers in your range, try this:
Book1
ABCD
10
21
32
43
5
6
71
Sheet2

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

##### MrExcel MVP
cdnqte said:
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

##### Board Regular
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!!!

Replies
12
Views
399
Replies
10
Views
237
Replies
4
Views
74
Replies
4
Views
288
Replies
0
Views
144

1,195,672
Messages
6,011,084
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back