Information about Excel Formulas Functions

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com Read as “IF the average of A2 through A5 is greater than 50, THEN enter the sum or B2 through B5, ELSE enter a 0” Hands on Exercises: Start Excel. 1. Open the file named Excel-FormulasFunctions.xls. (The five sheets in this workbook will be used in class. Notice each sheet tab identifies the topic covered.) 2. Make sure you are looking at the first sheet, named AvgMaxMin We will enter formulas to calculate the AVERAGE, MAXIMUM (highest) and MINIMUM (lowest) book sales in cells N5, O5, P5. Note that the formulas have already been entered in N4, O4, and P4 for you to refer to. Reminder: Always position the cursor in the cell where you want the result of your function or formula to go before starting your formula or function. 3. Click Cell N5. 4. Click the Insert Function button 5. Select AVERAGE from the Function Name box on the right side. (If you don’t see it, click All in the Function Category box to get the entire list and choose from there.) 6. Once you have found it, click OK. The Function Argument Box will open. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com . 7. Indicate the range of cells B5 to M5. (Note: Excel may try to help you by suggesting a range. If the range suggested by Excel is correct then this step is not necessary, however it is a good idea to double check. ) If the box is taking up too much space, you may click the Collapse Dialog Box button which collapses the dialog allowing you to check the range. Click the Collapse Dialog button again to expand the window. 8. Notice the formula result is displayed near the bottom of the formula palette. 9. Click OK. 10. The function can now be copied down to the rest of the cells in the column. Use the fill handle to do so. 11. Refer to the preceding steps to enter the function for MAX in cell O5 and MIN in cell P5. Hint: Check the contents of cells O4 and P4 before you begin. Be sure to ask if you have questions. SimpleIF 1. Switch to the second sheet labeled SimpleIF. In this sheet you will use a simple IF function to evaluate a number in column B (the students’ numerical grade) and then assign an S or NC (indicating Satisfactory or No Credit for the course) in column C. NOTE: A satisfactory grade is one which is > 65.5 For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com Look at the contents of cell C4 and also notice the informational text off to the right. =IF(B4>65.5,"S","NC") (Tip: The quotes around S and NC (or any text) are necessary. Quotes are added automatically when using the function argument box.) 2. Click cell C5, then click the Insert Function button and try to create the function yourself. Please ask if you have questions. NestedIF 1. Click the NestedIF sheet tab. This sheet will show you how to assign a letter grade to a numeric score. 2. The first function has been entered for you in C2. Click C2 and check the formula bar to see what it looks like. If you would like, try to recreate the function in Cell C3. =IF(B3>92,"A",IF(B3>85,"B",IF(B3>78,"C",IF(B3>65,"D","F")))) 3. Click Cell C3. Hint: Start with the Insert Function button and choose the IF function. To add each of the nested IF functions for the “value_if_false” fields, click the function box (to the left of the red X on the formula bar and choose IF. This is a tricky one. Please ask if you would like assistance. Another method is to type the formula if you already know what it is. AVERAGE, MAX, MIN AND IF REVIEW 1. Click the Grades sheet tab. This sheet will allow you to apply what you have just learned about AVG, MAX, MIN and IF. Refer to the information on the lower portion of this sheet, the instructions on pages 2 and 3 of this handout, or to the other sheets in the Excel file. If you have questions, be sure to ask the instructor. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com CONDITIONAL SUM The last sheet in your workbook gives examples of a conditional sum by using the SUMIF function. The SUMIF function adds the cells specified by a given condition or criteria. 1. Click the Cond.Sum sheet tab. This sheet contains data about employee travel expenses. In this exercise we want to find a total dollar amount by person. One way to do this is by using the SUMIF function. 2. Scroll down to the bottom of the data. 3. Click cell I25 and check the formula bar to see the contents. =SUMIF(B5:B22,"Dr. Alexander",I5:I22) The first part of the formula sets the range that contains the criteria. The second tells you what the criteria is (i.e,; is equal to “Dr. Alexander”). The third part defines the range that contains the data that you would like to manipulate. To check this out, change the contents of cell G26 to Dr. Alexander. If you would like to recreate the function, just choose any cell and start by clicking the Insert Function button and choosing SUMIF. Notice the information provided at the bottom of the sheet to help you. STATISTICAL Functions Open Excel-StatsCalc.xls Complete the formulas in Column D by using the Insert Function feature or you may simply type in the formulas listed in Column E. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com Now try it by adding a new feature “Analysis Tool Pac” Go to Tools Add ins Analysis Tool Pac Let’s now use the new feature. Go to Tools Data Analysis Descriptive Statistics Select Input Range as B5:B14 Select Output Range E18 Check Summary Statistics Microsoft Excel has hundreds of functions, many of them very complex. This class has covered only a few of the most commonly used functions. As you explore the other functions in Excel, refer to on- line help and the dialog box prompts for assistance. The more you use Excel functions the easier they become. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. For example, cell A3 ...

Read more

Excel functions (by category ... The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 ...

Read more

Learn how to use all 300+ Excel formulas and functions including worksheet functions entered in the formula bar and VBA functions used in Macros. Here are ...

Read more

Provides help to users of Excel, with an emphasis on functions and formulas, but also covering basic Excel, tips and an introduction to VBA.

Read more

Excel Helps - is place to help you learn Excel simply and quickly. This is a tutorial explaining how to start and make a great use of Excel.

Read more

Post and discuss Excel Formulas & functions. ... Contains unread posts Contains no unread posts Hot thread with unread posts

Read more

Discover how functions in Excel help you save time. If you are new to functions in Excel, we recommend you to read our introduction to formulas and ...

Read more

Overview of formulas in Excel Applies To: Excel ... You can find all of Excel's functions on the Formulas tab on the Ribbon: Excel function syntax.

Read more

MS Excel; Formulas / Functions ... all conditions within the AND function must be TRUE for the condition to be met. This comes in very handy in Excel formulas.

Read more

A collection of useful Excel formulas for sums and counts, dates and times, text manipularion, conditional formatting, percentages, lookups, and more!

Read more

## Add a comment