132 PCWorld APRIL 2020HERE’S HOW EXCEL FUNCTIONS AND TOOLS
spreadsheet in item 13, or download the full
workbook at go.pcworld.com/xlfm.- Enter the following AND formula in
cell D4: =AND($B4>=501,$C4<=500).
Again, note the $ signs. Then copy down to
cell D13. - Enter this formula in cell F4:
=OR($B4>=501,$C4<=500), then copy
down. Notice the results in the rows with
borders; that is, 5, 8, and 13. The AND
results are all FALSE because both conditions
were false (or not true); while the OR results
were all TRUE because one of the conditions
was true, while the other was false. If this
seems confusing, study the numbers in
columns B and C. Then read the formulas
that calculate for the AND function, then the
OR function, and it will make more sense. - NOT
Having explained above how the AND and
OR functions
(also called
Boolean
operators)
work, the
third Boolean
operator in
that mix is the
NOT function.
Ever search
through a
really long list
of data and
wish you could remove all the entries that do
NOT apply? For example, I want to see
everything available about alternative energy
EXCEPT (or NOT) nuclear.
In Excel, it’s an easy task. Create a list of
25 companies that provide various
alternative energy sources and what those
resources are (columns A, B, C; begin on
row 4). Enter the following formula in cell
C4: =NOT(B4=”Nuclear”). Then copy the
formula from C4 down to C5 through C28.
If the response is TRUE, the energy
source is NOT nuclear. If the response is
FALSE, the energy source IS nuclear. Yes, it’s
reverse logic and you may not immediately
see a need for this function but, if you’re an
avid Excel user, you will discover many
reasons to use this formula in the future.
TIP: Remember that Boolean logic
applies throughout all database
programs, including your favorite searchAND plus OR functions.