intermediate level -alice platt most spreadsheet users know how to utilize basic math operations in...
TRANSCRIPT
Intermediate LevelIntermediate Level
-Alice Platt-Alice Platt
Most spreadsheet users know how to utilize Most spreadsheet users know how to utilize basic math operations in their basic math operations in their spreadsheets. spreadsheets.
At the next skill level, formulas can be used At the next skill level, formulas can be used to:to:
•Increase efficiency
•Add functionality
•Add usability
Back Next Start End
Making a cell change format Making a cell change format when a condition is metwhen a condition is met
This module will provide instruction on This module will provide instruction on three intermediate spreadsheet three intermediate spreadsheet functions.functions.These functions will be covered in the These functions will be covered in the following topics:following topics:
•Counting objectsCounting objects
•Counting objects if a condition is Counting objects if a condition is truetrue
Back Next Start End
These functions can save time by:These functions can save time by:
CountA
Conditional Format
CountIf
•sorting objectssorting objects
•counting counting objectsobjects•providing programmed providing programmed
alertsalertsThey work together They work together to make your to make your spreadsheet more spreadsheet more functional.functional.
Back Next Start End
These functions are called respectively:These functions are called respectively:
CountACountA
•CountIfCountIf
ConditionalConditional FormatFormat
Back Next Start End
Back Next Start End
Counts the number of non-blank cellsCounts the number of non-blank cells
Counts the number of non-Counts the number of non-blank blank
cells and text that can be cells and text that can be
interpreted as a numberinterpreted as a number
Counts the number of non-blank Counts the number of non-blank cells cells
and included number valuesand included number values
Back Next Start End
Count how many names are on a Count how many names are on a roster that constantly changesroster that constantly changes
Count how many items are in an Count how many items are in an
inventoryinventory
Back Next Start End
A B C D E F1
2 Ex 1 Ex 23 Mary Mary4 Joe Joe 15 Mat Mat6 Art Art 17 Adam Adam8
9 Arriving from California
10 Terry Terry11
12 Eric Eric13 Joe Joe14
15 TOTALS 8 8
Example 1
The CountA function counts the names in the column, excluding the blank cells. As names are added and deleted, the CountA function will continue to keep an accurate account of the number of names. (Press Enter to Continue)
Example 2In this example the CountA function counts the names and ignores the subheading -‘Arriving from California’.
Back Next Start End
The basic formula is:The basic formula is:
==CountA(range1,range2,…)CountA(range1,range2,…)•Example 1 Formula:Example 1 Formula: ==CountA(B2:B13)CountA(B2:B13)•Example 2 Formula:Example 2 Formula: ==CountA(D2:D8,D10:D13)CountA(D2:D8,D10:D13)
Up to 30 ranges may be Up to 30 ranges may be defined.defined.
Back Next Start End
Back Next Start End
Count how often a condition is Count how often a condition is metmet
Count how often a value or text Count how often a value or text occursoccurs
Back Next Start End
How many sales did Bob make?
How many invoices are equal to $20?
How many sales were over $20?
How many sales were less than $20?
Back Next Start End
How many sales did each person complete?
A B C D E F1 Person Invoice2 Bob 103 Greg 13 Greg= 54 Rob 40 Rob= 35 Greg 2 Bob= 66 Greg 47 Rob 13 Invoices<25 98 Bob 23 Invoices=25 19 Bob 32 Invoices>25 410 Rob 2111 Greg 412 Bob 313 Bob 4714 Bob 2515 Greg 38
How many invoices are less then/ equal to/greater then 25?
Back Next Start End
(Press Enter to Continue)
=COUNTIF(RANGE,”VALUE”)• Can use only one range• Value must be in full quotes and
may be:1. Text2. Number (equality)3. Inequality (> or < or <= or
>=) =COUNTIF(RANGE,CELL)
Counts how often the contents of the CELL will be counted in the selected RANGE
Back Next Start End
=COUNTIF(B2:B15,”Greg”)Counts the number of times Greg appears in the range B2:B15
=COUNTIF(C2:C15,”25”)Counts the number of times the value 25 appears in the range C2:C15
=COUNTIF(C2:C15,”>=25”)Counts the number of times a value greater then or equal to 25 appears in the range C2:C15
Back Next Start End
Back Next Start End
When the specified condition of a cell is met, the appearance of the cell will change so that:
1. Borders will be added or changed AND/OR
2. Font/appearance will be changed AND/OR
3. Cell colors/patterns will be added/changed
Up to 3 different conditions can be set for a cell
Back Start EndNext
PROCEDURE
STEP 1: Click on cell to be formatted
STEP 2: Choose Conditional Format from Format menu
NOTE: Conditional Format is NOT available with mouse right click
Back Next Start End
STEP 3: In the Conditional Format dialogue box do the following:
A. Choose Cell Value Is from the drop down menu
B. Choose a value condition for the cell from the drop down menu
C. Choose the Value or Values of the cell that will determine when the condition is TRUE
D. Click on Format
Back Next Start End
B.Not BetweenEqual ToNot Equal ToGreater ThanLess ThanGreater Than or Equal ToLess Than or Equal To
A.Choose Cell Value Is
C.
Cell Values
D.
Click on Format
Back Start EndNext
STEP 4: In the Format Box choose how you want the cell and/or text to appear when the condition you have chosen is TRUE
A. Choose the font and/or color AND/OR
B. Choose the cell pattern and/or fill color AND/OR
C. Choose the type of border
Back Next Start End
STEP 5: If there is only one condition in which you want the appearance of the cell to change, then click OK in the Conditional Format box
STEP 6: If there is a second or third condition in which you want the cell to change appearance, then click Add in the Conditional Format box. A second and third Conditional Format box will appear. Repeat Steps 1 – 5.
STEP 7: If you which to clear the Conditional Formatting, simply click on Delete in the Conditional Format box
Back Next Start End
Add another
Condition
Clear the formatting
Click to set and finish
Back Next Start End
EXAMPLE: Inventory of 100 boxes
Cell Value = Greater than or equal to 50 boxes Condition 1: Font is Black and cell fill is Light Green
Cell Value = Between 35 and 50 boxes Condition 2: Font is Black and cell fill is Yellow
Cell Value = Less than or equal to 35 boxes Condition 3: Font is Bold Black and cell fill is Red
Back Next EndStart
Format Menu
Condition 1
Condition 2
Condition 3
Back
Next
Start
End
ZOOOOM !!!!!!!
ALMOST TO THE FINISH!!
Back Next Start End
FunctioFunctionn
FormulaFormula UseUse
Count Count Alpha/Alpha/
Numeric Numeric EntriesEntries
=CountA(Range1,Rang=CountA(Range1,Range2)e2)
Use to count how Use to count how many cells have many cells have entries; will count text entries; will count text and numbers. Up to 30 and numbers. Up to 30 ranges.ranges.
Count Count specific specific Alpha/ Alpha/
Numeric Numeric entriesentries
=CountIf(Range,”value=CountIf(Range,”value”)”)
Use to count cells with Use to count cells with specific alpha or specific alpha or numeric entries or numeric entries or inequalities. One inequalities. One range and value per range and value per formula. formula.
Format Format Cells Cells
ConditionallConditionallyy
Use Conditional Format Use Conditional Format menu and dialogue menu and dialogue box.box.
Use formats to change Use formats to change appearance of cell. Up appearance of cell. Up to 3 conditions can be to 3 conditions can be programmed per cell.programmed per cell.
Back Next Start End
In this module you have learned the formulas for three spreadsheet functions:
CountA
CountIf
Conditional Format
CountA – Use when it is necessary to know how many cells have alpha/numeric data.
CountIf – Use when alpha/numeric data must be sorted.
Conditional Format – Use to change the appearance of a cell when the contents change according to pre-determined conditions.
Back Next Start End
I hope you have found this I hope you have found this presentation to be informative, presentation to be informative, interesting, and useful. interesting, and useful.
I would love to hear your comments I would love to hear your comments and suggestions. You can submit and suggestions. You can submit them to me using the email them to me using the email address provided on my Web site.address provided on my Web site.
Back Start