Comprehensive practical guide to mastering Excel 2010 functions and formulas. This pocket-sized reference covers fundamental formula conception through advanced techniques, including operators, cell references, naming conventions, and complex formula structures. The manual progresses from basic calculations and conditional logic with the IF function to sophisticated applications such as database functions, text processing, date and time calculations, financial computations, and array formulas. Key topics include lookup and reference functions, statistical and mathematical operations, error detection and auditing, circular reference management, and data table simulations. Each chapter includes practical examples, professional tips, and detailed explanations to help both beginners and experienced users optimize their spreadsheet work. The guide contains eleven comprehensive chapters with step-by-step instructions for implementing formulas effectively in professional environments.

Table of Contents:

Chapter 1 Developing simple formulas .... page 11
1.1. Understanding design principles .... page 13
Entering a formula .... page 13
Modifying a formula .... page 14
1.2. Using operators .... page 15
Using mathematical operators .... page 15
Using the concatenation operator .... page 17
1.3. Mastering relative, absolute and mixed references .... page 17
Discovering cell references .... page 18
Three-dimensional references .... page 27
External references .... page 29
1.4. Discovering additional tools and parameters .... page 30
Transforming a formula into a value .... page 30
Preventing Excel from systematically recalculating formulas .... page 31
1.5. Not displaying formulas .... page 32
Avoiding display inconsistencies due to rounding .... page 33
Displaying L1C1 type references .... page 34
Chapter 2 Using names in formulas .... page 35
2.1. Simply assigning a name to a cell or range of cells .... page 37
Assigning a name to a cell .... page 37
Using a name in a formula .... page 38
Assigning a name to a range of cells .... page 39
Selecting a cell or named range .... page 40
2.2. Defining and modifying names .... page 41
Defining a name .... page 41
Modifying the target of a name .... page 42
Inserting a name in a formula .... page 43
Pasting the list of names .... page 44
Creating series of names .... page 45
Deleting a name .... page 46
Systematically replacing cell references with names .... page 47
2.3. Assigning names to constants and formulas .... page 49
Assigning names to constants .... page 49
Assigning names to formulas .... page 50
2.4. Worksheet-specific names .... page 51
Chapter 3 Searching and using functions .... page 53
3.1. Understanding the function concept .... page 55
Discovering the function library .... page 56
Searching and inserting a function .... page 57
Entering a known function .... page 60
Using libraries .... page 62
Using the AutoSum button .... page 62
3.2. Using different types of arguments .... page 63
3.3. Knowing the different function categories .... page 65
Lookup and reference functions .... page 65
Text functions .... page 66
Date and time functions .... page 66
Logical functions .... page 66
Information functions .... page 66
Database functions .... page 67
Mathematical functions .... page 67
Statistical functions .... page 67
Financial functions .... page 70
Engineering functions .... page 70
Chapter 4 Using the IF function .... page 73
4.1. Discovering the function .... page 75
4.2. Developing simple formulas .... page 76
Displaying a fixed message if a condition is met .... page 76
Displaying a variable message if a condition is met .... page 77
Integrating the result of a conditional formula in an expression .... page 77
Testing on a character string .... page 78
Testing on a date .... page 79
Avoiding the display of an error message .... page 80
Including a formula in the condition .... page 81
Counting the number of different values in a sorted range of cells .... page 81
4.3. Using AND and OR operators .... page 83
The AND operator .... page 83
The OR operator .... page 84
The NOT operator .... page 86
Additional information on logical tests .... page 86
4.4. Nesting multiple IF functions .... page 88
Limiting the size of formulas .... page 89
4.5. Discovering the CHOOSE function .... page 91
Chapter 5 Calculating and counting .... page 93
5.1. Discovering essential functions .... page 95
Calculation functions .... page 95
Rounding functions .... page 104
Counting functions .... page 108
5.2. Performing calculations .... page 111
Calculating a sum .... page 111
Calculating a running total .... page 112
Calculating an average .... page 113
Calculating the sum of cells meeting criteria .... page 113
Determining the most frequent value in a range of cells .... page 115
Calculating a percentage change .... page 116
Displaying the largest values .... page 117
Rounding values .... page 118
5.3. Counting cells .... page 119
Counting empty cells .... page 120
Counting non-empty cells .... page 120
Counting cells containing numeric values .... page 120
Counting cells containing text .... page 121
Counting cells containing a character string .... page 121
Counting cells whose content is greater than a threshold .... page 121
Chapter 6 Exploiting databases .... page 123
6.1. Managing data with Excel .... page 125
Building a data table .... page 125
6.2. Discovering essential functions .... page 127
6.3. Performing simple searches .... page 136
Searching for a specific value in a table .... page 136
Avoiding error messages during a search .... page 139
Using other search techniques .... page 140
Exploiting data in interval form .... page 142
Searching for a value in a two-entry table .... page 143
6.4. Synthesizing data .... page 144
Calculating a moving average .... page 144
Performing multi-criteria syntheses .... page 146
Chapter 7 Processing text data .... page 151
7.1. Discovering essential functions .... page 153
7.2. Performing simple processing .... page 161
Juxtaposing character strings .... page 161
Integrating numeric values in character strings .... page 161
Counting characters in a text .... page 162
Searching in a text .... page 163
Replacing one text with another .... page 164
7.3. Combining functions for more complex processing .... page 165
Removing characters from the right or left .... page 165
Completing a character string to reach a fixed number of characters .... page 165
Counting occurrences of a character or word in a text .... page 166
Extracting the first word from a text .... page 166
Extracting the last word from a text .... page 166
Separating words in a text .... page 167
Chapter 8 Performing calculations on dates and times .... page 169
8.1. Understanding date representation in Excel .... page 171
Discovering the serial number concept .... page 171
Distinguishing date systems .... page 171
Entering dates and times .... page 172
Setting century interpretation .... page 173
8.2. Discovering essential functions .... page 173
8.3. Performing calculations on dates .... page 180
Displaying today's date in a text .... page 181
Writing the month in letters .... page 181
Writing the day of the week in letters .... page 181
Determining the quarter number .... page 181
Determining the last day of the month .... page 181
Determining the first day of the month .... page 182
Calculating the number of days in the month .... page 182
Determining the date of the previous Sunday .... page 182
Converting from American format .... page 184
Identifying an anniversary date .... page 185
Testing if a year is a leap year .... page 185
8.4. Performing calculations on times .... page

Excel 2010 Functions and Formulas - Complete Guide

Document Details

Related Documents

LCD TV Screen Repair Guide: Complete Troubleshooting

Sewing Machine Repair Tutorial: Complete Troubleshooting Guide

Learning Electronics from Zero - Level 3 Course

Lead-Acid Battery Desulfation Guide