Pivot Table Report – Grouping the Date Field by Days, Months, Quarters and Years in Microsoft Excel 2010

 

If you want to find the minimum value based on the criteria in a range,you can use MIN & IF functions to retrieve the output.

Min: Returns the smallest number from a range of cells or array. For example,if a list of numbers contains 5, 6, 7, 8, 9 & 10, then the output will be 5.

Syntax =MIN(number1,number2,…)

number1:These are numeric values.They can be numbers, name ranges or arrays. It is the first value argument that represents the values that you have taken as a reference.

number2:These are numeric values.They can be numbers, name ranges or arrays. It is the second value argument that represents the values that you have taken as a reference.

There can be a maximum 255 arguments.Refer below shown screenshot:
style=”font-size:16px;”
img1
 
The IF function checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.

Syntax = IF(logical_test,value_if_true,value_if_false)
 
logical_test: Logical test will test the condition or criteria.If condition meets then it returns the preset value, and if condition does not meet then it returnsanother preset value.

value_if_true: The value that you want to be returned if this argument returns TRUE.

value_if_false: The value that you want to be returned if this argument returns FALSE

Let us take an example:-

We have some codes in column A & numbers in column B.Condition is set in cell D2 & D3.We want a formula that will give the minimum value from column B based on the criteria set in cell D2 & D3.
 
img2
 

  • In cell E2, the formula would be
  • {=MIN(IF($A$2:$A$9=D2,$B$2:$B$9))}
  • This is an array formula which requires formula to be enclosed with curly brackets by using CTRL + SHIFT + ENTER.

 
img3
 

  • Copying the formula in cell E3, we will get the minimum output for code BBB

 
img4
 

 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>