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 kth 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?
No comments:
Post a Comment