Contextures

Contextures News

Excel MIN IF Formula

July 11, 2017

There's no MINIF function, so build your own, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos. We're on our summer schedule now, so the next newsletter will arrive on July 25th.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Build a MIN IF Formula

To find the lowest amount in a list, use the MIN function. But how can you find the lowest amount based on criteria, such as the lowest quantity of Pens sold? There isn't a built-in MINIF function, but you can combine MIN and IF functions, to build your own.

In this example, sales records are in cells F2:G9. "Pens" is entered in C2, and this formula in cell D2 will find the lowest quantity for that product. This is an array-entered formula, so press Ctrl+Shift+Enter instead of just pressing Enter.

=MIN(IF($F$2:$F$9=C2,$G$2:$G$9))

You can get results for multiple criteria too, by nesting another IF function in the formula. See that example on the MIN IF or MAX IF page on my website.

NOTE: If you don't like array-entered formulas, create a pivot table based on the data, and use the "Summarize Values By" option to show the Min for each product.

MIN IF formula

Interactive Dashboards

Learn how to build interactive dashboards with Power BI and Excel, using free tools. In a free one-hour webinar, Mynda Treacy shows you how to go from raw data in Excel, to a sophisticated set of dashboards, in a few simple steps. You don't need advanced Excel skills or macros to create these interactive displays.

The webinar covers lots of material, very quickly, but don't worry about taking notes. After the webinar, you can download the full video and practice files, to review the lesson at your own pace. I really liked that -- there were a few steps that I wanted to see again.

Don't let everyone get ahead of you, with the Power tools! This webinar is a great way to see the amazing things you can do in modern Excel. Click this link to register, and don't delay -- the webinars end this Thursday, July 13th.

Excel Articles

Here are a couple of recent Excel articles that you might find useful or interesting.

Rounding Time - Chandoo shares his formula for rounding time to minutes or seconds, and there are more suggestions in the comment section. (Level - Intermediate)

Power Pivot - On his latest Excel podcast, John Michaloudos talks with Rob Collie, who was on the Microsoft team that developed Power Pivot. Rob started using Excel to run his fantasy football league! (Level - All)

Math and Sports - There's a lot of math in Excel, so you might enjoy these cartoons of mathematicians explaining sports to each other. My favourite is the Soccer one -- "You could do the whole thing in Excel!". (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues

Stop and Smell the Flowers

Our granddaughter came to visit for a few days, and she certainly kept us busy. As usual, we did arts & crafts, and a bit of baking. We also played mini golf, and she was the only one in our group to get a hole-in-one! Then, just for fun, we had a Christmas in July day, and exchanged silly gifts from the dollar store -- things like glow sticks, water balloons, and stuffed monkeys! After all that excitement, it was nice to take a few minutes to stop and smell the lilies (the roses had ants and bees on them).

weekly photo

That's it for this week! Watch for the next newsletter in 2 weeks.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: http://www.contextures.com/newsletter/excelnews2017/20170711ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:41 AM