Some Special/Useful Functions 


Outline
1) Overview
2) Random Numbers and Recalculation:
3) Simple Statistical Functions on Ranges:
4) The COUNTIF Function (and more on using BOOLEAN values):
5) The IF function:
6) Summary:


1) Overview:

In this lecture we will examine some interesting and useful functions that are
not like the typical mathematical functions that we know: RAND, RANDBETWEEN,
COUNT, COUNTIF, and IF. We will use these new functions, together, in various
models, including for for computing the probability of throwing various
combinations of dice and the probablility of winning or losing on the the first
throw of a craps game.


2) Random Numbers and Recalculation:

When calculated, the RAND function produces a random value in the range [0,1).
In this notation, the "[" means "closed" which means that a random number of 0
CAN be generated. The ")" means "open", which means that a  random number of 1
CANNOT be generated. Thus, this interval specification [0,1) means that RAND
always produces a result that is always >= 0 and strictly < 1 (so it could
generate 0 and .999999999999 but not 1).

To use the RAND function in a formula we write RAND(). Like the PI() function,
it has no arguments. Remember that all function names must be followed by
parentheses that can contain their arguments, even if no argument goes in the 
parentheses. Of course, standard mathematical functions like SQRT always take
one arguement (the value to compute the square root of), but the RAND function
takes no argument.

-->Create a worksheet named RAND
-->Enter RAND (boldfaced) into cell A1
-->Compute random numbers in cells A2:A21 by entering the formula =RAND() into
-->  cell A2 and filling the next 19 cells with a copy of this formula.

We have seen that when we enter a new value into a cell, all cells with
formulas that depend on that one recalcuate their formulas to produce new
values, and then all cells with formulas that depend on these cells also
recalculate their formulas to produce new values, etc. until all necessary
cells recalculate their values based on the changed cell. Recall how we used
the Trace Dependents and Trace Precedents commands to find which cells are
inputs to and outputs of other cells.

When Excel recalculates ANYTHING, a formula that contains the RAND() function,
recalculates a new random number.

-->Enter a value in any cell on the RAND worksheet and watch cells A2:A21
-->  recalculate their values.
-->Delete the value you entered in that cell; cells A2:A21 recalculate their
-->  values again.

We can force Excel to recalculate all its formulas by pressing the F9 key, or
alternatively by clicking the upper calculator icon (called Calculate Now) in
the Calculation group at the far right of the ribbon with the tab Formulas.
Of course, you can put this icon in the QAT, but most student memorize that F9
does this operation.

Typically, other cells have formulas that calculate results based on these
random numbers. Pressing F9 is useful in spreadsheets that contain random
numbers: by pressing it, the spreadsheet recalculates new random numbers and
recalculates the the formulas in other cells that depend on the random numbers.

-->Press the F9 key a few times and watch the random numbers recalculate
-->  themselves in cells A2:A21.

Truth be told, you can stop Excel from recalculating cells EXCEPT when you
explicitly tell it to do so. If you click the big left calculator icon (named
Calculation Options) in the Calculation group at the far right of the ribbon
with the tab Formulas, then click Manual, Excel will only recalculate when you
press the F9 key or the recalculate icon. I find it difficult to work in manual
calculation mode; in only a few cases does it make anything easier.

It is easy to use the standard RAND function to calculate random numbers in
any range (and you should memorize this technique). For example, suppose that
we wanted to calculate random numbers in the range [1,10). We use the formula
9*RAND()+1.

-->Enter 9*A1+1 (boldfaced, just the text, NOT a formula: no =) into cell B1
-->Enter the formula =9*A2+1 into cell B2 and the fill the next 19 cells
-->  cells with a copy of this formula.

Notice that the values displayed are all between 1 and 10; you may get exactly
the number 1 (although it is not likely) but you will never get exactly the
value 10, because these random numbers are in the range [1,10). We can prove
that random numbers will be generated in this range by using range notation and
simple arithmetic.

  RAND()   calculates a random number in the range [0,1), so
9*RAND()   calculates a random number in the range [0,9)
           (the smallest value is still 0, the largest not quite 9), so
9*RAND()+1 calcuates a random number in the range [1,10)
           (the smallest value is 1 bigger than 0, the largest not quite 1
            bigger than 9).

So 9*RAND()+1 calculates a random number in the range [1,10).

We can generalize this result to calculate random numbers in any range. If we
want to calculate a random number in the range [a,b), we can use the formula
(b-a)*RAND()+a. Notice in the specific example we did above, calculating random
numbers in the range [1,10), a=1, b=10, so (b-a)=9 and the formula is
9*RAND()+1. Generally, using the same demonstration, 

  RAND()       calculates a random number in the range [0,1), so
(b-a)RAND()    calculates a random number in the range [0,b-a)
                (the smallest value is still 0, the largest not quite 9), so
(b-a)*RAND()+a calcuates a random number in the range [a,b)
                (the smallest value is a bigger than 0, the largest not quite a
                 bigger than b-a: b-a + a = b).

This is a very useful formula and you should memorize it (or be able to
reconstruct it from the demonstration shown above).

-->Using this information, enter a formula in A24 that calculates a random
-->  number between -5 and +5. Copy this formula in all the cells in a
-->  rectangle whose upper left corner is A24 and whose lower-right corner
-->   is J23 (not a row or column at a time, but with one big copy/past in
-->   the desired rectangle).

RANDBETWEEN is a second useful function that calculates random numbers. Unlike
the RAND function, this function always calcluates an integer result, based
on the values specifed by its two arguments. So, if we write =RANDBETWEEN(1,10)
in a cell, then this formula calculates a random number that is either 1, or 2,
or 3, ..., or 10 with equal probability (each number about 1/10th of the time);
if we write =RANDBETWEEN(1,2) in a cell, then this formula calculates a random
number that is either 1 or 2.

For RANDBETWEEN to work correctly, its first argument must be <= its second
argument (as it is in the two examples above: 1<=10 and 1<=2). If the two
arguments are the same, e.g. RANDBETWEEN(5,5), then all ther random numbers
will be 5 -not too interesting; it the first number is > than the second,
then the cell will display the error value #NUM!
       
-->Create a worksheet named COUNTIF (we use it here, and again later)
-->Enter the text Low and High (boldfaced) in A1 and A2 respectively
-->Enter 1 and 10 in cells B1 and B2 respectively
-->Enter a formula in A3 that computes a random number between whatever value
-->  is in B1 up to the value that is in B2
-->Copy and past this formula in all the cells in a rectangle whose upper left
-->  corner is A4 and whose lower-right corner is J12
-->Did you use relative or absolute references to B1 and B2? Which is correct?
-->  You want every formula to refer to B1 and B2, after pasting
-->Change the values in cell B1 and/or B2 and see how Excel recalculates the
-->   random numbers; press F9 and what the results change


3) Simple Statistical Functions on Ranges:

Excel has many common statistical functions that operate on ranges: MAX, MIN,
SUM, AVERAGE, and COUNT are the most common ones. We have used some of these
functions before in our worksheets. These functions operate on ranges
specifying rows, specifying columns, or even specifying rectangles.

-->On the RAND worksheet, calculate the max, min, sum, average and count of all
-->  the random numbers specified in the column range B2:B21 (with appropriate
-->  labels)
-->Press F9, which will recalculate all the random numbers, and therefore also
-->  recalculate these statistical quantities
-->Calculate the max, min, sum, average and count of all the random numbers
-->  specified in the rectangular range A24:J33 (with appropriate labels)
-->Press F9, which will recalculate all the random numbers, and therefore also
-->  recalculate these statistical quantities

COUNT returns how many numbers are in the cells specified by its arguments. If
a cell cotains text or a boolean value, it is not counted.

-->Enter some text in cell B13 and observe what happens in cell E6

The other statistical functions ignore cells that don't contain numbers.

Remember, when you change any cell, all the cells containing formulas using
random number recalculate their values. This changes the max, min, sum, and
average -as well as the count. The count drops to 19 because of the entered
text.

-->Undo the change, reverting cell B13 to the formula there originally

Sometimes we'd like to make changes to formulas that operate on random values
to see the effect. But, if ALL THE RANDOM VALUES CHANGE, it is hard to
understand the effect. 

One way to overcome this problem while debugging a spreadsheet containing
random numbers is to copy all the values in a range (here A2:A21) and paste
them back in the same range by using the Paste disclosure triangle (in the
Clipborad group on the ribbon for the Home tab) and then clicking Paste_Values.
This replaces all the formulas in these cells with the values that the cells
currently contain. Now we can change individual values in these cells without
the other cells recalculating random values. Likewise, these cells remain
unchanged when we press F9. They store values, not formulas. And, all the
formulas in column B compute the same values because the cells in column A that
they depend on do not change. If we want to go back to formulas we will have to
enter them all again.

There are many other uses of copy and paste special, which we will discuss
later in the course.

After this operation, we can explore changing formulas that operate on these
random values (which now don't change). This technique is useful for debugging
these formulas. After they are correct, we can restore each of these cells back
to calculating random numbers.

-->Copy and paste (special) cells B2:B21 as described above
-->Press F9 and watch the random numbers in column A change, but not in column
-->  B and not in the statistical cells E2:E6
-->Change the number in cell B13, watch it and watch the statistics change (but
-->  just by a small amount; try putting some text in cell B13
-->Enter Formula Auditing display mode and undo commands until the formulas are
-->  restored to cells B2:B21.

Again, notice that max, min, sum, average, and count ignore cells that do not
contain numbers. Average is computed by SUM and COUNT functions, so text is not
added into the sum and is not counted, so average truely computes the average
of all numbers in a range (totally ignoring text)

-->On the COUNTIF worksheet, enter a formula into cell B15 that calculates how
-->  many numbers are in the rectangle whose range is A2:J12

Now we will discuss a function that is similar to the COUNT function, but is
much more useful and powerful.


4) The COUNTIF Function (and more on using BOOLEAN values):

COUNTIF takes two arguments: it is illustrated in Excel as 
COUNTIF(range,criteria)

We already know about ranges (which can be rows, columns, and rectangles).
The second argument, criteria, specifies under what condition to count
the number in the row, column, or rectangle. There are many interesting forms
for criteria. The simplest are 

  (1) A constant: e.g., 5
  (2) A cell: e.g., A1 (meaning the value in that cell)
  (3) Some text representing the criteria: e.g., "<=5" or "<"&A1)

Here is what COUNTIF does if each criterion is used

  (1) It counts how many values in the range are = to 5
  (2) It counts how many values in the range are = to the value stored in A1
  (3) It counts how many values in the range are <= to 5 or <= A1

In fact, we can use the third form and write the criteria as "=5", in which
case it will calculate the same result as (1) or "="&A1, in which case it will
calculate the same result as (2).

What if we want to determine how many cells meet the criteria of being <=
the value in ANOTHER CELL (say <= the value in cell A1). We CANNOT write the
criteria as "<=A1". We CAN write the criteria as "<="&A1, which if you recall,
will catenate the text "<=" with the VALUE in cell A1 to produce the test; so,
if A1 stored 8, the criteria "<="&A1 would calculate the text value "<=8" and
be equivalent to the criteria "<=8"). But "<=A1" would make no sense because
Excel doesn't substite values for the cell addresses INSIDE TEXT.

Let's practice with using these forms of criteria with COUNTIF.

-->On the COUNTIF worksheet enter the text Value into cell A17
-->On the COUNTIF worksheet, enter the values 1-10 in cells A18:A27
-->Enter the formula =COUNTIF(A3:J12, A18) into cell B18
-->  Do this by entering =COUNTIF( and then selecting the rectangular range
-->  and then by completing the formula, ending it in an )
-->Fill this formula into cells B19:B27

We know that since all the random numbers are between 1 and 10, the count of
all the 1s, 2s, 3s, ... 10s should add up to 100

-->Enter the formula =SUM(B18:B27) into cell B29

Notice that although this sum should be 100, it is smaller. It is common to
formulas as "checks" to make sure other formulas are correct. Here we know
what the SUM should produce; by seeing a different number there, we know that
something is wrong with the formulas in B18:B27.

When I first composed this spreadsheet, I actually made an error, and spotted
it through the use of the SUM check. Get into the habit of thinking up simple
checks and placing them in your worksheets. For example, if I compute the
percentages for a bunch of possibilities, I often SUM them (they should sum to
1) to ensure that I have taken all the possibilities into account.

We can use formula auditing (in the various forms that we have discussed) to
find the source of the error. Everything looks fine if we audit cell B18,
but if we audit cell B19 we see the range it uses is not A3:J12 but instead
A4:J13! When I used fill, it copied the formula in cell B18
   =COUNTIF(A3:J12,A18)
into the cell B19 as
   =COUNTIF(A4:J13,A19)
because all the references here are RELATIVE. While the reference in the second
argument should be relative (A18 correctly goes to A19) so that the COUNTIF is
comparing to a different value, both references in the first argument should be
ABSOLUTE, so the same rectangle of values is selected each time for analysis.
To correct this problem,

-->Enter the text (boldfaced) "= Value" into cell B17
-->Enter the formula =COUNTIF($A$3:$J$12, A18) into cell B18
-->  Do this by entering =COUNTIF( and then selecting the rectangular range
-->  and then pressing F4 and then by completing the formula, ending it in an )
-->Fill this formula into cells B19:B27

Now the sum in cell B29 should be 100.

-->Enter the text "<= Value" into cell C17
-->Enter the formula =COUNTIF($A$3:$J$12,"<="&A18) into cell C18
-->Fill this formula into cells C19:C27

Notice that in the column, C18 displays the number of values <=1 (which should
be the same as A18), C19 displays the number of values <=2 (which should
be the same as the sum of  A18 and A19), ... C27 displays the number of values
<=10 (which should be 100, since all the values are <= 100).

Here is another way to do that same computation

-->Enter the text (boldfaced) Sum to Top into cell D17
-->Enter the formula =SUM($B$18:B:18) into cell D18
-->Fill this formula into cells D19:D27

Press F9 a few times to see cells C18:C27 and D18:D27 always match.

So, we have seen we can use COUNTIF with any single relational operator
catenated with any value (a constant, another cell, or even a formula on
another cell: we haven't seen this last one yet, but it is allowed, something
like "<"&A1+A2, which would count values if they were less than the sum of
A1 and A2). Recall that the + operator has a higher precedence than the <
operator.

Now, what if we want to determine how many cells meet a more complicated
criteria, involving multiple relational operators. Suppose that we wanted to
count those cells that were the N lowest or the N highest values. For example,
if N were 2, we'd be looking for values that were the two lowest (either 1 or
2) or the two highest (9 or 10). Read this paragraph slowly and carefully, as
well as the following ones in this section to make sure you understand the
material, which is complicated.

Excel can do this kind of complex counting if we

  (a) Create a temporary shape equivalent to the rectangle of cells each with
      a formula that is TRUE if its corresponding random number is in the
      required range

  (b) Enter a final cell with a COUNTIF formula that counts how many cells
      from part (a) are equal to TRUE.

-->Enter text N (boldfaced) in cell A32 and the value 2 (boldfaced) in cell B32
-->Enter in cell A33 the formula =OR(A3<$B$1+$B$32, A3>$B$2-$B$32)
-->Copy and paste A33 into cells A33:J42; note that we cannot use Filling,
-->  because we are creating a rectangular shape: filling works only for rows
-->  or columns (not both at the same time)
-->Use Trace Precedents to see all the cells that provide inputs to this one
-->Enter text Extreme (boldfaced) in cell A44
-->Enter the formula =COUNTIF(A33:J42,TRUE) int cell B44, which counts how many
-->  TRUE values there are in the rectangle of BOOLEAN values

A33 will become the upper-leftmost cell in the rectangle of BOOLEAN values. It
refers to A3 (the upper-leftmost cell in the rectangle of random values) and
checks whether it is < $B$1 (which is currently 1) + $B$32 (which is currently
2); if either that relation is TRUE, or it is > $B$2 (which is currently 10)
- $B$32 (which is currently 2). So it checks A33 < 3 or A33 > 8, which checks
whther A33 is 1, 2, 9, or 10.

We make A33 a relative reference, so that when copied into the rectangle, it
changes, and make all the other references absolute, so that they don't. The
end result is a rectangle of BOOLEAN values, the same size as the rectangle of
random values, with each BOOLEAN computing whether it RANDOM number (the one
in the same place in the upper rectangle) satisfies the required condition.

-->Click on a cell showing TRUE and then click the Evaluate formula icon, and
--> click Evaluate repeadtedly, watching how the value in that cell is
-->   computed; do this again for a cell showing FALSE

Note that if we change the High value, cell B2, to 20, all the random numbers
will recompute, in the range 1 to 20, and the Extreme count will count those
values that are 1, 2, 19, or 20 (many more FALSE value than before).

Likewise, if we change N value, cell to B32, to 5,  all the random numbers
will recompute, still in the range 1 to 20, but now the Extreme count will
count those values that are 1, 2, 3, 4, 5, 16, 17, 18, 19, or 20 (many more
TRUE value than before: in fact about 1/2 the values).

With this approach, we can count any arbitrarily complicated criteria for a
row, column, or rectangle of values.


5) The IF function:

The final function that we will examine in this lecture is the IF function.
It has the form: IF (boolean formula , formula1 ,formula2). Excel evaluates the
boolean formula first: if it is TRUE than formula1 calculates the value of the
cell. If it is FALSE then formula2 calculates the value of the cell. The
simplest of formulas are just numbers or text (which inside a formula are
always enclosed in quotation marks: although text values representing entire
celss appear without quotes).

-->Create a worksheet named IF
-->Enter the text Input and IF Result (and boldface each) in cells A1 and B1
-->Enter the formula =IF(A2>=0,"Positive","Negative") into cell B2
-->Enter positive and negative values into cell A2 (and 0 too) and observe
-->  if/how cell B2 changes
-->Click cell B2 and then click the Evaluate formula icon, and click Evaluate
-->  repeadtedly, watching how the value in that cell is computed

-->Enter the formula =IF( A3>0, "Credit", IF(A3<0,"Debit","")) into cell B3
-->Enter positive , 0, and negative values into cell A3 and observe if/how cell
-->   B3 changes
-->Click cell B2 and then click the Evaluate formula icon, and click Evaluate
-->  repeadtedly, watching how the value in that cell is computed

Just as we can nest mathematical functions inside mathematical functions
(e.g., COS(2*SQRT(5)) ) we can nest IF functions inside IF functions. We would
read the second function as:

  If A3>0 is TRUE, the IF function calculates the text of its 2nd argument
    "Credit" as the value of the cell
  If A3>0 is FALSE, the IF function calculates whatever value is calculated by
   its 3rd argument: the inner (second) IF as the value of the cell: to do this
      If A3<0 is TRUE, the inner IF function calculates the text of its 2nd
        argument "Debit" as the value of the cell
      If A3<0 is FALSE, the inner IF function calculates the text of its 3rd
        argument "" as the value of the cell (A3 is zero, neither positive or
        negative, so it is neither a credit nor debit).

Suppose that we wanted to compute a letter grade using the following criteria:
0-59 F, 60-69 D, 70-79 C, 80-89 B, 90-100 A.

This is a complicated formula and will result in many nested IF functions when
using the approach above. Rather than writing it all at once, we could start
by writing it as

-->Enter the formula =IF(A4<60,"F","?") in cell B4
-->Enter various values in cell A4 to ensure this formula works for values <60
-->   and results in ? for other values.

Then we could

-->Update the formula to  =IF(A4<60, "F", IF(A4<70,"D","?"))
-->Enter various values in cell A4 to ensure this formula works for values
-->   0-60, 61-69, and results in ? for other values.

Then we could 

-->Update the formula to  =IF(A4<60, "F", IF(A4<70, "D", IF(A4<80, "C", "?")))
-->Enter various values in cell A4 to ensure this formula works for values
-->   0-60, 61-69,, 70-79, and results in ? for other values.

Then we could 

-->Continue in this fashion until the formula is correctly entered, testing
-->  values in all the ranges.
-->Enter different values in A4 and see how B4 updates.
-->Click cell B4 and then click the Evaluate formula icon, and click Evaluate
-->  repeadtedly, watching how the value in that cell is computed

Excel puts a limit of 7 IF statements nested inside each other. Often we can
use extra cells to bypass this limit. For example, the formula above will
result in a cell whose formula contains 4 IF functions. We could do the
equivalent by entering the two formulas

  =IF(A5<60, "F",   IF(A5<70, "D",  IF(A5<80,"C","?")))

into cell C5 and 

  =IF(A5<80, C5, IF(A5<90,"B","A)))

into cell B5. Here C5 computes its result from C4 when A5 < 80. Notice
that the first formula has 3 IF functions and the second one has 2 IF
functions (each les than the 4 IF functions in the original formula).

As we saw with mathematical functions, we can use extra cells in a spreadsheet
to perform parts of the computation, simplifying the formulas in each cell at
the expense of using (spreading the calculation over) more cells.


Let's see another way, using the IF function, to calculate how many random
values are between two limits

-->On the IF worksheet
-->Enter the text Low and High (boldfaced) in A8 and A9 respectively
-->Enter 1 and 10 in cells B8 and B9 respectively
-->Enter a formula in A10 that computes a random number between whatever value
-->  is in B8 up to the value that is in B9
-->Copy and paste this formula in all the cells in a rectangle whose upper left
-->  corner  is A10 and whose lower-right corner is J19

Now suppose that we want to calculate how many of these random values are
between some minimum and maximum values inclusive; say the minimum is 3 and
the maximum is 7.

-->Enter the text Min and Max (boldfaced) into cells A20 and A21
-->Enter the values 3 and 7 into cells B20 and B21

Th test is too complicated for the COUNTIF function (the criteria is too
complicated because it has more than one relational operator). We could use an
IF with the BOOLEAN approach, but instead we will use a similar one, using
an IF function that produces a numerical result, in a cell range that has the
same shape as the one storing the random numbers.

-->Enter the formula =IF( AND(A10>=$B$20, A10<=$B$21), 1, 0) in cell A22
-->Notice that this formulat uses an IF function along with the AND function
--> that we studied earlier, and subformulas with relational operators.
-->Copy and paste this formula in all the cells in a rectangle whose upper left
-->  corner  is A22 and whose lower-right corner is J31
-->Because of the relative reference to the cell A22, each of the pasted
-->  formulas will refer to a different cell in the upper rectangle
-->To see this, use an appropriate formula auditing tool to see that each cell
-->  in this lower rectangle refers to a different cell in the upper rectangle

Notice in this each cell in the lower rectangle calculates the value 1, if 
its corresponding cell in the upper rectangle is a value between B20 and B21
inclusive (3 and 7 now); otherwise the cell in the lower rectangle calculates a
value of 0.

Now, we can calculate the number of values in the upper rectangle that are
between 3 and 7 inclusively, by summing all the values in the lower rectangle
(each cell with 1 in the lower rectangle counts as another cell in the upper
rectangle whose values satisifed the criteria).

-->Enter the text Count (boldfaced) in cell A34
-->Enter the formula =SUM(A22:J31) in cell B34


Now let's look at throwing 100 dice in the game of craps and computing the
probability of winning or losing on the first throw, or having to make your
point on subsequent throws. In craps, you win on the first throw if you roll
a 7 or 11; you lose if you throw a 2, 3, or 12; for all other throws you must
make your point (throw that same value again before throwing a 7).

-->On the IF worksheet, fill in the formula =RANDBETWEEN(1,6)+RANDBETWEEN(1,6)
-->   in cell A40, and then copy and past that formula in the range A40:J49
-->Enter the formula
-->  =IF(OR(A40=7,A40=11),"win", IF(OR(A40=2,A40=3,A40=12),"lose","point"))
-->  in A52, and then copy and past that formula in the range A52:J61

This formula uses the rules explained above to compute "win", "lose", or
"point". The outer IF computes "win" if the cell's value is 7 or 11; it
computes the inner IF otherwise, which computes "lose" if the cell's value is
2, 3, or 12, and computes "point" otherwise.

-->Enter the text win, lose, and point (notice we use no quotes, because we are
-->  not in a formula) in cells A64, A65, and A66
-->Enter the formula =COUNTIF($A$52:$J$61, A64) into cell B64, and then fill
-->  it down to B66
-->The three cells in column B compute the number of wins, loses, and having to
-->  make points for the throws in A40:J49.

As a check

-->Enter the text Sum (boldfaced) into cell A68 and the formula  =SUM(B64:B66)
-->  in cell B68, which should always sum to 100.

Press F9 a few time to recalculate everything. While all the throws change,
the numbers should not change too much.


As a final example, let's calculate the probability of throwing every
possible value (2-12) using two dice (each throwing a value of 1-6).

-->Create a worksheet named 2 Dice
-->Enter the formula =RANDBETWEEN(1,6)+RANDBETWEEN(1,6) in cell A1
-->Copy and past this formula in all the cells in a rectangle whose upper left
-->  corner is A1 and whose lower-right corner is J30.
-->Enter the test Throws# (boldface) in cell B32
-->Enter a formula that count the number of throws (boldfaced) in cell B32
-->Enter the text Throw, Count, Percent (boldfaced) in cells A34, B34, and C34
-->Enter all possible throws for the two dice in the range of cells A35:A45
-->Count how often each of these possible throws occurs in the range of cells
-->  B35:B45
-->Compute the percentage of times each of these throw occurs in the range of
-->  cells C35:C45 by dividing each count by the total number of throws
-->Add up the Counts and Percents in columns B and C to ensure that everything
-->  has been counted.

Press F9 a few time to recalculate everything. While all the throws change,
the percentages should not change much.

Construct an equivalent sheet for throwing 3 dice (where the possible throws
are 3-18).

Throughout the quarter we will continue to find new and interesting uses for
the IF function, and you will gain more experience using it and understanding
this important function.


6) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something in Excel.

Know how to use the RAND and RANDBETWEEN functions in formulas; know how to
transform the random numbers [0,1) into any interval [a,b).

Know that the F9 key recalculates all the values of the cells in the
spreadsheet. Know when cells containing formulas (especially those using
random functions) are recalculated.

Know how to use the MAX, MIN, SUM, AVERAGE, and COUNT function on ranges that
are row, columns, and rectangles.

Know how to use the COUNTIF function, with it range and criteria arguments.

Know how to use arbitrary BOOLEAN formulas to do advanced counting.

Understand that sometimes we add simple checks into our worksheets to ensure
that we are entering formulas correctly. If one of these simple checks fails,
know how to use formula auditing to find and correct the problem.

Begin to understand how to use the IF function. We will see more uses soon.