A reader needs to find the difference between the time listed and the earliest time listed for that same day. Here’s the data:

Date	Time	Difference
6/9/2014	14:49:05	0:00:00
6/9/2014	14:49:47	0:00:42
6/9/2014	14:50:33	0:01:28
6/9/2014	14:51:17	0:02:12
6/9/2014	14:51:31	0:02:26
6/9/2014	14:51:56	0:02:51
7/9/2014	6:19:55	0:00:00
7/9/2014	6:21:09	0:01:14
7/9/2014	6:21:31	0:01:36
7/9/2014	6:22:25	0:02:30
7/9/2014	6:22:53	0:02:58
7/9/2014	6:23:23	0:03:28
7/9/2014	6:23:47	0:03:52

The formula in the Difference column, C2, is

, filled down to fit the data.

It’s an array formula, so don’t type the curly braces, but enter with Ctrl+Shift+Enter, not just enter. The array part of the formula, the part subtracted from B2, is the smallest value where the date in column A is a match. By selecting everything in the MIN function in the formula bar and pressing Ctrl+=, you can see how Excel is calculating the minimum.

Because we’re dealing with times, the numbers aren’t so easy to read. But the important part is at the end of the array – a bunch of empty strings. When the date doesn’t match, the IF function returns an empty string. MIN ignores any text, so only the smallest of the numbers listed is returned.

I was writing some formulas for a tiered commission calculation recently that I thought I should post. But beyond just what the formulas do, it reminded me that I’ve never shared my ‘counting zeros’ opinion, so I’m wrapping that in with this post too.

You have a commission structure where you pay your salesmen 5% for every sale. If the sale is a particularly large one, you pay them a bonus commission – 8% for the portion of the sale that’s over $20,000. But you don’t want your salespeople getting so rich that they have enough money to quit. Nor do you want them to get an unfairly huge commission on an unusually large sale. So you have a third tier that reduces their commission to 1% for the portion of the sale that’s over $100,000.

Let’s look at the formulas for column H.

H4 =MAX(MIN(2*10^4,H2)*0.05,0)
H5 =MAX(MIN(8*10^4,H2-2*10^4)*0.08,0)
H6 =MAX(0,H2-10^5)*0.01

• In H4, Take the smaller of $20,000 and whatever is in H2 and multiply it by 5%. Then take that larger of that result or 0, just in case there’s a negative sale in there. (5% of $20,000 = $1,000)
• In H5, Take the smaller of $80,000 ($100k less $20k) and the amount that H2 exceeds $20,000 and multiply by 8%. Then take the larger of that result or zero. (8% of $80,000 = $6,400)
• In H6, Take the larger of 0 and the amount less $100,000 and multiply by 1%. (1% of $1,000 = $10)

The MIN part of the formulas in H4 and H5 make sure you don’t pay more commission on that tier than you should. The MAX part returns zero when the calculation goes negative.

About counting zeros. You may have noticed that I use terms like 2*10^4 to represent $20,000. I’m a big fan of commas, but I can’t use them in formulas (they’re kind of important for separating arguments). I picked up using scientific notation in formulas from a scientist I know and I love it. No more do I have count the zeros in

=IF(A1=25000000,600000,8000000)

to know if it’s 25 million, 2.5 million, or 250 million. Instead I write

=IF(A1=25*10^6, 6*10^5, 8*10^6)

An even better answer is to put those values in cells and refer to the cells. When they’re in cells, I can format them and use commas to count the zeros. But let’s face it, sometimes we hardcode numbers in formulas. And when I do, I’ve been using this method for larger numbers and, after a small adjustment period, it’s been great.