|
AFs (array-entered formulas) are a feature of Excel that is not covered well
in Excel's help menus. Actually, until Excel 2002, they were hardly mentioned
in there at all. And, the books I have seen that address AFs would intimidate
the average user into believing this isn't something they could even attempt.
So, my intention here is to tell you what AFs can do and how to use them in
the simplest way possible. Once you understand the basics of how these work,
you can then try them on more complicated data.
First of all, why would we want to use them? Here's a scenario. Let's say you
have a list of numbers in Column A and you want a sum or a count of them based
on two different criteria. You can use a SUMIF or a COUNTIF formula
if you just have one criterion; but if you have more than one, you will need
to use an AF.
Let's do this together, with a simple example. Enter the following data into
Column A in a brand new, empty workbook.
|
|
A
|
|
1
|
Salary |
|
2
|
$500 |
|
3
|
$1000 |
|
4
|
$200 |
|
5
|
$600 |
|
6
|
$1200 |
|
7
|
$100 |
|
8
|
$5000 |
|
9
|
$1700 |
|
10
|
$400 |
|
11
|
$500 |
If you wanted a sum of all the amounts that are greater than $500, you
would use the formula:
=SUMIF(A2:A11,">500")
...which would give you the result of $9500.
If you wanted to get a count, instead of adding them together, you would
use the formula:
=COUNTIF(A2:A11,">500")
... which would give the result of 5.
But, let's say you need two criteria applied. The SUMIF and COUNTIF
formulas are limited here.
Important Note! First, let me explain how an AF must be entered.
All AFs have curly brackets { } around them. This is how Excel identifies them
as AFs. However, you do not type in the curly brackets! These are added
when you hit ctrl+shift+enter and you must do this every time you
enter or change an AF. If you manually type in the curly brackets, Excel does
not recognize it as being a formula.
Let's Try It
Say you want a sum of all of the numbers greater than 500 and less than 300.
Here's where an AF comes in really handy.
Click into cell A12 and type this formula and when you are finished typing,
hit ctrl+shift+enter:
=SUM((A2:A11>500)*(A2:A11))+SUM((A2:A11<300)*(A2:A11))
You will see that you get $9800 as a result and this is correct. And, if you
look at the formula in the Formula Bar at the top of your spreadsheet,
you will see that Excel added the curly brackets to the formula and it looks
like this:
{=SUM((A2:A11>500)*(A2:A11))+SUM((A2:A11<300)*(A2:A11))}
TIP! If your formula gives you an error or you are not getting the correct
answer, and you are sure you hit ctrl+shift+enter, it's possible that one of
the values in A2:A11 is not being seen correctly by Excel. To help troubleshoot
your problem, you can display what Excel is calculating in any part of your
formula. When you use AFs, Excel looks into each area nested in parentheses
and calculates that first. So, in the example above, A2:A11>500 would return
either a true or a false. Is it greater than 500 or isn't it?
To check, highlight A2:A11>500 (but not the parentheses around it), as in
the image below.
Then, hit your F9 key and you will see what Excel is seeing:
This is a handy tip to remember for troubleshooting the parts of any formula
that are giving you problems. Once you have seen the True/False results, hit
your Esc (Escape) key to exit the formula. Hitting Enter will not work,
because Excel will think you are correcting the formula and remember that any
time you correct an AF, you must hit ctrl+shift+enter.
So, that's how to do a simple sum AF. Now, what if we wanted a count instead
of a sum? That's easy to fix. All you need to do is replace the part of the
formula that multiplies the cells by themselves, with a one (1).
Just adjust the above formula to look like this, then hit ctrl+shift+enter
again to update it:
=SUM((A2:A11>500)*1)+SUM((A2:A11<300)*1)
The result you get should be 7. If it's not, recheck your formula and try the
F9 trick mentioned above.
Here's another scenario where an AF comes in handy. Let's say you want to
sum or count the contents of Column A, based on something that is in another
column. Yup, AFs can do this too and boy, is this ever handy!
Add this data to Column B:
|
|
A
|
B
|
|
1
|
Salary |
Dept.
|
|
2
|
$500 |
A
|
|
3
|
$1000 |
B
|
|
4
|
$200 |
C
|
|
5
|
$600 |
D
|
|
6
|
$1200 |
C
|
|
7
|
$100 |
B
|
|
8
|
$5000 |
A
|
|
9
|
$1700 |
A
|
|
10
|
$400 |
A
|
|
11
|
$500 |
C
|
Here are some formulas you can try to see how this works. These formulas include
the curly brackets, but remember, you do not type them, just hit ctrl+shift+enter
to let Excel add them for you:
Total salaries for the people in Dept. A:
{=SUM((A2:A11)*(B2:B11="A"))}
...the result should be $7600
Count of all people in Dept. C who earn less than $1000:
{=SUM((A2:A11<1000)*(B2:B11="C"))*1}
...the result should be 2
Average salary for people who make more than $1000:
{=AVERAGE(IF(A2:A11>1000,A2:A11,""))}
...the result should be $2663.33
Getting the hang of it? I know it's a lot to grasp, but I recommend you think
of various scenarios and try them out. The best way to learn this stuff is to
play with it.
Have fun!
For more information on Excel formulas, visit Linda's Interactive Formula
Pages:
IF Statement: http://personal-computer-tutor.com/IFstatement.htm
VLOOKUP: http://personal-computer-tutor.com/vlookup.htm
Or check out Linda's eBooks, online classes, and Office Tutorial CD:
http://personal-computer-tutor.com/services.htm
|
