; Excel 2007 Sales Spreadsheet Examples Toy Company
Documents
User Generated
Resources
Learning Center
Your Federal Quarterly Tax Payments are due April 15th

# Excel 2007 Sales Spreadsheet Examples Toy Company

VIEWS: 79 PAGES: 81

Excel 2007 Sales Spreadsheet Examples Toy Company document sample

• pg 1
```									       Here is a list of what a cell can contain: TEXT, NUMBER, TRUE or FALSE, Blank cell, FORMULA, Blank from formula, erro
TRUE FALSE logic formulas in Excel like: =B23=C23. This formula asks the question is the content of B23 the same as C23? T
because 15 <> 15.1. Case in words is not considered. For example Case = case is TRUE to Excel.
Comparative Operators: =, <>, >, >=, <, <=
AND function (2 up to 255 logical tests that all must be TRUE for the AND function to deliver a TRUE to the cell or
OR function (2 up to 255 logical tests that if at least one is TRUE then the OR function delivers a TRUE to the cell o
NOT function converts TRUE to FALSE and FALSE to TRUE
IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: ISTEXT function, ISNUMBER function,
ISBLANK function, ISERROR function, ISNONTEXT function
IF function can put one of two things in a cell. The IF function has three parts: 1) Logical Test, 2) Value If True, 3) Va
IFERROR function looks to see if there is an error from a formula. If there is an error it puts something in the cell, otherwise
calculate.

Logic, TRUE FALSE
formulas, IS & IF
functions
List of what can go in a cell
NUMBER                                                4.23
TRUE                          TRUE
FALSE                         FALSE
Error from formula                         #DIV/0!

Logical    Comparative
Logical Question                   No.                       No.       Formula    Operator
Is 15=15.1?                                           15.0         15.1 FALSE     =
Is 15<>15.1?                                          15.0         15.1 TRUE      <>
Is 15>15?                                             15.0         15.0 FALSE     >

Is 15>=15?                                            15.0         15.0   TRUE    >=
Is 15<15?                                             15.0         15.0   FALSE   <
Is 15<=15?                                            15.0         15.0   TRUE    <=

Customer Credit Score                                    3
Customer Last Year Sales            \$          500,000.00

Company Credit Score Hurdle                              3

Company Last Year Sales Hurdle      \$         1,000,000.00
Rule 1: We have two logical tests
and both must be met before we
extend credit: AND function                 FALSE           AND

Rule 2: We have two logical tests
and "one or the other or both"
(also said in this way; 'at least
one') can be met before we
extend credit: OR function                  TRUE            OR

ISTEXT function            rad                      TRUE    TRUE    Value refers to text.
ISTEXT function                              23     FALSE   FALSE
ISNUMBER function                               12     TRUE    TRUE    Value refers to a number.
ISLOGICAL function                   TRUE             TRUE    TRUE    Value refers to a logical value.
ISBLANK function                                     TRUE    TRUE    Value refers to an empty cell.
ISERROR function                    #REF!            TRUE    TRUE    Value refers to any error value (#N/A, #VALUE!
ISERR function                    #N/A             FALSE   FALSE   Value refers to any error value except #N/A.
ISNA function                    #N/A             TRUE    TRUE    Value refers to the #N/A (value not available) e
ISNONTEXT function            Excel                    FALSE   FALSE   Value refers to any item that is not text. (Note

IF function can put one of two things in a cell. The IF
function has three parts:
1) Logical Test
2) Value If True
3) Value If False.
For the 'Value If True' and 'Value If False', you can put
numbers, text (must be in double quotes), formulas, or
cell ranges

This example puts one of two words (text) into a cell
Text or
Number
Text or Number          Formula
Big List                           Formula 1               2
word1                              Text                    Text
12 Number                  Number
word3                              Text                    Text
word4                              Text                    Text
5896 Number                   Number
word6                              Text                    Text
word7                              Text                    Text

This example puts one of two numbers into a cell
Do you get a bonus?
You get Bonus if you sell \$6,000.00 or more
Bonus Hurdle                                \$6,000.00
Bonus Amount                                  \$200.00

Put 1 of 2 Formulas in a cell
Revenues                                        \$6.00
Expenses                                    \$6,000.00
Net Loss                                    \$5,994.00

Conditional Formatting (Home Ribbon in 2007, Format menu in 2003) uses Logical Formulas
k cell, FORMULA, Blank from formula, error from formula
n is the content of B23 the same as C23? The answer is FALSE
example Case = case is TRUE to Excel.
>, >=, <, <=
ND function to deliver a TRUE to the cell or formula)
he OR function delivers a TRUE to the cell or formula)
and FALSE to TRUE
ons: ISTEXT function, ISNUMBER function, ISLOGICAL function,
SNONTEXT function
parts: 1) Logical Test, 2) Value If True, 3) Value If False
rror it puts something in the cell, otherwise it lets the formula

Comparative Operator
Equal
Not Equal
Great Than

Greater Than Or Equal To
Less Than
Less Than Or Equal To
ue refers to text.

ue refers to a number.

ue refers to a logical value.
ue refers to an empty cell.
ue refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ue refers to any error value except #N/A.
ue refers to the #N/A (value not available) error value.
ue refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
DIV/0!, #NUM!, #NAME?, or #NULL!).

unction returns TRUE if value refers to a blank cell.)
Account Not Suspended, No Overdue Balance and
Rule 1            Last Year's Sales > \$50,000.00
Account Not Suspended and Both Credit Hurdles
must be met and at least 1 of the remaining criteria
Rule 2                     must be met
Formula Inputs
Custome Accou Rating1 Rating2 Asset   Last    Over
r Name nt     Hurdle Hurdle Value     Year's Due
Yes        2.5     4.5 200,000 50,000 Yes
Customer Credit Analysis For Accounts Receivable

Accou                                          Over     Rule 1
nt                                   Last      Due      Account Not Suspended, No
Custome Suspe                Asset           Year's    Balan    Overdue Balance and Last
r Name nded? Rating1 Rating2 Value           Sales     ce       Year's Sales > \$50,000.00
Cust01              5     7.4 208,339         90,550
Cust02              5     5.9 374,775         51,255 Yes
Cust03            3.3     3.9 371,040         56,241
Cust04 Yes        1.2     2.4 331,439         69,920
Cust05 Yes        3.4     5.3 336,505         58,534 Yes
Cust06            4.5       6 336,794         60,423 Yes
Cust07            4.4     2.4 500,000         49,850
Cust08            2.9     5.5 375,800         62,100
Cust09            2.5     8.9 254,888         75,000
r Accounts Receivable
Rule 2
Account Not Suspended and
Both Credit Hurdles must be                   ONLY: Two
met and at least 1 of the                     rules are false,
remaining criteria must be NONE               but Asset
met                         Fail Both Rules   Hurdle Met         Decision
Account Not Suspended, No Overdue Balance and
Rule 1            Last Year's Sales > \$50,000.00
Account Not Suspended and Both Credit Hurdles
must be met and at least 1 of the remaining criteria
Rule 2                     must be met
Formula Inputs

Accou                                        Over
nt                                   Last    Due
Custome Suspe Rating1 Rating2 Asset          Year's Balan
r Name nded? Hurdle Hurdle Value             Sales   ce
Yes        2.5     4.5 200,000        50,000 Yes
Customer Credit Analysis For Accounts Receivable

Accou                                          Over     Rule 1
nt                                   Last      Due      Account Not Suspended, No
Custome Suspe Rating1 Rating2 Asset          Year's    Balan    Overdue Balance and Last
r Name nded? Hurdle Hurdle Value             Sales     ce       Year's Sales > \$50,000.00
Cust01               5     7.4 208,339        90,550                        TRUE
Cust02               5     5.9 374,775        51,255 Yes                    FALSE
Cust03             3.3     3.9 371,040        56,241                        TRUE
Cust04 Yes         1.2     2.4 331,439        69,920                        FALSE
Cust05 Yes         3.4     5.3 336,505        58,534 Yes                    FALSE
Cust06             4.5       6 336,794        60,423 Yes                    FALSE
Cust07             4.4     2.4 500,000        49,850                        FALSE
Cust08             2.9     5.5 375,800        62,100                        TRUE
Cust09             2.5     8.9 254,888        75,000                        TRUE
r Accounts Receivable
Rule 2
Account Not Suspended and
Both Credit Hurdles must be                   ONLY: Two
met and at least 1 of the                     rules are false,
remaining criteria must be None               but Asset
met                         Fail Both Rules   Hurdle Met         Decision
TRUE                  FALSE             FALSE        Approved
TRUE                  FALSE             FALSE        Approved
FALSE                  FALSE             FALSE        Take A Second Look
FALSE                  TRUE              TRUE         Reject
FALSE                  TRUE              TRUE         Reject
TRUE                  FALSE             FALSE        Approved
FALSE                  TRUE              TRUE         Reject
TRUE                  FALSE             FALSE        Approved
TRUE                  FALSE             FALSE        Approved
Is Account                          Credit           Credit                                   Past Year's
Suspended?      Customer            Rating 01        Rating 02        Asset Value             Sales
Not Suspended   Customer 01                      4                9              \$159,441         \$332,082
Not Suspended   Customer 02                      4                6              \$796,571         \$455,968
Not Suspended   Customer 03                      2                3             \$1,243,611        \$373,892
Suspended       Customer 04                      3                3             \$4,939,327        \$285,446
Not Suspended   Customer 05                      2                7             \$2,647,055        \$191,068
Not Suspended   Customer 06                      2                2             \$1,409,633        \$168,029
Not Suspended   Customer 07                      3                8             \$4,350,814        \$353,054
Not Suspended   Customer 08                      4                9             \$2,395,694        \$198,841
Not Suspended   Customer 09                      0                3             \$1,629,030        \$199,163
Not Suspended   Customer 10                      3                8              \$207,371         \$189,052
Suspended       Customer 11                      0                5             \$4,857,701        \$347,454
Not Suspended   Customer 12                      3                4             \$1,968,563          \$90,424
Not Suspended   Customer 13                      3                8             \$3,183,148        \$169,508
Not Suspended   Customer 14                      2                7             \$3,898,837        \$238,658
Not Suspended   Customer 15                      0                9             \$4,643,757        \$439,679

Assumptions
Credit Criteria Credit               Credit                                Credit
1               Criteria 2           Criteria 3       Credit Criteria 4    Criteria 5

Hurdle
Hurdle Credit       Credit           Hurdle                                   Current Over
Rating 01           Rating 02        Asset Value Past Year's Sales            Due Balance

2           4.5         \$200,000                    \$50,000              0

Low                             0                0
High                            4               10
Rule 1              Rule 2
Not Suspended, No   Both Credit Hurdles must
Overdue Balance     be met and at least 1 of NONE: 2     ONLY: Two rules are
Current Over        and Past sales >    the remaining criteria   rules not   false, but Asset
Due Balance         \$50,000.00          must be met              met)        Hurdle Met
0
1
1
1
0
0
1
0
1
1
1
1
1
0
1

Rule 1              Rule 2

Not Suspended, No   Both Credit Hurdles must
Overdue Balance     be met and at least 1 of
Is Account          and Past sales >    the remaining criteria
Suspended?          \$50,000.00          must be met

Suspended

Not Suspended
Is Account                          Credit           Credit                           Past Year's Current Over
Suspended?      Customer            Rating 01        Rating 02        Asset Value     Sales         Due Balance
Not Suspended   Customer 01                      4                9        \$159,441       \$332,082                  0
Not Suspended   Customer 02                      4                6        \$796,571       \$455,968                  1
Not Suspended   Customer 03                      2                3      \$1,243,611       \$373,892                  1
Suspended       Customer 04                      3                3      \$4,939,327       \$285,446                  1
Not Suspended   Customer 05                      2                7      \$2,647,055       \$191,068                  0
Not Suspended   Customer 06                      2                2      \$1,409,633       \$168,029                  0
Not Suspended   Customer 07                      3                8      \$4,350,814       \$353,054                  1
Not Suspended   Customer 08                      4                9      \$2,395,694       \$198,841                  0
Not Suspended   Customer 09                      0                3      \$1,629,030       \$199,163                  1
Not Suspended   Customer 10                      3                8        \$207,371       \$189,052                  1
Suspended       Customer 11                      0                5      \$4,857,701       \$347,454                  1
Not Suspended   Customer 12                      3                4      \$1,968,563         \$90,424                 1
Not Suspended   Customer 13                      3                8      \$3,183,148       \$169,508                  1
Not Suspended   Customer 14                      2                7      \$3,898,837       \$238,658                  0
Not Suspended   Customer 15                      0                9      \$4,643,757       \$439,679                  1

Assumptions
Credit Criteria Credit               Credit           Credit Criteria Credit
1               Criteria 2           Criteria 3       4               Criteria 5

Hurdle
Hurdle Credit       Credit           Hurdle      Past Year's          Current Over Is Account
Rating 01           Rating 02        Asset Value Sales                Due Balance Suspended?

2           4.5         \$200,000            \$50,000                0 Suspended

Low                             0                0                                                  Not Suspended
High                            4               10
Rule 1              Rule 2
Not Suspended, No   Both Credit Hurdles must
Overdue Balance     be met and at least 1 of NONE: 2     ONLY: Two rules are
and Past sales >    the remaining criteria   rules not   false, but Asset
\$50,000.00          must be met              met)        Hurdle Met
TRUE                   TRUE               FALSE           FALSE
FALSE                  TRUE               FALSE           FALSE
FALSE                 FALSE               TRUE             TRUE
FALSE                 FALSE               TRUE             TRUE
TRUE                   TRUE               FALSE           FALSE
TRUE                  FALSE               FALSE           FALSE
FALSE                  TRUE               FALSE           FALSE
TRUE                   TRUE               FALSE           FALSE
FALSE                 FALSE               TRUE             TRUE
FALSE                  TRUE               FALSE           FALSE
FALSE                 FALSE               TRUE             TRUE
FALSE                 FALSE               TRUE             TRUE
FALSE                  TRUE               FALSE           FALSE
TRUE                   TRUE               FALSE           FALSE
FALSE                 FALSE               TRUE             TRUE

Rule 1              Rule 2

Not Suspended, No   Both Credit Hurdles must
Overdue Balance     be met and at least 1 of
and Past sales >    the remaining criteria
\$50,000.00          must be met
Taxable Earnings
Name        Hours Wage    Gross      Beg Cumulative  End Cumulative UnEm
Employee 1      33  12.55     414.15       25,001.37       25,415.52
Employee 2      30  25.00     750.00       36,589.74       37,339.74
Employee 3      38  23.00     874.00      115,450.01      116,324.01
Employee 4      40  22.00     880.00        5,000.06        5,880.06
Employee 5      32  24.00     768.00        6,999.00        7,767.00
Employee 6      40  17.00     680.00        7,000.34        7,680.34
Employee 7      37  13.00     481.00        7,001.93        7,482.93
Employee 8      39  24.75     965.25       94,199.64       95,164.89
Employee 9      32  17.00     544.00       94,200.50       94,744.50
Employee 10     38  24.19     919.22       94,201.13       95,120.35
Employee 11     33  19.00     627.00       95,000.49       95,627.49
Employee 12     39  20.35     793.65      125,000.35      125,794.00
Employee 13     39  18.00     702.00          500.67        1,202.67
Employee 14     32  11.00     352.00        6,850.76        7,202.76
Employee 15     30  18.00     540.00       20,000.00       20,540.00
Employee 16     37  12.45     460.65       93,990.66       94,451.31
569 301.29  10,750.92      826,986.65      837,737.57             -

Assumptions
UnEm State      UnEm Fed
5.40%        0.08%
UnEm Fed Ceiling
7,000

105,000

73,900
67,000
62,000
57,000
55,000
5,000
Taxable Earnings                        Deductions
SS             Medicare      SS         Medicare Pension Total Ded. Net Pay     Expense
414.15       -         6.01    8.28      14.29     399.86      414.15
750.00       -       10.88    15.00      25.88     724.12      750.00
874.00       -       12.67    17.48      30.15     843.85      874.00
880.00       -       12.76    17.60      30.36     849.64      880.00
768.00       -       11.14    15.36      26.50     741.50      768.00
680.00       -         9.86   13.60      23.46     656.54      680.00
481.00       -         6.97    9.62      16.59     464.41      481.00
965.25       -       14.00    19.31      33.31     931.94      965.25
544.00       -         7.89   10.88      18.77     525.23      544.00
919.22       -       13.33    18.38      31.71     887.51      919.22
627.00       -         9.09   12.54      21.63     605.37      627.00
793.65       -       11.51    15.87      27.38     766.27      793.65
702.00       -       10.18    14.04      24.22     677.78      702.00
352.00       -         5.10    7.04      12.14     339.86      352.00
540.00       -         7.83   10.80      18.63     521.37      540.00
460.65       -         6.68    9.21      15.89     444.76      460.65
-       10,750.92        -      155.90   215.01    370.91  10,380.01    10,750.92

Assumptions
SS             Medicare      Pension
6.20%         1.45%         2%
SS Ceiling
105,000

CURRENT Pay check

23100
9000
6,900                               6900
5,000
5,000
2,000
50,000
5,000
Taxable Earnings
Name        Hours Wage    Gross      Beg Cumulative  End Cumulative UnEm
Employee 1      33  12.55     414.15       25,001.37       25,415.52         -
Employee 2      30  25.00     750.00       36,589.74       37,339.74         -
Employee 3      38  23.00     874.00      115,450.01      116,324.01         -
Employee 4      40  22.00     880.00        5,000.06        5,880.06      880.00
Employee 5      32  24.00     768.00        6,999.00        7,767.00        1.00
Employee 6      40  17.00     680.00        7,000.34        7,680.34         -
Employee 7      37  13.00     481.00        7,001.93        7,482.93         -
Employee 8      39  24.75     965.25       94,199.64       95,164.89         -
Employee 9      32  17.00     544.00       94,200.50       94,744.50         -
Employee 10     38  24.19     919.22       94,201.13       95,120.35         -
Employee 11     33  19.00     627.00       95,000.49       95,627.49         -
Employee 12     39  20.35     793.65      125,000.35      125,794.00         -
Employee 13     39  18.00     702.00          500.67        1,202.67      702.00
Employee 14     32  11.00     352.00        6,850.76        7,202.76      149.24
Employee 15     30  18.00     540.00       20,000.00       20,540.00         -
Employee 16     37  12.45     460.65       93,990.66       94,451.31         -
569 301.29  10,750.92      826,986.65      837,737.57    1,732.24

Assumptions
UnEm State      UnEm Fed
5.40%        0.08%
UnEm Fed Ceiling
7,000

106,000            1,000
105,000
82,900           22,100
73,900           23,100
67,000
62,000
57,000
55,000
5,000
Taxable Earnings                        Deductions
SS             Medicare      SS         Medicare Pension Total Ded. Net Pay     Expense
414.15         414.15     25.68       6.01    8.28      39.97     374.18      414.15
750.00         750.00     46.50     10.88    15.00      72.38     677.62      750.00
-           874.00       -       12.67    17.48      30.15     843.85      874.00
880.00         880.00     54.56     12.76    17.60      84.92     795.08      880.00
768.00         768.00     47.62     11.14    15.36      74.12     693.88      768.00
680.00         680.00     42.16       9.86   13.60      65.62     614.38      680.00
481.00         481.00     29.82       6.97    9.62      46.41     434.59      481.00
965.25         965.25     59.85     14.00    19.31      93.16     872.09      965.25
544.00         544.00     33.73       7.89   10.88      52.50     491.50      544.00
919.22         919.22     56.99     13.33    18.38      88.70     830.52      919.22
627.00         627.00     38.87       9.09   12.54      60.50     566.50      627.00
-           793.65       -       11.51    15.87      27.38     766.27      793.65
702.00         702.00     43.52     10.18    14.04      67.74     634.26      702.00
352.00         352.00     21.82       5.10    7.04      33.96     318.04      352.00
540.00         540.00     33.48       7.83   10.80      52.11     487.89      540.00
460.65         460.65     28.56       6.68    9.21      44.45     416.20      460.65
9,083.27     10,750.92     563.16    155.90   215.01    934.07    9,816.85   10,750.92

Assumptions
SS             Medicare      Pension
6.20%         1.45%         2%
SS Ceiling
105,000

CURRENT Pay check

23,100                             23100
9,000                              9000
6,900                              6900
5,000
5,000
2,000
50,000
5,000
Put 1 of 3 Percentages in a
Formula               Mutually Exclusive Categories Sales    %
Your Sales > = 10000           \$10,000 4.00%

Multiple IF Rule # 1: Be sure to start at top and go to bottom, or at the
bottom and go to the top
Multiple IF Rule # 2: If there are 3 possibilities, there are 2 IFs, If there are 4
possibilities, there are 3 IFs, etc.

VLOOKUP to Replace IF          Mutually Exclusive Categories Sales    %
Your Sales > = 10000           \$10,000 4.00%
Put 1 of 3 Percentages in a
Formula              Mutually Exclusive Categories Sales    %
Your Sales > = 10000           \$10,000 4.00%

Multiple IF Rule # 1: Be sure to start at top and go to bottom, or at the bottom
and go to the top
Multiple IF Rule # 2: If there are 3 possibilities, there are 2 IFs, If there are 4
possibilities, there are 3 IFs, etc.

VLOOKUP to Replace IF   Mutually Exclusive Categories Sales    %
Your Sales > = 10000           \$10,000 4.00%
1)                 Range means a group of cells such as B12:E12
2)              The Range B12:E12 can contain the values: 5, 7, 9, 11

An array (different than a range) means a group of things (values, text,
3)    TRUE/FALSE) stored in a range of cells or in an array using array syntax

Array syntax uses the: 1) curly bracket to contain the array, 2) comma
4)            for column and 3) semi-colon for row, 4) Text is in quotes
5)                           An array could be: {5, 7, 9, 11}
If the values did not need to be in cells, instead of =SUM(B12:E12), you
6)                     could use the formula =SUM({5, 7, 9, 11})
To create an array from a range of values, click in a cell, type the equal
sign, highlight a range of cells, then highlight the range of cells in the
formula, hit the F9 key, then Ctrl + C to copy the array with all the array
7)                                        syntax.

8)      An array could be: {''Week1'',''Week2'',''Week3'',''Week4'';5,7,9,11}

Instead of the formula, =VLOOKUP(B20,\$B\$15:\$C\$17,2,0), you could
use this (then the data is hard coded into formula),
=VLOOKUP(B20,{""Part1"",25;""Part2"",10;""Part3"",5},2,0). This
formula is not an Array Formula (because it does not use Ctrl + Shift +
9)        Enter to put formula in cell), but it is a formula that uses an array.

Week1          Week2            Week3       Week4               Total          Total
5                7           9                  11

Item           Price
Part1                  \$25.00
Part2                  \$10.00
Part3                   \$5.00

Product        Price
Part2

Different than regular formulas, array formulas can:
1) Do operations on arrays instead of individual cells

2) Allow you to enter functions such as TRANSPOSE, FREQUENCY and
MMULT, which are by definition array formulas.
3) Allow ranges or arrays in function arguments where the function is
expecting a single value
Array Formulas can do operations on arrays such as
10)                          =SUM(B35:B38*C35:C38)
In order to get Excel to recognize that your formula is an array formula,
you must use the key strokes Ctrl + Shift + Enter to put the formula in
11)                          the cell or range of cells.

When you use Ctrl + Shift + Enter to put an array formula into a cell or
12) range of cells Curley Brackets appear automatically around the formula.
If you enter =SUM(B35:B38*C35:C38), {=SUM(B35:B38*C35:C38)} will
13)                                   appear
of using an extra column to calculate sales for each transaction and then
adding the total for each transaction, you can simply use an array
14)                             formula in one cell.

In cell D40 create this formula: =SUM(B35:B38*C35:C38), then use Ctrl + Shift +
Enter to create the Array Formula. The result will look like
{=SUM(B35:B38*C35:C38)}. The curley brackets will be put in automatically.
Units         Price          Sales
5        \$22.00              What are we doing: multiply, then add
2        \$23.00              Array formula advantage: use less space
4        \$25.00              Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
6        \$24.50
Total                               \$403.00
Total                     Array formula in 1 cell
Total                     SUMPRODUCT multiplies the related ranges and then adds. SUMPRODUCT can b
To Enter Array Formula you must use keystrokes: Ctrl +
Shift + Enter

In cell D53 create this formula: =MIN(C46:C51-B46:B51), then use Ctrl + Shift +
Enter to create the Array Formula. The result will look like {=MIN(C46:C51-
B46:B51)}. The curley brackets will be put in automatically.

Sample       Sample
Weight Beg Weight End Change
1      10.00 lbs     11.20 lbs              What are we doing: subtracting then find MIN
2      12.50 lbs     13.00 lbs              Array formula advantage: use less space
3       9.00 lbs     10.00 lbs              Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
4      11.50 lbs     12.00 lbs
5      10.00 lbs     10.25 lbs
6      14.00 lbs     14.75 lbs

Array formula in 1 cell
To Enter Array Formula you must use keystrokes: Ctrl +
Shift + Enter
In cell D65 create this formula: =MAX(C58:C63-B58:B63), then use Ctrl + Shift +
Enter to create the Array Formula. The result will look like {=MAX(C58:C63-
B58:B63)}. The curley brackets will be put in automatically.

Stock \$ End Stock \$ Beg    Change
1             26            27                  What are we doing: subtracting then find MAX
2             28            19                  Array formula advantage: use less space
3             13            15                  Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
4             27            15
5             17            29
6             28          32.5

Array formula in 1 cell
To Enter Array Formula you must use keystrokes: Ctrl +
Shift + Enter
Weight in
Portfolio           0.333333         0.333333333 0.33333333

Economic    Probability         Stock1      Stock2               Stock3           Expected
State       of State            Return      Return               Return           Returns
Weak                      0.4         0.1                 0.15              0.2
Boom                      0.6        0.08                 0.04                0

Array formula in 1 cell
Don't forget Ctrl + Shift + Enter
Weight in
Portfolio             0.25                    0.75
Economic    Probability     Stock1     Stock2                    Expected
State       of State        Return     Return                    Returns
Weak                  0.55       -0.02                    0.05
Normal                  0.3     0.059                    0.065
Boom                  0.15        0.12                   0.085

Array formula in 1 cell Don't forget Ctrl + Shift
Does not require Ctrl + Shift + Enter
Example of SUMPRODUCT formula that will not work:                 #VALUE!         For SUMPRODUCT, you must use multiplication
If you want to multiply ranges and add, instead of the SUM function
with the keystrokes Ctrl + Shift = Enter, you can use the SUMPRODUCTS
function. This is not an array formula because we do not use Ctrl + Shift
+ Enter. SUMPRODUCT can handle arrays without Ctrl + Shift + Enter. If
the ranges used in SUMPRODUCT are the same size, use ranges and
commas. If the ranges used in SUMPRODUCT are NOT the same size,
15)                 use ranges and the multiplication symbol.
16)              See this video for more about SUMPRODUCT:                    Excel Formula Efficiency 1: SUMPRODUCT function 12 E

17)     For other videos and video series about array formulas, see these:      06 Excel Series: Array Formulas
Excel Magic Trick 313: Some Facts About Array Formula
07 Excel Formula Efficiency Series

Weight in
Portfolio             0.25                 0.75
Economic    Probability     Stock1     Stock1
State       of State        Return     Return
Weak                  0.55       -0.02                0.05
Normal                  0.3     0.059                0.065
Boom                  0.15        0.12               0.085

E. Returns
and larger array formulas may take a long time to calculate

ds. SUMPRODUCT can be used with commas when the ranges are the same size

and larger array formulas may take a long time to calculate
and larger array formulas may take a long time to calculate

Ctrl + Shift + Enter

Don't forget Ctrl + Shift + Enter
Shift + Enter
must use multiplication instead of ranges and commas because the size of the ranges/arrays are not the same.
SUMPRODUCT function 12 Examples

1)                 Range means a group of cells such as B12:E12
2)              The Range B12:E12 can contain the values: 5, 7, 9, 11

An array (different than a range) means a group of things (values, text,
3)    TRUE/FALSE) stored in a range of cells or in an array using array syntax

Array syntax uses the: 1) curly bracket to contain the array, 2) comma
4)            for column and 3) semi-colon for row, 4) Text is in quotes
5)                           An array could be: {5, 7, 9, 11}
If the values did not need to be in cells, instead of =SUM(B12:E12), you
6)                     could use the formula =SUM({5, 7, 9, 11})
To create an array from a range of values, click in a cell, type the equal
sign, highlight a range of cells, then highlight the range of cells in the
formula, hit the F9 key, then Ctrl + C to copy the array with all the array
7)                                        syntax.

8)      An array could be: {''Week1'',''Week2'',''Week3'',''Week4'';5,7,9,11}

Instead of the formula, =VLOOKUP(B20,\$B\$15:\$C\$17,2,0), you could
use this (then the data is hard coded into formula),
=VLOOKUP(B20,{""Part1"",25;""Part2"",10;""Part3"",5},2,0). This
formula is not an Array Formula (because it does not use Ctrl + Shift +
9)        Enter to put formula in cell), but it is a formula that uses an array.

Week1          Week2            Week3       Week4               Total            Total
5                7           9                  11            32              32

Item           Price                                                         1
Part1                  \$25.00                                                1
Part2                  \$10.00                              25                1
Part3                   \$5.00                                                3

Product        Price
Part2                      10
10

Different than regular formulas, array formulas can:
1) Do operations on arrays instead of individual cells

2) Allow you to enter functions such as TRANSPOSE, FREQUENCY and
MMULT, which are by definition array formulas.
3) Allow ranges or arrays in function arguments where the function is
expecting a single value
Array Formulas can do operations on arrays such as
10)                          =SUM(B35:B38*C35:C38)
In order to get Excel to recognize that your formula is an array formula,
you must use the key strokes Ctrl + Shift + Enter to put the formula in
11)                          the cell or range of cells.

When you use Ctrl + Shift + Enter to put an array formula into a cell or
12) range of cells Curley Brackets appear automatically around the formula.
If you enter =SUM(B35:B38*C35:C38), {=SUM(B35:B38*C35:C38)} will
13)                                   appear
of using an extra column to calculate sales for each transaction and then
adding the total for each transaction, you can simply use an array
14)                             formula in one cell.

In cell D40 create this formula: =SUM(B35:B38*C35:C38), then use Ctrl + Shift +
Enter to create the Array Formula. The result will look like
{=SUM(B35:B38*C35:C38)}. The curley brackets will be put in automatically.
Units         Price          Sales
5        \$22.00 \$110.00 What are we doing: multiply, then add
2        \$23.00      \$46.00 Array formula advantage: use less space
4        \$25.00 \$100.00 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
6        \$24.50 \$147.00
Total            \$403.00            \$403.00
Total            \$403.00 Array formula in 1 cell
Total            \$403.00 SUMPRODUCT multiplies the related ranges and then adds. SUMPRODUCT can b
To Enter Array Formula you must use keystrokes: Ctrl +
Shift + Enter

In cell D53 create this formula: =MIN(C46:C51-B46:B51), then use Ctrl + Shift +
Enter to create the Array Formula. The result will look like {=MIN(C46:C51-
B46:B51)}. The curley brackets will be put in automatically.

Sample       Sample
Weight Beg Weight End Change
1      10.00 lbs     11.20 lbs 1.20 lbs What are we doing: subtracting then find MIN
2      12.50 lbs     13.00 lbs 0.50 lbs Array formula advantage: use less space
3       9.00 lbs     10.00 lbs 1.00 lbs Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
4      11.50 lbs     12.00 lbs 0.50 lbs
5      10.00 lbs     10.25 lbs 0.25 lbs
6      14.00 lbs     14.75 lbs 0.75 lbs
0.25 lbs
0.25 lbs Array formula in 1 cell
To Enter Array Formula you must use keystrokes: Ctrl +
Shift + Enter
In cell D65 create this formula: =MAX(C58:C63-B58:B63), then use Ctrl + Shift +
Enter to create the Array Formula. The result will look like {=MAX(C58:C63-
B58:B63)}. The curley brackets will be put in automatically.

Stock \$ End Stock \$ Beg    Change
1             26            27        -1 What are we doing: subtracting then find MAX
2             28            19         9 Array formula advantage: use less space
3             13            15        -2 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
4             27            15        12
5             17            29       -12
6             28          32.5      -4.5
12
12 Array formula in 1 cell
To Enter Array Formula you must use keystrokes: Ctrl +
Shift + Enter
Weight in
Portfolio           0.333333        0.333333333 0.33333333

Economic     Probability         Stock1      Stock2            Stock3       Expected
State        of State            Return      Return            Return       Returns
Weak                       0.4         0.1              0.15            0.2      0.06
Boom                       0.6        0.08              0.04              0     0.024
0.084
0.084 Array formula in 1 cell
Don't forget Ctrl + Shift + Enter
Weight in
Portfolio             0.25                 0.75
Economic     Probability     Stock1     Stock2         Expected
State        of State        Return     Return         Returns
Weak                   0.55       -0.02           0.05    0.017875
Normal                   0.3     0.059          0.065       0.01905
Boom                   0.15        0.12         0.085 0.0140625
0.0509875
0.0509875 Array formula in 1 cell Don't forget Ctrl + Shift
0.0509875 Does not require Ctrl + Shift + Enter
Example of SUMPRODUCT formula that will not work:        #VALUE! For SUMPRODUCT, you must use multiplication
If you want to multiply ranges and add, instead of the SUM function
with the keystrokes Ctrl + Shift = Enter, you can use the SUMPRODUCTS
function. This is not an array formula because we do not use Ctrl + Shift
+ Enter. SUMPRODUCT can handle arrays without Ctrl + Shift + Enter. If
the ranges used in SUMPRODUCT are the same size, use ranges and
commas. If the ranges used in SUMPRODUCT are NOT the same size,
15)                 use ranges and the multiplication symbol.
16)              See this video for more about SUMPRODUCT:                    Excel Formula Efficiency 1: SUMPRODUCT function 12 E

17)     For other videos and video series about array formulas, see these:      06 Excel Series: Array Formulas
Excel Magic Trick 313: Some Facts About Array Formula
07 Excel Formula Efficiency Series

Weight in
Portfolio             0.25                 0.75
Economic    Probability     Stock1     Stock1
State       of State        Return     Return
Weak                  0.55       -0.02                0.05
Normal                  0.3     0.059                0.065
Boom                  0.15        0.12               0.085
-0.00275            0.020625
0.004425            0.014625
0.0045          0.0095625
E. Returns       0.050988           0.0509875
0.050988           0.0509875
and larger array formulas may take a long time to calculate

ds. SUMPRODUCT can be used with commas when the ranges are the same size

and larger array formulas may take a long time to calculate
and larger array formulas may take a long time to calculate

Ctrl + Shift + Enter

Don't forget Ctrl + Shift + Enter
Shift + Enter
must use multiplication instead of ranges and commas because the size of the ranges/arrays are not the same.
SUMPRODUCT function 12 Examples

SUMPRODUCT function
SUM = add ==> The "sum" of 3 + 4 = 7
PRODUCT = multiply ==> The "product" of 1 * 3 = 3
The SUMPRODUCT function multiplies arrays of the same size and then
adds the products                          note: The array arguments must have the same dim
The SUMPRODUCT function multiplies 1st, then adds                 SUMPRODUCT treats array entries that are no
SUMPRODUCT can deal with ranges or arrays
1 row by 2 column array * 1 row by 2 column array =
A8:B8*A9:B9 = 1*3+2*2 = 7
1           2
3           2
1*3+2*2 = 7
SUMPRODUCT(A8:B8,A9:B9) = 7. Notice
commas between arrays.
You can do this: SUMPRODUCT(A8:B8*A9:B9) = 7 but for large
spreadsheets it may take longer to calculate.

1          2
3   2
1*3+2*2 = 7
SUMPRODUCT(A14:B14,C15:D15) = 7.
Notice commas between arrays.

1          2                    3
2
The array arguments must have the same dimensions. If they do not,
SUMPRODUCT returns the #VALUE! error value.
SUMPRODUCT(A19:B19,D19:D20) will not
work because the arrays are not the same
dimensions. The dimensions are 1 by 2 * 2
by 1.

You can get around this by using a
multiplication symbol instead of a comma.
SUMPRODUCT(A19:B19*D19:D20)
This is what it did: 1*3+2*3+1*2+2*2 =

Rule about when to use comma: When ranges/arrays are same
dimensions
Rule about when to use multiplication symbol: When ranges/arrays
are different dimensions

SUMPRODUCT is great for dealing with arrays of TRUEs and FALSEs
when you are doing conditional adding
Although you can use *1, /1, +0 to convert TRUEs & FALSEs to 1s and
0s, the method that calculates fastest is double negative --.
Month                                         Feb
Year                                                2009
Sales Rep                                     Julie
Count Records
Date                                          SalesRep # Phone Calls
1/2/2009 Mo                        5 SUMIFS cannot do this because SUMIFS cannot handl
2/1/2009 Julie                     2 =SUMIFS(C38:C45,--(TEXT(A38:A45,"mmmyyyy")),B3
1/15/2009 Mo                        5
2/1/2009 Mo                        3
1/30/2009 Julie                     2
2/15/2009 Julie                     2
2/15/2009 Mo                        3
1/28/2009 Julie                     3

SUMPRODUCT can deal with arrays, whereas the SUMIFS function can
not (our TEXT example is proof of this). Note: SUMIFS see workbook
references as arrays and so it is best to use SUMPRODUCT for
conditional adding when you have workbook references.

See this video for more about SUMPRODUCT and SUMIF:
Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays
must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
s array entries that are not numeric as if they were zeros.
Months   Years        SalesRep
Jan              2009 Mo
Feb                   Julie

use SUMIFS cannot handle arrays.
38:A45,"mmmyyyy")),B32&B33,B38:B45,B34)
SUMPRODUCT function
SUM = add ==> The "sum" of 3 + 4 = 7
PRODUCT = multiply ==> The "product" of 1 * 3 = 3
The SUMPRODUCT function multiplies arrays of the same size and then
adds the products                          note: The array arguments must have the same dim
The SUMPRODUCT function multiplies 1st, then adds                 SUMPRODUCT treats array entries that are no
SUMPRODUCT can deal with ranges or arrays
1 row by 2 column array * 1 row by 2 column array =
A8:B8*A9:B9 = 1*3+2*2 = 7
1           2
3           2
1*3+2*2 = 7                                          7
SUMPRODUCT(A8:B8,A9:B9) = 7. Notice
commas between arrays.                               7            7
You can do this: SUMPRODUCT(A8:B8*A9:B9) = 7 but for large
spreadsheets it may take longer to calculate.

1          2
3   2
1*3+2*2 = 7
SUMPRODUCT(A14:B14,C15:D15) = 7.
Notice commas between arrays.                      7

1          2                    3
2
The array arguments must have the same dimensions. If they do not,
SUMPRODUCT returns the #VALUE! error value.
SUMPRODUCT(A19:B19,D19:D20) will not
work because the arrays are not the same
dimensions. The dimensions are 1 by 2 * 2
by 1.                                     #VALUE!

You can get around this by using a
multiplication symbol instead of a comma.
SUMPRODUCT(A19:B19*D19:D20)                       15
This is what it did: 1*3+2*3+1*2+2*2 =            15

Rule about when to use comma: When ranges/arrays are same
dimensions
Rule about when to use multiplication symbol: When ranges/arrays
are different dimensions

SUMPRODUCT is great for dealing with arrays of TRUEs and FALSEs
when you are doing conditional adding
Although you can use *1, /1, +0 to convert TRUEs & FALSEs to 1s and
0s, the method that calculates fastest is double negative --.
Month                                         Feb
Year                                                2009
Sales Rep                                     Julie
Count Records                                          2
Date                                          SalesRep # Phone Calls
1/2/2009 Mo                        5 SUMIFS cannot do this because SUMIFS cannot handl
2/1/2009 Julie                     2 =SUMIFS(C38:C45,--(TEXT(A38:A45,"mmmyyyy")),B3
1/15/2009 Mo                        5
2/1/2009 Mo                        3
1/30/2009 Julie                     2
2/15/2009 Julie                     2
2/15/2009 Mo                        3
1/28/2009 Julie                     3

SUMPRODUCT can deal with arrays, whereas the SUMIFS function can
not (our TEXT example is proof of this). Note: SUMIFS see workbook
references as arrays and so it is best to use SUMPRODUCT for
conditional adding when you have workbook references.

See this video for more about SUMPRODUCT and SUMIF:
Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays
must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
s array entries that are not numeric as if they were zeros.
Months   Years        SalesRep
Jan              2009 Mo
Feb                   Julie

use SUMIFS cannot handle arrays.
38:A45,"mmmyyyy")),B32&B33,B38:B45,B34)
Array Functions must be entered as array formulas with the key strokes Ctrl +
1)                                  Shift + Enter

TRANSPOSE (turns flips the range 90 degrees) is an array function
that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell
must be highlighted before you create the formula.

In the case of TRANSPOSE you must count the number of columns
and rows in the original and then highlight a similar range, but
flipped 90 degrees ==> 4 x 2 becomes 2 x 4.
Item                  Price
Part1                    \$50.00
Part2                    \$10.00
Part3                     \$5.00

To Enter Array Formula you must use
keystrokes: Ctrl + Shift + Enter
FREQUENCY is an array function that: 1) must be entered using Ctrl + Shift +
Enter 2) All the cell must be highlighted before you create the formula. The
FREQUENCY function uses two arguments: data_array and bins_array (see
below)

Data_array                      Bins_array (Upper
(Numbers to be                  Limit of each
counted)                        class)
5                            10 <= 10
9                            20 10 > and <= 20
10                            30 20 > and <= 30
15
To Enter Array Formula you must use
22                    keystrokes: Ctrl + Shift + Enter
21
4             Some Other Array Functions and videos to watch:
FREQUENCY         Excel Array Formula Series #8: FREQUENCY function
TRANSPOSE         Excel Array Formula Series #2: TRANSPOSE function
MMULT             Excel Array Formula Series 12: MMULT function Matrix Algebra
ROW               Excel Array Formula Series #11: ROW & COLUMN functions
COLUMN            Excel Array Formula Series #11: ROW & COLUMN functions
MINVERSE
LINEST            Excel Array Formula Series #13: LINEST function
UENCY function
SPOSE function
LT function Matrix Algebra
W & COLUMN functions
W & COLUMN functions
Array Functions must be entered as array formulas with the key strokes Ctrl +
1)                                  Shift + Enter

TRANSPOSE (turns flips the range 90 degrees) is an array function
that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell
must be highlighted before you create the formula.

In the case of TRANSPOSE you must count the number of columns
and rows in the original and then highlight a similar range, but
flipped 90 degrees ==> 4 x 2 becomes 2 x 4.
Item                  Price
Part1                    \$25.00
Part2                    \$10.00
Part3                     \$5.00

Item                  Part1    Part2                Part3
Price                   \$25.00             \$10.00             \$5.00

To Enter Array Formula you must use
keystrokes: Ctrl + Shift + Enter
FREQUENCY is an array function that: 1) must be entered using Ctrl + Shift +
Enter 2) All the cell must be highlighted before you create the formula. The
FREQUENCY function uses two arguments: data_array and bins_array (see
below)

Data_array                      Bins_array (Upper
(Numbers to be                  Limit of each
counted)                        class)
5                            10 <= 10                         4
9                            20 10 > and <= 20                1
10                            30 20 > and <= 30                2
15
To Enter Array Formula you must use
22                    keystrokes: Ctrl + Shift + Enter
21
4             Some Other Array Functions and videos to watch:
FREQUENCY         Excel Array Formula Series #8: FREQUENCY function
TRANSPOSE         Excel Array Formula Series #2: TRANSPOSE function
MMULT             Excel Array Formula Series 12: MMULT function Matrix Algebra
ROW               Excel Array Formula Series #11: ROW & COLUMN functions
COLUMN            Excel Array Formula Series #11: ROW & COLUMN functions
MINVERSE
LINEST            Excel Array Formula Series #13: LINEST function
UENCY function
SPOSE function
LT function Matrix Algebra
W & COLUMN functions
W & COLUMN functions
If you enter a range of values or an array into a function argument that is
expecting a single value:
1) You must use Ctrl + Shift + Enter to put the formula in the cell
2) You will return an array of values. If the function is in one cell, it will only
show the first value in the array. If the function is in a formula or function
1)                        that expects an array, the whole array will be used.

TEXT function with custom number
format to count Dates in Jan 2009
Criteria 1 Jan
Criteria 2                       2009
Count                               3
Count                               3
Sum Sales                          10
Sum Sales                          10

Dates      Sales
1/25/2009                       \$5.00
2/20/2009                       \$2.00
7/1/2009                       \$5.00
5/2/2009                       \$6.00
1/2/2009                       \$1.00
1/18/2009                       \$4.00
4/23/2009                       \$2.00

Date         Item                        VLOOUP Price
5/5/2009   Part1                                                     Item         Price
5/6/2009   Part2                                                     Part1           \$25.00
5/7/2009   Part2                                                     Part2           \$10.00
5/8/2009   Part3                                                     Part3            \$5.00
5/9/2009   Part1
Total
Total
Total

SalesRep     Sales
Joe                              \$5.00
Sioux                            \$2.00
Joe                              \$5.00
Sioux                            \$6.00
Joe                              \$1.00
Joe                              \$4.00
Sioux                            \$2.00

criteria     Joe
MEDIAN                                                             =MEDIAN(IF(A31:A37=B39,B31:B37))
MAX                                                                =MAX(IF(A31:A37=B39,B31:B37))
MIN                                                                =MIN(IF(A31:A37=B39,B31:B37))

Names         Count Unique Records
Tom                                                                =SUMPRODUCT(--(A45:A51<>""),1/COUNTIF(A45:A51,A45:
Sioux
Sue
Sioux

Jo
Mo

2)                                           Array Formulas: Add the 3 highest values
3)                                     Remember to enter array formula with Ctrl + Shift + Enter
4)                                         The formula is: =SUM(LARGE(D11:H11,{1,2,3}))

Boomerang Maximun Time Aloft
Throw 1                     Throw 2       Throw 3 Throw 4 Throw 5 Sum Top 3
Thrower 1                          35.1            42.3       43     16.5   22.15
Thrower 2                          42.3            35.1     16.5    22.15      43
Thrower 3                          6.87            38.2     47.8    27.76   20.34
Thrower 4                         22.15            16.5       43     42.3    35.1
Thrower 5                         26.36            49.8    11.97    27.46   19.96
Thrower 6                         26.36           42.85    45.62    24.54    3.98
Thrower 7                         28.18           17.03    35.21    32.38    6.66
Thrower 8                         10.82           39.95    27.78     2.05    54.3
Thrower 9                          35.1            42.3     16.5       43   22.15
Thrower 10                         8.47           32.62    11.95    13.07   16.36

In large spreadsheets array formulas can slow
calculating speed down considerably.
Series:
07 Excel Formula Efficiency Series
=SUM((TEXT(A12:A18,"mmmyyyy")=B4&B5)*B12:B18)
=SUMPRODUCT(--(TEXT(A12:A18,"mmmyyyy")=B4&B5),B12:B18)

=SUM(SUMIF(E22:E24,B21:B25,F22:F24))
=SUMPRODUCT(SUMIF(E22:E24,B21:B25,F22:F24))
B39,B31:B37))

5:A51<>""),1/COUNTIF(A45:A51,A45:A51&""))

+ Enter
))

Rank
=SUM(LARGE(B61:F61,{1,2,3}))
If you enter a range of values or an array into a function
argument that is expecting a single value:
1) You must use Ctrl + Shift + Enter to put the formula in the
cell
2) You will return an array of values. If the function is in one
cell, it will only show the first value in the array. If the
function is in a formula or function that expects an array, the
1)                                  whole array will be used.

TEXT function with
custom number format
to count Dates in Jan
2009
Criteria 1 Jan
Criteria 2        2009
Count                 3
Count                 3
Sum Sales            10                                                          =SUM((TEXT(A12:A18,"mmmyyyy")=
Sum Sales            10                                                          =SUMPRODUCT(--(TEXT(A12:A18,"m
Jan2009
Dates      Sales
1/25/2009     \$5.00
2/20/2009     \$2.00
7/1/2009     \$5.00
5/2/2009     \$6.00
1/2/2009     \$1.00
1/18/2009     \$4.00
4/23/2009     \$2.00

Date         Item         VLOOUP Price
5/5/2009   Part1              \$25.00                Item         Price
5/6/2009   Part2              \$10.00                Part1           \$25.00
5/7/2009   Part2              \$10.00                Part2           \$10.00
5/8/2009   Part3               \$5.00                Part3            \$5.00
5/9/2009   Part1              \$25.00
Total              \$75.00
Total              \$75.00                                           =SUM(SUMIF(E22:E24,B21:B25,F22:F
Total              \$75.00                                           =SUMPRODUCT(SUMIF(E22:E24,B21

SalesRep     Sales
Joe              \$5.00
Sioux            \$2.00
Joe              \$5.00
Sioux            \$6.00
Joe              \$1.00
Joe              \$4.00
Sioux            \$2.00
criteria     Joe
MEDIAN              4.5                             =MEDIAN(IF(A31:A37=B39,B31:B37))
MAX                   5                             =MAX(IF(A31:A37=B39,B31:B37))
MIN                   1                             =MIN(IF(A31:A37=B39,B31:B37))

Names        Count Unique Records
Tom                  5                              =SUMPRODUCT(--(A45:A51<>""),1/COUNTIF(A45:A51,A45:A51&""))
Sioux
Sue
Sioux

Jo
Mo

2)                                   Array Formulas: Add the 3 highest values
3)                             Remember to enter array formula with Ctrl + Shift + Enter
4)                                 The formula is: =SUM(LARGE(D11:H11,{1,2,3}))

Boomerang Maximun Time Aloft
Throw 1      Throw 2       Throw 3 Throw 4 Throw 5 Sum Top 3 Rank
Thrower 1         35.1             42.3        43    16.5      22.15   120.4                  3.5
Thrower 2         42.3             35.1      16.5   22.15          43  120.4                  3.5
Thrower 3         6.87             38.2      47.8   27.76      20.34  113.76                    7
Thrower 4        22.15             16.5        43    42.3        35.1  120.4                  3.5
Thrower 5        26.36             49.8     11.97   27.46      19.96  103.62                    8
Thrower 6        26.36            42.85     45.62   24.54        3.98 114.83                    6
Thrower 7        28.18            17.03     35.21   32.38        6.66  95.77                    9
Thrower 8        10.82            39.95     27.78    2.05        54.3 122.03                    1
Thrower 9         35.1             42.3      16.5      43      22.15   120.4                  3.5
Thrower 10        8.47            32.62     11.95   13.07      16.36   62.05                  10

can slow calculating speed down
considerably.
this video Series:
07 Excel Formula Efficiency Series
XT(A12:A18,"mmmyyyy")=B4&B5)*B12:B18)
ODUCT(--(TEXT(A12:A18,"mmmyyyy")=B4&B5),B12:B18)

MIF(E22:E24,B21:B25,F22:F24))
ODUCT(SUMIF(E22:E24,B21:B25,F22:F24))
(A45:A51,A45:A51&""))

=SUM(LARGE(B61:F61,{1,2,3}))
Succeeding in Business with MS Excel 2007, ISBN 978-1-4239-0605-6, Authors:
Gross, Akaiwa, Nordquist
PO527 Credit Analysis HW P242-243
WWC Hiring Analysis HW P276-278
Unpaid Invoice Penalties P257-259, Painting Job Estimator P 278-280
605-6, Authors:

P 278-280
04839dba-f672-4ee9-9af3-2bc2c09774cd.xls - P214-241 - Level 3

Rule #1                                      Rule #2                      Rule #3
Reject                                  FALSE                                     FALSE                     FALSE
Further Evaluate                        TRUE                                      FALSE                     FALSE
OR
FALSE                                     TRUE                      FALSE
FALSE                                     FALSE                     TRUE
FALSE                                     TRUE                      TRUE
Inside an or
Accept                                   TRUE                                     TRUE                      TRUE
TRUE                                     FALSE                     TRUE
TRUE                                     TRUE                      FALSE
Accounts Receivable Department - Custo

D&B
D&B     Composite
Credit     Credit
Current    Past Due   Net Worth     Rating   Appraisal (1
Customer Name              Current Credit Limit   Previous Year's Sales   Year's Sales Balance     in (000)     Class      Best)
Athletic Gear Corp.    \$                  9,000   \$             15,382    \$ 11,952 \$      0       \$       450    BA           4
Baltimore O's                            39,000                 10,033       7,789        0             1,950    3A           1
Baseball & More                          75,000                 60,009      55,342   13,892            37,500    4A           2
Canadian Ski Club                        33,000                 35,039      50,921      495             1,650    BA           2
Concord Pro Shop                                                                                       10,000    4A           1
Everything Golf                         25,000                  15,221        9,483      2,899          1,250    3A           3
Lake Pro Shops                          42,000                  80,498       81,126          0          2,100    3A           2
Mars Dept. Store                        27,000                  35,354       20,666          0            213    BB           3
RG Bradley                              46,000                  90,970       18,343          0          2,300    3A           1
RX for Sports                           15,000                   5,663        3,014          0            750    2A           1
School Sports Supply                    45,000                  50,278       32,338          0          2,250    3A           3
Ski World                               26,000                  25,864       28,154          0            300    BA           2
Sneaker Kingdom                         45,000                  40,157       25,379          0          2,250    3A           2
Sports & Stuff                          15,000                  15,898       14,732     14,383            450    BA           1
Toy Kingdom                             22,000                  10,073        1,047          0          1,100    3A           3
Under the Sea                           45,000                  95,411       64,418          0            150    CB           4
US Olympic Team                         20,000                   5,621        6,171          0          1,000    3A           1
WWW Sports Inc.                        100,000                  60,009       60,354          0        500,000    5A           2
Zip & Sons                              10,000                  15,490       22,760          0            620    1A           2

Formatting Rules
Net worth is less
than
Rule 1                 \$1,000,000.00              \$   1,000,000.00
Net worth is
greater than
Rule 2                 \$10,000,000.00             \$ 10,000,000.00
vable Department - Customer Credit Analysis

Rule 1 (FALSE is Reject):   Rule 2 (FALSE is Reject):
D&B                               Past due balance is less    Composite credit appraisal
PAYDEX      D&B Stress Risk Class   than 10% of this year's     value of 1 OR a PAYDEX
(100 Best)         (1 Best)          total sales                 score over 90
15                 3                          TRUE                       FALSE
51                 1                          TRUE                       TRUE
70                 1                          FALSE                      FALSE
43                 1                          TRUE                       FALSE
91                 1                                                     TRUE
76                 1                        FALSE                        FALSE
87                 1                        TRUE                         FALSE
94                 1                        TRUE                         TRUE
21                 1                        TRUE                         TRUE
59                 1                        TRUE                         TRUE
91                 1                        TRUE                         TRUE
82                 1                        TRUE                         FALSE
71                 1                        TRUE                         FALSE
67                 1                        FALSE                        TRUE
14                 1                        TRUE                         FALSE
79                 2                        TRUE                         FALSE
87                 1                        TRUE                         TRUE
97                 1                        TRUE                         TRUE
96                 1                        TRUE                         TRUE
Rule 3 (FALSE is Reject): Net worth of
at least \$500,000.00 AND a composite
credit appraisal value less than or
equal to 2 AND PAYDEX score over 70      Reject? Further Evaluate?
AND a stress risk class of 1             Accept?
FALSE                   Accept
FALSE                   FurtherEvaluate
FALSE                   Reject
FALSE                   Accept
TRUE                   FurtherEvaluate
FALSE                   Reject
TRUE                   FurtherEvaluate
FALSE                   FurtherEvaluate
FALSE                   FurtherEvaluate
FALSE                   FurtherEvaluate
FALSE                   FurtherEvaluate
FALSE                   Accept
TRUE                   FurtherEvaluate
FALSE                   FurtherEvaluate
FALSE                   Accept
FALSE                   Accept
TRUE                   FurtherEvaluate
TRUE                   FurtherEvaluate
TRUE                   FurtherEvaluate
PO 527 Bidder List Financial Evalu

D&B
Composite
Credit
Net Worth       Appraisal (1
Bidder List                   Previous Experience Grade                     (Dollars)        Best)
Logistic SB Inc.     None                                                     \$ 2,500,000                 2
XBE Corporation      Satisfactory                                              237,000,000                1
Software Solutions   Satisfactory                                                 1,823,000               3
Exceed RW            Unsatisfactory                                                  75,000               2
Roberts Wise Inc.    Satisfactory                                                   680,000               3

Did any evaluate to TRUE?
Did all not evaluate to TRUE? Or Did all come

Assumptions
Rule 1               D&B Stress Risk Class (1 Best) Hurdle = 1                                1
D&B PAYDEX (100 Best) is greater than 60 AND D&B
Composite Credit Appraisal (1 Best) is less than or equal
to 2 AND Net Worth (Dollars) are greater than
Rule 2               \$1,000,000.00                                                          60              2
D&B PAYDEX (100 Best) is greater than or equal to 85
Rule 3               OR Previous Experience Grade is Satisfactory                           85 Satisfactory
Format if TRUE = Green                                        TRUE
Format if FALSE = Grey                                       FALSE
Format Bidder name who had Previous Experience

Note:
Conditional formatting in column A is based on values in Column B. The formula is: =B3=\$C\$18
er List Financial Evaluation

Rule 2: D&B PAYDEX (100 Best)
is greater than 60 AND D&B
Rule 1: D&B
Composite Credit Appraisal (1      Rule 3: D&B PAYDEX (100 Best)
D&B Stress Stress Risk   Best) is less than or equal to 2 AND is greater than or equal to 85 OR
D&B PAYDEX Risk Class Class (1 Best)    Net Worth (Dollars) are greater than   Previous Experience Grade is
(100 Best)     (1 Best) Hurdle = 1                 \$1,000,000.00                        Satisfactory
85      1          TRUE                 TRUE                                TRUE
90      1          TRUE                 TRUE                                TRUE
55      2          FALSE                FALSE                               TRUE
38      1          TRUE                 FALSE                               FALSE
43      1          TRUE                 FALSE                               TRUE

TRUE                   TRUE                                TRUE
to TRUE? Or Did all come out false?       FALSE                  FALSE                               FALSE

\$ 1,000,000.00
TheZone Customer Accounts - Credit & Payme

60-Days     90-Days
Customer Name                                     Customer Type      Current Balance Due       30-Days Past Due   Past Due    Past Due
Athletic Gear Corp.                                      A           \$            8,612    \$                  0   \$       0   \$       0
Baltimore O's                                            B                            0                       0           0           0
Baseball & More                                          A                        2,345                   3,473       5,557       4,862
Canadian Ski Club                                        C                            0                     345           0         150
Everything Golf                                          A                            0                       0       2,000         899
Sports & Stuff                                           A                            0                  14,000         383           0

Assumptions
Credit Approved
Credit Denied

Note: Order for non-mutually exclusive criteria matters here because you can have balances in all three categories (not mutua
and so you must test

Minimum before          Minimum               Surcharge % of 90
applying surcharge     surcharge             day balance
If the 90-Days Past Due balance is greater
than \$200.00 then the large of \$100.00 or
10% of 90 day balance                         \$             200      \$             100                 10.00%
Fee for 30-Days        Fee for 60-Days       Fee for 90-Days
Past Due               Past Due              Past Due
Alternative Penalty Scheme                    \$               25     \$               50    \$              100
Past Due               Past Due              Past Due
Days                                                           30                     60                    90

This example is of criteria that is mutually exclusive, the order of the logical tests does not matter.

Late Fee             Cust Type
A Cust Type Late Fee                                            50 A
B Cust Type Late Fee                                           100 B
C Cust Type Late Fee                                           150 C

VLOOKUP if often better than many Ifs. But if you use Ifs, better to go from low to high or high to low instead of betw
Hurdle                 Penalty
Level 1: less tha \$1,000.00                  \$              1,000   \$               25
Level 2: less than \$5,000.00 and greater
than or equal to \$1,000.00                    \$              1,000   \$               50
Level 3 greater than \$5,000.00               \$              5,000   \$              100

Past Due                                       =IF(A40<B\$33,C\$33,IF(A40<B\$35,C\$34,C\$35))                           =IF(A40>B\$35,C\$35,IF(A40>B\$34,C\$34,C\$3
\$                                       670   \$          25.00                                                    \$ 25.00
\$                                       141   \$          25.00                                                    \$ 25.00
\$                                     3,893   \$          50.00                                                    \$ 50.00
\$                                     4,741   \$          50.00                                                    \$ 50.00
\$                                     8,434   \$         100.00                                                    \$ 100.00
\$                                     7,401   \$         100.00                                                    \$ 100.00
\$   4,819   \$    50.00   \$ 50.00
\$   8,344   \$   100.00   \$ 100.00
counts - Credit & Payment Status
If the 90-Days Past   Alternative
Due balance is      Penalty
Current Credit Status (FALSE
greater than \$200.00 Scheme: 30 =
means that they have been                           then the large of   \$25.00; 60 =      Customer
Total Past  denied credit but may still   Credit Approved    \$100.00 or 10% of 90 \$50.00; 90 =       Type Late
Due Balance            owe \$)              or Credit Denied         day balance        \$100.00           Fee
\$        0           TRUE                  Credit Approved   \$                  -   \$         -     \$       -
0           TRUE                  Credit Approved   \$                  -   \$         -     \$       -
13,892           TRUE                  Credit Approved   \$                  486 \$        100    \$        50
495           TRUE                  Credit Approved   \$                  -   \$        100    \$       150
2,899           FALSE                  Credit Denied    \$                  100 \$        100    \$        50
14,383           FALSE                  Credit Denied    \$                  -   \$          50   \$        50

all three categories (not mutually exclusive),

al tests does not matter.

h or high to low instead of between.

>B\$35,C\$35,IF(A40>B\$34,C\$34,C\$33))                       =IF(AND(A40>=B\$34,A40<B\$35),C\$34,IF(A40<B\$33,C\$33,C\$35))
\$          25.00
\$          25.00
\$          50.00
\$          50.00
\$         100.00
\$         100.00
\$    50.00
\$   100.00
Fixed Fee & 90 Day Penalties                             Graduated Penalties - Range                     Penalty
Fixed Fee Penalty                \$               50             Graduated penalty <                    \$    1,000   \$         0
Hurdle for penalty               \$           100                Graduated penalty >=1000 but <              5,000       100
90-day penalty percentage                    10%                Graduated penalty >=                        5,000       200

TheZone Accounts Payable - Outstanding Balance Report
Applies                                          Total Past
Fixed      30-Days       60-Days 90-Days          Due         Fixed
Vendor Name                      Category             Penalty    Past Due      Past Due Past Due        Balance     Penalty
RTF Electric                     Utilities            TRUE      \$     16,254   \$       0   \$      0    \$   16,254   \$    50
Ross County Water & Sewer        Utilities            FALSE            1,435           0          0         1,435   \$         0
YNC Trucking                     Transportation       TRUE                 0           0       3,300        3,300   \$    50
Italian Leather Group Ltd.       Raw Materials        TRUE             6,756           0       5,674       12,430   \$    50
Union Plastics                   Raw Materials        TRUE                 0       436            0          436    \$    50
Freight to Go                    Transportation       FALSE                0           0        873          873    \$         0
Temps R'Us                       Labor                FALSE            2,700           0          0         2,700   \$         0
Notworth Telephone               Telephone            TRUE                 0           0       2,345        2,345   \$    50
Category Penalties
Utilities                  10%
Labor                      15%
Other                       5%

nce Report

Penalty    Penalty              Penalty
\$         0   \$           200   \$       1,625
0               100            144
330               100            165
567               200            622
0                0              22
87                0              44
0               100            405
235               100            117
Appl
Personal
College         Major                                        Interview       Work
Name                          GPA             Code          Refer-ences                    Rating          Experience
Michael                                 3.6             2                              3               5      TRUE
Brian                                   3.9            18                              3               3      FALSE
George                                  2.1            15                              3               2      FALSE
Jasen                                   3.0            30                              3               5      TRUE
Cindy                                   3.2           100                              4               3      TRUE
Sara                                    3.7             4                              3               6      TRUE
Courtney                                4.0            16                              5               4      FALSE
Scott                                   2.1           260                              2               2      FALSE
Mark                                    2.8            20                              3               5      FALSE

Assumptions
Job Applicants must supply the
following information                                     Automatically disqualified                              Automa
Valid GPA min                      1.5                             If any of these are TRUE                             If all of the
Valid GPA max                        4                      Valid GPA min                          1.5
Standardized universal
Major Code                                                  Valid GPA max                              4
Valid Employment Exam
Engineering                              1                  Score min                              200

Valid Employment Exam

Valid Standardized
Economics                                3                  universal Major Code min                   1

Valid Standardized
Physical Science                      4                     universal Major Code max               250
Last one                            250                     GPA less than                           2.5

Reference letters less than              3                  Reference letters less than                3
Personal Interview rating                                   Employment Exam less
less than                                3                  than                                   600
Personal Interview rating
Work Experience                 TRUE           FALSE        less than                                  3
Valid Employment Exam
Score min                           200
Valid Employment Exam
Score max                           800
ranking

Automatically disqualified
Automatically hired
Undecided   If the applicant does not fit into one of the other categories
Application for Employment
School         InvalidScores/                                 Automatically
Employment ExamScore              Rank           Major?             Automatically Disqualified Hired
700            12        FALSE                   FALSE                  FALSE
850             1         TRUE                   TRUE                   FALSE
780             5        FALSE                   TRUE                   FALSE
710             3        FALSE                   FALSE                  FALSE
600            26        FALSE                   FALSE                  FALSE
740            15        FALSE                   FALSE                  TRUE
720             8        FALSE                   FALSE                  FALSE
590            33         TRUE                   TRUE                   FALSE
760             6        FALSE                   FALSE                  FALSE
Total                           2                           3               1
Total                           2                           3               1
All are invalid                                      FALSE                   FALSE                  FALSE
Any are invalid?                                     TRUE                    TRUE                   TRUE

Automatically hired
If all of these are TRUE
GPA score greater than                     3.5

Major Code min                              1

Major Code max                             20

ranking less than or equal to              25

Employment Exam greater
than                                       700

Personal Interview rating
greater than or equal to                    4
Work Experience                     TRUE
Not Automatically       Not Automatically
Disqualified            Hired                   No Decision       No Decision
TRUE                    TRUE                  TRUE              TRUE
FALSE                   TRUE                  FALSE            FALSE
FALSE                   TRUE                  FALSE            FALSE
TRUE                    TRUE                  TRUE              TRUE
TRUE                    TRUE                  TRUE              TRUE
TRUE                    FALSE                 FALSE            FALSE
TRUE                    TRUE                  TRUE              TRUE
FALSE                   TRUE                  FALSE            FALSE
TRUE                    TRUE                  TRUE              TRUE
6                       8                 5
6                       8                 5
FALSE                   FALSE                 FALSE
TRUE                    TRUE                  TRUE
RJ Construction - Painting Estimator

Square
Feet (SF)
of
Length in Width in                             Wall/Ceilin Wall      New Color
Room                       Feet       Feet       Height in Feet           g           Condition Lighter ?
Kitchen                            20         15                      8          860           2 TRUE
Bedroom1                           16         12                      8          640           1 FALSE
Bedroom2                           10         12                      8          472           1 FALSE
Bath                                8          6                      8          272           3 FALSE
Total
Total Discounted Price:

Assumptions
Wall Condition        NO.        Cost primer Cost of first coat
Excellent                           1              0               0.45
Reasonable                          2              0                0.5
Poor                                3            0.5                0.6

Cost per square foot for
second coat when poor           0.35
New Color Lighter ?          TRUE          FALSE                    0.3

Economy                           -0.1                            1000         10%
Superior                             0                            2500         12%
5000         15%
High Price                        450
Painting Estimator

Wall                      Second    Paint
Repairs & First Coat      Coat      Quality
Paint    Costs     Costs           Costs     t          Total Costs Price?
Premium         -          430        258.00    129.00      817.00    TRUE
Economy         -          288           -      (64.00)     224.00 FALSE
Superior        -        212.4           -          -       212.40 FALSE
Superior    136.00       163.2         95.20        -       394.40 FALSE
136.00    1,093.60        353.20       65.00     1,647.80
1483.02

alternative               alternative alternative
430.00                        129       1483.02
288.00                         -64
212.40                           0
163.20                           0
PO 611 Bidder List Financial Evaluation

Net Worth    D&B CCAR (1
Customer Name                         (Dollars)      Best)                D&B PAYDEX (100 Best)
BFF Industries                   \$    157,795                   1                                 90
NRKK                                  387,000                   4                                 83
Pergo Molding                         775,961                   2                                 98
Allma                              17,043,973                   3                                 55
JF & Sons                          35,446,678                   1                                 32
Argree Inc.                        63,545,102                   3                                  0
RGM Plastics                       64,831,692                   2                                 85
Soto Services                     182,686,331                   1                                 73
NE Plastic                        239,227,375                   2                                 79
SPDM                              487,215,654                   2                                 97

Assumptions (HURDLES)                                  Total outcomes =
Net Worth (Dollars)              \$ 500,000.00
D&B CCAR (1 Best)                            1                      Experiments
D&B Stress Risk Class (1 Best)               1                      Outcomes for each experiment
D&B PAYDEX (100 Best)                       90
Allow to bid                                                        Exclude
Exclude
Get more data                                                       Get more data

Allow to bid
al Evaluation
D&B Stress
Risk Class                                       Final
(1 Best)    Rule #1   Rule #2   Rule #3    Recommendation
1        FALSE     TRUE      FALSE     Get more data
3        FALSE     FALSE     FALSE     Exclude
1        TRUE      FALSE     TRUE      Allow to bid
2        TRUE      FALSE     FALSE     Get more data
1        TRUE      TRUE      FALSE     Allow to bid
1        TRUE      FALSE     FALSE     Get more data
1        TRUE      FALSE     FALSE     Get more data
1        TRUE      TRUE      FALSE     Allow to bid
2        TRUE      FALSE     FALSE     Get more data
1        TRUE      FALSE     TRUE      Allow to bid

8

3
2

FALSE       FALSE     FALSE

TRUE        FALSE     FALSE
FALSE       TRUE      FALSE

TRUE        TRUE      FALSE
TRUE        TRUE      TRUE
FALSE       TRUE      TRUE
TRUE        FALSE     TRUE
FALSE       TRUE      TRUE

```
To top