Microsoft Excel Functions & Formulas

by Held, Bernd

• ISBN13: 9781598220117
• ISBN10: 159822011X
• Format: Paperback
• Copyright: 2006-11-15
• Publisher: Jones & Bartlett Learning

Summary

Equipped with a variety of functions and easy to use, Microsoft Excel is the tool of choice when it comes to crunching numbers, building charts, and analyzing tables. But most users only scratch the surface of the program's functionality, especially the built-in formulas designed to make everyday operations and real-world tasks more efficient. Microsoft Excel Functions & Formulas demonstrates the secrets of Excel functions through the use of practical and useful examples in a quick reference format. Topics include logical, text, date and time, basic statistical, mathematical, financial, database, and reference functions as well as conditional formatting with formulas, array formulas, and user-defined functions.

Table of Contents

Acknowledgments	p. xiii
Introduction	p. xv
Formulas in Excel	p. 1
Production per hour	p. 2
Calculate the age of a person in days	p. 3
Calculate a price reduction	p. 4
Convert currency	p. 5
Convert from hours to minutes	p. 6
Determine fuel consumption	p. 7
Calculate your ideal and recommended weight	p. 8
The quick calendar	p. 9
Design your own to-do list	p. 10
Incrementing row numbers	p. 11
Convert negative values to positive	p. 12
Calculate taxes	p. 13
Combine text and numbers	p. 14
Combine text and date	p. 15
Combine text and time	p. 16
A special ranking list	p. 17
Determine the average output	p. 18
Stocks - gains and losses	p. 19
Evaluate profitability	p. 20
Determine percentage of completion	p. 21
Convert miles per hour to km per hour	p. 22
Convert feet per minute to meters per second	p. 23
Convert liters to barrels, gallons, quarts, and pints	p. 24
Convert from Fahrenheit to Celsius	p. 25
Convert from Celsius to Fahrenheit	p. 26
Calculation with percentage	p. 27
Monitor the daily production plan	p. 28
Calculate number of hours between two dates	p. 29
Determine the price per pound	p. 30
Determine how many pieces to put in a box	p. 31
Calculate manpower required for a project	p. 32
Distribute sales	p. 33
Calculate your net income	p. 34
Calculate percentage of price reduction	p. 35
Doubling every three hours	p. 36
Calculate the average speed	p. 37
Logical Functions	p. 39
Use the AND function to compare two columns	p. 40
Use the AND function to show sales for a specific period of time	p. 41
Use the OR function to check cells for text	p. 42
Use the OR function to check cells for numbers	p. 43
Use the IF function to compare columns and return a specific result	p. 44
Use the IF function to check for larger, equivalent, or smaller values	p. 45
Combine IF with AND to check several conditions	p. 46
Use the IF function to determine the quarter of a year	p. 47
Use the IF function to check cells in worksheets and workbooks	p. 48
Use the IF function to calculate with different tax rates	p. 49
Use the IF function to calculate the commissions for individual sales	p. 50
Use the IF function to compare two cells	p. 51
Use the INT function with the IF function	p. 52
Use the TYPE function to check for invalid values	p. 53
Use the IF function combined in one cell more than seven times	p. 54
Use the IF function to check whether a date is in the past or future	p. 55
Use the IF function to create your own timesheet	p. 56
Text Functions	p. 57
Use the LEFT and RIGHT functions to separate a text string of numbers	p. 58
Use the LEFT function to convert invalid numbers to valid numbers	p. 59
Use the SEARCH function to separate first name from last name	p. 60
Use the MID function to separate last name from first name	p. 62
Use the MID function to sum the digits of a number	p. 63
Use the EXACT function to compare two columns	p. 64
Use the SUBSTITUTE function to substitute characters	p. 65
Use the SUBSTITUTE function to substitute parts of a cell	p. 67
Use the SUBSTITUTE function to convert numbers to words	p. 68
Use the SUBSTITUTE function to remove word-wrapping in cells	p. 69
Use the SUBSTITUTE function to combine and separate	p. 70
Use the REPLACE function to replace and calculate	p. 71
Use the FIND function to combine text and date	p. 73
Use the UPPER function to convert text from lowercase to uppercase	p. 75
Use the LOWER function to convert text from uppercase to lowercase	p. 76
Use the PROPER function to convert initial characters from lowercase to uppercase	p. 77
Use the FIXED function to round and convert numbers to text	p. 78
Use the TRIM function to delete spaces	p. 79
Use the TRIM function to convert "text-numbers" to real numbers	p. 80
Use the CLEAN function to remove all nonprintable characters	p. 81
Use the REPT function to show data in graphic mode	p. 82
Use the REPT function to show data in a chart	p. 83
Use the CHAR function to check your fonts	p. 84
Use the CHAR function to determine special characters	p. 85
Use the CODE function to determine the numeric code of a character	p. 86
Use the DOLLAR function to convert numbers to currency in text format	p. 87
Use the T function to check for valid numbers	p. 88
Use the TEXT function to combine and format text	p. 89
Date and Time Functions	p. 91
Use custom formatting to display the day of the week	p. 92
Use the WEEKDAY function to determine the weekend	p. 93
Use the TODAY function to check for future dates	p. 94
Use the TEXT function to calculate with the TODAY function	p. 95
Use the NOW function to show the current time	p. 96
Use the NOW function to calculate time	p. 97
Use the DATE function to combine columns with date parts	p. 98
Use the LEFT, MID, and RIGHT functions to extract date parts	p. 99
Use the TEXT function to extract date parts	p. 100
Use the DATEVALUE function to recalculate dates formatted as text	p. 101
Use the YEAR function to extract the year part of a date	p. 102
Use the MONTH function to extract the month part of a date	p. 103
Use the DAY function to extract the day part of a date	p. 104
Use the MONTH and DAY functions to sort birthdays by month	p. 105
Use the DATE function to add months to a date	p. 106
Use the EOMONTH function to determine the last day of a month	p. 107
Use the DAYS360 function to calculate with a 360-day year	p. 108
Use the WEEKDAY function to calculate with different hourly pay rates	p. 110
Use the WEEKNUM function to determine the week number	p. 111
Use the EDATE function to calculate months	p. 112
Use the WORKDAY function to calculate workdays	p. 113
Use the NETWORKDAYS function to determine the number of workdays	p. 115
Use the YEARFRAC function to calculate ages of employees	p. 116
Use the DATEDIF function to calculate the ages of employees	p. 117
Use the WEEKDAY function to calculate the weeks of Advent	p. 118
Use the TIMEVALUE function to convert text to time	p. 119
Use a custom format to create a time format	p. 120
Use the HOUR function to calculate with 100-minute hours	p. 121
Use the TIME function to combine single time parts	p. 122
Basic Statistical Functions	p. 125
Use the MAX function to determine the largest value in a range	p. 126
Use the MIN function to find the employee with the lowest sales	p. 127
Use the MIN function to detect the smallest value in a column	p. 128
Use the SMALL function to find the smallest values in a list	p. 129
Use the LARGE function to find the highest values	p. 130
Use the INDEX, MATCH, and LARGE functions to determine and locate the best salesperson	p. 131
Use the SMALL function to compare prices and select the cheapest offer	p. 132
Use the AVERAGE function to calculate the average output	p. 133
Use the SUBTOTAL function to sum a filtered list	p. 134
Use the COUNT function to count cells containing numeric data	p. 135
Use the COUNTA function to count cells containing data	p. 136
Use the COUNTA function to count cells containing text	p. 137
Use the COUNTBLANK function to count empty cells	p. 138
Use the COUNTA function to determine the last filled row	p. 139
Use the SUBTOTAL function to count rows in filtered lists	p. 140
Use the RANK function to determine the rank of sales	p. 141
Use the MEDIAN function to calculate the median sales	p. 142
Use the QUARTILE function to calculate the quartiles	p. 143
Use the STDEV function to determine the standard deviation	p. 144
Mathematical Functions	p. 145
Use the SUM function to sum a range	p. 146
Use the SUM function to sum several ranges	p. 147
Use the SUMIF function to determine sales of a team	p. 148
Use the SUMIF function to sum costs higher than $1000	p. 149
Use the SUMIF function to sum costs up to a certain date	p. 150
Use the COUNTIF function to count phases that cost more than $1000	p. 151
Use the COUNTIF function to calculate an attendance list	p. 152
Use the SUMPRODUCT function to calculate the value of the inventory	p. 153
Use the SUMPRODUCT function to sum sales of a particular team	p. 154
Use the SUMPRODUCT function to multiply and sum at once	p. 155
Use the ROUND function to round numbers	p. 156
Use the ROUNDDOWN function to round numbers down	p. 157
Use the ROUNDUP function to round numbers up	p. 158
Use the ROUND function to round time values to whole minutes	p. 159
Use the ROUND function to round time values to whole hours	p. 160
Use the MROUND function to round prices to 5 or 25 cents	p. 161
Use the MROUND function to round values to the nearest multiple of 10 or 50	p. 162
Use the CEILING function to round up values to the nearest 100	p. 163
Use the FLOOR function to round down values to the nearest 100	p. 164
Use the PRODUCT function to multiply values	p. 165
Use the PRODUCT function to multiply conditional values	p. 166
Use the QUOTIENT function to return the integer portion of a division	p. 167
Use the POWER function to calculate the square and cube roots	p. 168
Use the POWER function to calculate interest	p. 169
Use the MOD function to extract the remainder of a division	p. 170
Modify the MOD function for divisors larger than the number	p. 171
Use the ROW function to mark every other row	p. 172
Use the SUBTOTAL function to perform several operations	p. 173
Use the SUBTOTAL function to count all visible rows in a filtered list	p. 174
Use the RAND function to generate random values	p. 175
Use the RANDBETWEEN function to generate random values in a specified range	p. 176
Use the EVEN and ODD functions to determine the nearest even/odd value	p. 177
Use the ISEVEN and ISODD functions to check if a number is even or odd	p. 178
Use the ISODD and ROW functions to determine odd rows	p. 179
Use the ISODD and COLUMN functions to determine odd columns	p. 180
Use the ROMAN function to convert Arabic numerals to Roman numerals	p. 181
Use the SIGN function to check for the sign of a number	p. 182
Use the SUMSQ function to determine the square sum	p. 183
Use the GCD function to determine the greatest common divisor	p. 184
Use the LCM function to determine the least common multiple	p. 185
Basic Financial Functions	p. 187
Use the SYD function to calculate depreciation	p. 188
Use the SLN function to calculate straight-line depreciation	p. 189
Use the PV function to decide amount to invest	p. 190
Use the PV function to compare investments	p. 191
Use the DDB function to calculate using the double-declining balance method	p. 192
Use the PMT function to determine the payment of a loan	p. 194
Use the FV function to calculate total savings account balance	p. 195
Use the RATE function to calculate interest rate	p. 197
Database Functions	p. 199
Use the DCOUNT function to count special cells	p. 200
Use the DCOUNT function to count cells in a range between x and y	p. 202
Use the DCOUNTA function to count all cells beginning with the same character	p. 203
Use the DGET function to search for a product number	p. 204
Use the DMAX function to find the most expensive product in a category	p. 205
Use the DMIN function to find the least expensive product	p. 206
Use the DMIN function to find the oldest person on a team	p. 207
Use the DSUM function to sum sales of a period	p. 208
Use the DSUM function to sum all prices of a category that are above a particular level	p. 209
Use the DAVERAGE function to determine the average price of a category	p. 210
Lookup and Reference Functions	p. 211
Use the ADDRESS, MAX, and MATCH functions to find the largest number	p. 212
Use the ADDRESS, MATCH, and MAX functions to find the smallest number	p. 214
Use the ADDRESS, MATCH, and TODAY functions to sum sales up to today's date	p. 215
Use the VLOOKUP function to look up and extract data from a database	p. 216
Use the VLOOKUP function to compare offers from different suppliers	p. 218
Use the HLOOKUP function to determine sales and costs of a team	p. 219
Use the HLOOKUP function to determine sales for a particular day	p. 221
Use the HLOOKUP function to generate a list for a specific month	p. 222
Use the LOOKUP function to get the directory of a store	p. 223
Use the LOOKUP function to get the indicator for the current temperature	p. 225
Use the INDEX function to search for data in a sorted list	p. 227
Use the INDIRECT function to play "Battle Ship"	p. 228
Use the INDIRECT function to copy cell values from different worksheets	p. 229
Use the INDEX function to determine the last number in a column	p. 230
Use the INDEX and COUNTA functions to determine the last number in a row	p. 231
Use the OFFSET function to sum sales for a specified period	p. 232
Use the OFFSET function to consolidate sales for a day	p. 234
Use the OFFSET function to filter every other column	p. 235
Use the OFFSET function to filter every other row	p. 236
Use the HYPERLINK function to jump directly to a cell inside the current worksheet	p. 237
Use the HYPERLINK function to link to the Internet	p. 239
Conditional Formatting with Formulas	p. 241
Use the WEEKDAY function to determine weekends and shade them	p. 242
Use the TODAY function to show actual sales	p. 244
Use conditional formats to indicate unavailable products	p. 245
Use the TODAY function to shade a special column	p. 246
Use the WEEKNUM and MOD functions to shade every other Tuesday	p. 247
Use the MOD and ROW functions to shade every third row	p. 248
Use the MOD and COLUMN functions to shade every third column	p. 249
Use the MAX function to find the largest value	p. 250
Use the LARGE function to find the three largest values	p. 251
Use the MIN function to find the month with the worst performance	p. 252
Use the MIN function to search for the lowest non-zero number	p. 253
Use the COUNTIF function to mark duplicate input automatically	p. 254
Use the COUNTIF function to check whether a number exists in a range	p. 255
Use conditional formatting to control font styles in a specific range	p. 257
Use a user-defined function to detect cells with formulas	p. 258
Use a user-defined function to detect cells with numeric values	p. 260
Use the EXACT function to perform a case-sensitive search	p. 262
Use the SUBSTITUTE function to search for text	p. 263
Use conditional formatting to shade project steps with missed deadlines	p. 264
Use conditional formatting to create a Gantt chart in Excel	p. 265
Use the OR function to indicate differences higher than 5% and lower than -5%	p. 266
Use the CELL function to detect unlocked cells	p. 267
Use the COUNTIF function to shade matching numbers in column B	p. 268
Use the ISERROR function to mark errors	p. 269
Use the DATEDIF function to determine all friends younger than 30	p. 270
Use the MONTH and TODAY functions to find birthdays in the current month	p. 271
Use conditional formatting to border summed rows	p. 272
Use the LEFT function in a product search	p. 273
Use the AND function to detect empty rows in a range	p. 274
Working with Array Formulas	p. 275
Use the ADDRESS, MAX, and ROW functions to determine the last used cell	p. 276
Use the INDEX, MAX, ISNUMBER, and ROW functions to find the last number in a column	p. 278
Use the INDEX, MAX, ISNUMBER, and COLUMN functions to find the last number in a row	p. 279
Use the MAX, IF, and COLUMN functions to determine the last used column in a range	p. 280
Use the MIN and IF functions to find the lowest non-zero value in a range	p. 281
Use the AVERAGE and IF functions to calculate the average of a range, taking zero values into consideration	p. 283
Use the SUM and IF functions to sum values with several criteria	p. 284
Use the INDEX and MATCH functions to search for a value that matches two criteria	p. 285
Use the SUM function to count values that match two criteria	p. 286
Use the SUM function to count values that match several criteria	p. 287
Use the SUM function to count numbers from x to y	p. 288
Use the SUM and DATEVALUE functions to count today's sales of a specific product	p. 289
Use the SUM function to count today's sales of a specific product	p. 290
Use the SUM, OFFSET, MAX, IF, and ROW functions to sum the last row in a dynamic list	p. 291
Use the SUM, MID, and COLUMN functions to count specific characters in a range	p. 292
Use the SUM, LEN, and SUBSTITUTE functions to count the occurrences of a specific word in a range	p. 293
Use the SUM and LEN functions to count all digits in a range	p. 295
Use the MAX, INDIRECT, and COUNT functions to determine the largest gain/loss of shares	p. 296
Use the SUM and COUNTIF functions to count unique records in a list	p. 297
Use the AVERAGE and LARGE functions to calculate the average of the x largest numbers	p. 298
Use the TRANSPOSE and OR functions to determine duplicate numbers in a list	p. 299
Use the MID, MATCH, and ROW functions to extract numeric values from text	p. 300
Use the MAX and COUNTIF functions to determine whether all numbers are unique	p. 301
Use the TRANSPOSE function to copy a range from vertical to horizontal or vice versa	p. 302
Use the FREQUENCY function to calculate the number of sold products for each group	p. 304
Special Solutions with Formulas	p. 305
Use the COUNTIF function to prevent duplicate input through validation	p. 306
Use the EXACT function to allow only uppercase characters	p. 307
Use validation to allow data input by a specific criterion	p. 308
Use controls with formulas	p. 309
Use Goal Seek as a powerful analysis tool	p. 311
Use a custom function to shade all cells containing formulas	p. 313
Use a custom function to change all cells with formulas to values	p. 314
Use a custom function to document and display all cells containing formulas	p. 315
Use a custom function to delete external links in a worksheet	p. 317
Use a custom function to delete external links in a workbook	p. 318
Use a custom function to enter all formulas into an additional worksheet	p. 319
User-defined Functions	p. 321
Use a user-defined function to copy the name of a worksheet into a cell	p. 322
Use a user-defined function to copy the name of a workbook into a cell	p. 323
Use a user-defined function to get the path of a workbook	p. 324
Use a user-defined function to get the full name of a workbook	p. 325
Use a user-defined function to determine the current user of Windows or Excel	p. 326
Use a user-defined function to display formulas of a specific cell	p. 327
Use a user-defined function to check whether a cell contains a formula	p. 328
Use a user-defined function to check whether a cell contains data validation	p. 329
Use a user-defined function to find all comments	p. 330
Use a user-defined function to sum all shaded cells	p. 331
Use a user-defined function to sum all cells with a shaded font	p. 332
Use a user-defined function to delete leading zeros for specified cells	p. 333
Use a user-defined function to delete all letters in specified cells	p. 334
Use a user-defined function to delete all numbers in specified cells	p. 335
Use a user-defined function to determine the position of the first number	p. 336
Use a user-defined function to calculate the cross sum of a cell	p. 337
Use a user-defined function to sum each cell's cross sum in a range	p. 338
Use a user-defined function to check whether a worksheet is empty	p. 339
Use a user-defined function to check whether a worksheet is protected	p. 340
Use a user-defined function to create your own AutoText	p. 341
Examples	p. 343
Calculating average fuel consumption	p. 344
Calculating net and corresponding gross prices	p. 347
Determining the economic value of a product	p. 348
Calculating the final price of a product, taking into account rebates and price reductions	p. 350
Searching for data that meets specific criteria	p. 351
Separating cities from zip codes	p. 353
Eliminating specific characters	p. 355
Combining text, dates, and timestamps	p. 356
Determining the last day of a month	p. 357
Determining the number of available workdays	p. 359
Determining a person's exact age	p. 361
Determining the number of values in a specific range	p. 362
Determining the weekly sales for each department	p. 364
Rounding a value to the nearest 5 cents	p. 367
Determining the inventory value	p. 368
Determining the top salesperson for a month	p. 370
Determining the three highest values in a list	p. 372
Determining the amount to invest	p. 374
Determining how many items are in various categories	p. 376
Finding a specific value in a complex list	p. 378
Dynamically showing costs and sales per day	p. 380
Extracting every fourth value from a list	p. 382
Index	p. 383
Table of Contents provided by Ingram. All Rights Reserved.