All articles with 'MIN()' Tag

How to find the lowest value? [Quick tip]

Published on Aug 12, 2013 in Excel Howtos
How to find the lowest value? [Quick tip]

Lets say you are the head of purchasing department at Big Corp Co.

You are obviously very busy. Every day starting with a large cup of coffee and ends with a big smile, as you save your company thousands of $s by negotiating best deals, finding best providers and being awesome.

Today, let me share a small Excel tip with you that will make you even more awesome.

Continue »

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Published on Jul 19, 2013 in Formula Challenges, Posts by Jeff
Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Welcome back to yet another gripping episode of “When good formulas go GREAT”. We’re working up the singles charts to the number one hit “Formula Challenge 001 – Return everything in string after first block of numbers” By way of a quick refresher, this formula challenge calls for a formula to return a substring from […]

Continue »

Formula Forensics No. 027 – Remove Leading Zeroes

Published on Aug 23, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 027 – Remove Leading Zeroes

Today in Formula Forensics we look at how to strip away leading zeroes from strings with this simple array formula

Continue »

Extracting numbers from text in excel [Case study]

Published on Jun 19, 2012 in Excel Howtos
Extracting numbers from text in excel [Case study]

Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.

Continue »

Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Published on Jun 7, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Today at Formula Forensics, we look at how to Count and Sum data using Criteria on Filtered data sets.

Continue »

Formula Forensic 020. Bhavik’s Monthly Workingdays Formula

Published on May 3, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic 020. Bhavik’s Monthly Workingdays Formula

Bhavik’s has presented us with a neat formula to determine how many working days are in a month.
This functionality has a wide range of uses in accounting, payroll, staff scheduling, accommodation planning & reporting and general scheduling activities.

Today in Formula Forensics we pull Bhavik’s Formula apart and see what is going on inside and the offer some extensions to increase the functionality of his formula.

Continue »

Formula Forensics. 009 – Pradhishnair’s Chainage Problem

Published on Jan 17, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics. 009 – Pradhishnair’s Chainage Problem

A common Forum Post question and one that Chandoo has written about a few times is, Does my data overlap with another range?

This week Formula Forensics examines Pradhishnair’s Overlapping Chaninage Problem where he wants to know if two values overlap with a range of other values

Continue »

Scheduling Variable Feed Sources

Published on Nov 18, 2010 in Excel Howtos, Posts by Hui
Scheduling Variable Feed Sources

In many industries, bulk commodities are received or delivered in batches or parcels of various quantities and with various properties.
This post will look at one option for scheduling these commodities within Excel.

Continue »

29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn Excel

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Continue »

Array formula to check if a number is prime [just for fun]

Published on May 29, 2009 in Learn Excel

I am math-geek-wannabe, if there ever is such a category. During my 3rd year of graduation I went and purchased the volume 2 of Donald Knuth’s Art of Computer Programming and thus began my love with all things random and prime. I never really became the math-geek I always wanted to, instead I became an insurance expert with tons of passion for data and visualization. But when I get a chance to poke with randomness or numbers, I always lap it up with joy. And that brings us to an interesting array formula trick to check if a number is prime or not.

(assuming the number is in the cell B2) type the below formula and

=IF(MIN(MOD($B$5,ROW(INDIRECT(“2:”&INT(SQRT($B$5))))))=0,”not prime”,”prime”)

hit ctrl+shift+enter and bingo, it tells you if the number is prime or not. Read on to find how it works

Continue »