110 PCWorld FEBRUARY 2020HERE’S HOW EXCEL FORMULAS CHEAT SHEET
must choose Format > Format Cells >
Number > Date, and then select a format
from the list.
Formula: =DATEVALUE(H33)- =FORMULATEXT
Use this function to display the “text” of a
formula in a given cell. For example, the
actual formula in cell E2 is =SUM(C2*D2);
but all you see is the answer, which is
$164.25. Sometimes it’s helpful, even
necessary, to see the actual function,
especially if you’re editing a macro or
tracking down a circular reference. So enter
this formula into a cell off to the side of your
spreadsheet matrix (such as cell F2):
=FORMULATEXT(E2) and Excel displays the
actual formula of E2. - =AVERAGE
Averages are used every day to determine
the median or midpoint of a set of numbers;
for example, average grades for a group of
students; average temperature of a region;
and/or average height of sixth graders.
Before functions, to get an average, you
would add a column of numbers; for
example 10 numbers; then divide by that
total (10). The AVERAGE function does that
for you. So typing =AVERAGE(E2:E6) adds
those five numbers, then divides by 5. - =COUNT & =COUNTA
These simple functions count the total
number of digits or text in a column of data.
COUNT only counts numbers and formulas,
while COUNTA counts everything—that is,
alpha and numeric characters plus
punctuation, symbols, and even spaces.
Why use the COUNT function? Imagine
that your friends pay dues for membership
into several different clubs. The spreadsheet
adds the dues for all clubs in column G, so
each individual knows how much he/she
owes in dues a month. Use the COUNT
function to total the number of people
in each club (without having to create a
column full of ones). For example;
=COUNT(B13:B22) tells us there are six
people in the Garden Club.- =COUNTBLANK
The COUNTBLANK function does exactly
what is says: It counts the number of blank
cells in a column or range. In column B of our
spreadsheet example, there are four blank
cells (and six cells with numbers). Note that
anything in a cell, even a space, will register
as a non-blank cell. In other words, if you put
a space in cell B13, the total blank cells
changes from 4 to 3. - =COUNTIF & combined
functions
This formula combines the COUNT and the
IF functions to count the number of cells in a
range that meet a specific condition.
For example, say that you want to count