APRIL 2020 PCWorld 131down.
- The formula works, but you have
to review each column to see who
won the prizes, because each column
shows ALL the values greater than the
amount in the formula. That’s ok for a
small spreadsheet, but not for
anything larger than a single screen. - We need a Nested IF statement
for this one. Repeat numbers 1, 2, and
3 above beginning on row 20; but
instead of the formula in 3 above,
enter this formula in B20: =IF(AND($
A20>500,$A20<1000),$A20,0). - Repeat number 4 above, but
edit the formulas like this: in C20,
=IF(AND($A20>1000,$A20<1500)
,$A20,0); D20 = =IF(AND($A20>1
500,$A20<2000),$A20,0); and in
E20, =IF($A20>2000, $A20, 0).
Yes, this last one is different because
there is no “less than” amount. Then
copy down. Now you can look at each
column and determine immediately
who the winner is for that category. - AND/OR
AND and OR are common functions in the
programmers’ environment, also referred to
as Boolean operators (along with NOT).
AND means that all conditions in the query
must be true; OR means that at least one
condition must be true.
For example, looking for an applicant
with MS Word AND MS Excel experience
means the applicant must have both skills to
qualify for the job. This condition would
provide a TRUE result. Looking for an
applicant with MS Word OR Excel means the
applicant must have one OR the other, but
not necessarily both. Also a TRUE result.
Having neither skills would, obviously,
provide a FALSE result.- Copy the numbers from the
Basic IFstatements and nested IF statements.