Here are 20 examples of how to use the MIN function in Excel:
1. Basic MIN Function
Find the smallest value in a range:
=MIN(A1:A10)
This returns the smallest value in cells A1 to A10.
2. MIN with Individual Cells
Find the smallest value among individual cells:
=MIN(A1, B1, C1)
This returns the smallest value among A1, B1, and C1.
3. MIN with Mixed Ranges and Cells
Combine ranges and individual cells:
=MIN(A1:A10, B1, C1)
This returns the smallest value in A1:A10, B1, and C1.
4. MIN with Negative Numbers
Find the smallest value including negative numbers:
=MIN(A1:A10)
If A1:A10 contains -5, 0, 10, the result is -5.
5. MIN with Blank Cells
Ignore blank cells:
=MIN(A1:A10)
Blank cells in A1:A10 are ignored.
6. MIN with Text
Ignore text in the range:
=MIN(A1:A10)
If A1:A10 contains numbers and text, only numbers are considered.
7. MIN with Logical Values
Ignore logical values (TRUE/FALSE):
=MIN(A1:A10)
Logical values in A1:A10 are ignored.
8. MIN with Zero
Find the smallest value including zero:
=MIN(A1:A10)
If A1:A10 contains 0, 5, 10, the result is 0.
9. MIN with Dates
Find the earliest date:
=MIN(A1:A10)
If A1:A10 contains dates, the earliest date is returned.
10. MIN with Time
Find the earliest time:
=MIN(A1:A10)
If A1:A10 contains times, the earliest time is returned.
11. MIN with Conditional Formatting
Use MIN in conditional formatting to highlight the smallest value in a range.
12. MIN with IF
Find the smallest value based on a condition:
=MIN(IF(A1:A10>5, A1:A10))
This returns the smallest value greater than 5 in A1:A10.
13. MIN with Array Formula
Find the smallest value in multiple ranges:
=MIN(A1:A10, B1:B10)
This returns the smallest value in both A1:A10 and B1:B10.
14. MIN with Named Range
Use a named range:
=MIN(MyRange)
Where MyRange refers to A1:A10.
15. MIN with Filtered Data
Find the smallest value in filtered data using SUBTOTAL:
=SUBTOTAL(105, A1:A10)
This ignores hidden rows in filtered data.
16. MIN with Multiple Criteria
Use MIN with IF and multiple criteria:
=MIN(IF((A1:A10>5)*(B1:B10<10), A1:A10))
This returns the smallest value in A1:A10 where A1:A10 > 5 and B1:B10 < 10.
17. MIN with Error Values
Ignore error values:
=MIN(IF(ISNUMBER(A1:A10), A1:A10))
This ignores errors like #DIV/0! in A1:A10.
18. MIN with Non-Adjacent Ranges
Find the smallest value in non-adjacent ranges:
=MIN(A1:A10, C1:C10, E1:E10)
This returns the smallest value in A1:A10, C1:C10, and E1:E10.
19. MIN with Dynamic Arrays
Use MIN with dynamic arrays (Excel 365+):
=MIN(FILTER(A1:A10, B1:B10="Category"))
This returns the smallest value in A1:A10 where B1:B10 is "Category".
20. MIN with LARGE
Find the smallest value excluding the smallest:
=MIN(IF(A1:A10>MIN(A1:A10), A1:A10))
This returns the second smallest value in A1:A10.
These examples demonstrate the versatility of the MIN function in Excel for various scenarios.
No comments:
Post a Comment