Find the Smallest Value in an Array based on a specific Item

Earlier today a co worker presented me with an Excel problem that he could not figure out. He wanted to find the lowest value in a table. Here is a short sample of the table he was looking at.

	A	B	C
1	ID	Product	Sale Amount
2	10001	Hammer	15.50
3	10002	Saw	7.00
4	10003	Square	11.25
5	10004	Bracket	13.00
6	10005	Hammer	11.00
7	10006	Saw	12.00
8	10007	Square	10.75
9	10008	Saw	15.00
10	10009	Hammer	12.00
11	10010	Saw	13.50
12	10011	Square	6.40
13	10012	Bracket	13.00

So I thought to myself, easy I can just do a =Min formula on column C    =MIN(C:C) and return 6.40

However then he said, he wanted the minimum value of Saws in the table. In the above example that would be 7, not 6.40.

HMMMMM. No problem, I can use the =small function.

SMALL(array,k)

Now small returns the k-th smallest value in a data set. For example, the fifth smallest number.

Since I want the smallest number k = 1. If I wanted the second smallest number I would use 2.

So I wrap the small function around an IF statement.

=SMALL(IF(B2:B13="Saw",C2:C13,""),1)

NOW TO MAKE THIS WORK, TURN THE FORMULA INTO AN ARRAY

While editing the formula, press CTRL+SHIFT+ENTER

The result is 7 since the lowest sale amount for the product SAW is 7.

If he wanted the largest I could replace =SMALL with =LARGE.

Now he could use a cell reference to pull his value. =SMALL(IF(B2:B13=H1,C2:C13,""),1)

In this instance the value in cell H1 would be looked up in the formula. Now I added the cell reference after my original solution and received errors for some of my values in the list. To correct this, I needed to recalculate my formula as an array (While editing the formula, press CTRL+SHIFT+ENTER) after setting the cell reference value.

I like this solution since it is simple. His work around was to bring the table into MS Access and then create two queries to get the answer.

He walked away happy with new knowledge and I once again sat back basking in the glow of being the Excel go to guy.

So how would you have solved this Excel question?

Calculating only when two cells are not blank

Earlier this week I received an email from a reader asking how to add / subtract cells only when there was no blank data between the rows.

I.E…. He only wanted to add / subtract cells in Column A and B when there were values in either cell or just one cell. If both cells did not have a value, then he didn't want to return a value.

Well this can be accomplished by combining an If statement with a test for blank value.

In this example, the formula sums in Column C for Rows 2 and 3 since there is a value in either Column A or B. For row 4, the formula does not return a value since Columns A and B are blank.

The formula checks for a blank value in the cell in Column A and for a blank value in the cell in Column B. If both are blank, the formula returns nothing “”, else it subtracts the cell in Column A from the cell in Column B.

	A	B	C	D
1	column 1	column 2	A - B = Answer	Formula
2	1	1	0	=IF(AND(ISBLANK(A2),ISBLANK(B2)),"",A2-B2)
3		1	-1	=IF(AND(ISBLANK(A3),ISBLANK(B3)),"",A3-B3)
4				=IF(AND(ISBLANK(A4),ISBLANK(B4)),"",A4-B4)
5	4	1	3	=IF(AND(ISBLANK(A5),ISBLANK(B5)),"",A5-B5)
6				=IF(AND(ISBLANK(A6),ISBLANK(B6)),"",A6-B6)
7	6	1	5	=IF(AND(ISBLANK(A7),ISBLANK(B7)),"",A7-B7)
8	7		7	=IF(AND(ISBLANK(A8),ISBLANK(B8)),"",A8-B8)
9	8	1	7	=IF(AND(ISBLANK(A9),ISBLANK(B9)),"",A9-B9)

IF(AND

Today I want to examine the often confusing If statement when using the Boolean AND.

=IF(AND

To the beginner Excel user, this can sometimes be confusing to understand.

Let’s look at a basic time sheet for a small company. All the employees get paid the same hourly rate of $10 an hour but not every employee is entitled to overtime pay (anyone who works over 40 hours). And just for simplicity of the formula demonstration (and cause the boss is super cool), if an employee works even 1 hour over 40, they get time and a half for all the hours worked for the week! I call this company Fantasy Land and the boss’s name is Richie Rich.

Employee	Eligible For Overtime	Hrs Worked	Pay Rate	Weekly Pay
Bill	Yes	45	10.00	675
Steve	No	42	10.00	420
Dave	No	38	10.00	380
Thomas	Yes	40	10.00	400
Nancy	Yes	52	10.00	780
Sue	Yes	52	10.00	780
Rebecca	Yes	38	10.00	380

If we look at the first employee, Bill is eligible for overtime and has worked 45 hours. So if we do the math, $45 X $15 (time and one half), he is owed $675 for the weeks work.

Steve on the other hand has also worked over 40 hours but is not eligible for overtime. So his pay is $42 X $10 = $420. It would be frustrating to have to manually calculate the weekly pay for every employee. Fortunately Excel allows you to factor multiple criteria with If statements that will help Richie Rich easily calculate his payroll expense.

Employee	Eligible For Overtime	Hrs Worked	Pay Rate	Weekly Pay	Formula in Column E
Bill	Yes	45	10.00	675	=IF(AND(B2="Yes",C2>40),(C2*(D2*1.5)),(C2*D2))
Steve	No	42	10.00	420	=IF(AND(B3="Yes",C3>40),(C3*(D3*1.5)),(C3*D3))
Dave	No	38	10.00	380	=IF(AND(B4="Yes",C4>40),(C4*(D4*1.5)),(C4*D4))
Thomas	Yes	40	10.00	400	=IF(AND(B5="Yes",C5>40),(C5*(D5*1.5)),(C5*D5))
Nancy	Yes	52	10.00	780	=IF(AND(B6="Yes",C6>40),(C6*(D6*1.5)),(C6*D6))
Sue	Yes	52	10.00	780	=IF(AND(B7="Yes",C7>40),(C7*(D7*1.5)),(C7*D7))
Rebecca	Yes	38	10.00	380	=IF(AND(B8="Yes",C8>40),(C8*(D8*1.5)),(C8*D8))

If we write out the logic it would look like this…

If an employee is eligible for overtime (YES) AND Hours worked > 40, multiply Hours worked X (Hourly Pay Rate X 1.5) ELSE multiply Hours Worked X Hourly Pay Rate.

The Boolean logic for writing this would be as follows…

=IF(AND(First Test),(Second Test) are both TRUE, Execute overtime pay calculation, Else Execute regular pay calculation)

So using the Boolean AND in our test for the If statement helps up easily join multiple evaluations to help us determine the correct answer.