2 Methods to Find the Minimum Number in a Range Excluding Zeros in Excel Worksheet

Sometimes there is a need to find the minimum number in a range excluding zeros. In this article, we will introduce two methods to finish this task.

When the worksheet contains a bunch of data and information, it is impossible for you to find the minimum number. And finding the number will cost you a lot of time and energy. In order to help you finish this task quickly, we have found the two methods. Now follow the steps below and see how the two methods take effect.

Method 1: Use Excel Functions

In this method, you will need to use Excel functions to finish this task.

  1. Click a blank cell in the worksheet where you need to get the result.
  2. And then input this formula into this cell:

=MIN(IF(B2:G4<>0,B2:G4))

In this formula, the B2:G4 is the target range. You need to change the range according to your need.

  1. After that, press the shortcut keys “Ctrl +Shift +Enter” on the keyboard. Therefore, this formula will be an array formula. And the result will appear in the cell immediately.Result

In addition, you can also use another formula for this task. You need to input this formula into a blank cell:

=SMALL(B2:G4,COUNTIF($B$2:$G$4,0)+1)

And then press the button “Enter” on the keyboard. You can also get the result. In this formula, we will count the number of zeros in this range and then use the SMALL function to get the result.Another Formula

You can use one of the two formulas to finish this task. Both of them are very easy to use.

Method 2: Use Excel VBA

To find the minimum number in a range, you can also use Excel VBA.

  1. Press the shortcut keys “Aly +F11” on the keyboard to open the Visual Basic editor.
  2. And then click the tab “Insert” in the toolbar.
  3. After that, choose the option “Module” in the drop-down list.Insert Module
  4. In this step, copy the following codes into the new module:
Sub FindMinimumNum()
  Dim objRange As Range, nTargetNumber As Integer

  '  Initialization
  nTargetNumber = Range("B2").Value

  For Each objRange In Range("B2:G4")
    If objRange.Value < nTargetNumber And objRange.Value <> 0 Then
      nTargetNumber = objRange
    End If
  Next

  MsgBox "The minimum number in this range excluding zeros is " & nTargetNumber
End Sub

In this macro, you need to change the target range according to the actual worksheet. We will get the result in a pop-up window. If you need to record the result in other form, such as returning the result in a blank cell, you can also modify codes by yourself.

  1. In this step, run this macro. You can press the button “F5” on the keyboard to run this macro. And then you will see this window immediately.

In the message, you will get the result.Window

From the above analysis, you can see that using VBA macro is also very easy.

A Comparison of the Two Methods

In order to help you better understand those two methods, we have this table below.

Comparison

Use Excel Functions

Use Excel VBA

Advantages

1.      There are two different formulas available. You can choose to use the one that you are familiar with.

2.      If you don’t know how to use Excel VBA, you can use formulas.

1.      Just by one click, you can get the result easily.

2.      You can modify the codes to finish other tasks according to your requirement.

Disadvantages

1.      There are function combinations in the formulas, which will make you hard to understand.

2.      The results in the target cell are returned by formulas. You still need to copy and paste as values if you have other needs.

1.      If you are not familiar with Excel VBA, you will probably meet with errors when using this method.

2.      Most of the time, using VBA codes will complicate the task.

The next time you have such requirement, you will know which method is the most suitable one.

Contact a Data Recovery Company for Help

Excel corruption is unavoidable to everyone. Once such accident happens, you need to repair it as soon as possible. If you have no knowledge about the data recovery, don’t try to fix it by yourself. You can turn to a data recovery company for help. Besides, you can also prepare a recovery tool at hand. Once accident happens, you can use this tool to repair damaged Excel immediately. Thus, your data and information will be safe all the time.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx data problem and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.