Thank you for subscribing to the RSS feed for Excel Pivot Tables .
For more pivot table tips, please visit Contextures.com
In Excel, you can use array formulas to find MIN IF and MAX IF. For example:
What was the lowest total order price for the Carrot Bars product?
What was the highest price for that product?
In the screen shot below, you can see the formula that calculates the MIN IF price:
=MIN(IF(Sales_Data[Product]=”Carrot”,Sales_Data[TotalPrice]))
Use a Pivot Table Instead
An easier way to find the lowest and highest values for a specific item, is to use a pivot table. It automatically creates a list of all the products, and you can select which function or functions you want to see in the totals.
In the screen shot below, the TotalPrice field has been added twice to the values area. In one field, the summary function has been changed to MIN and in the other field it was changed to MAX.
This is much quicker and easier than manually create a product list and entering the array formulas. You can read more about the summary functions on my Contextures website.
Video: Find MIN IF and MAX IF with a Pivot Table
In this video, you’ll see how to create a pivot table and show the list of products. Then, add the Total Price field, and see the highest and lowest order totals for each product.
Or watch the video on YouTube: Find MIN IF and MAX IF From Excel Pivot Table
Please Take the Number Format Survey
I’m working on an Excel Ribbon, and would like to find out which number format settings are most popular in pivot tables.
If you have a minute, please answer the 3 questions to help me see which number formats are most often used in pivot tables. Thanks!
____________________
Share and Enjoy
• Facebook • Twitter • Google Plus • LinkedIn