Microsoft Excel: Formulas, Formulas...
Northern New York Library Network 6721 US Hwy 11 Postdam, NY 13676 877-833-1674 or 315-265-1119 www.nnyln.org

Jim Crowley C3 - Crowley Computer Consulting 9148 State Highway 37 Ogdensburg NY 13669 315-394-7008 fax 315-394-7009 www.crowleycomputers.com jim@crowleycomputers.com Description: Excel has hundreds of functions and nobody knows them all, but spend some time and learn to create more powerful formulas. Students will dive into decision making functions like if() and lookup() and make Excel work for them! Students will also explore efficient use of multiple sheets in a workbook, creating multi-sheet and multi-workbook formulas. Students will learn about: if() functions, lookup() functions, conditional formatting, and using multiple worksheets in a workbook. 1. Course objectives. a. To provide the class with an exploration of spreadsheet design. b. To develop computer and spreadsheet literacy. c. To address the class' questions. Schedule a. 9:00 Registration b. 9:30 Morning session c. 12:00 Departure Handouts available in Adobe Portable Document Format (PDF) for download at www.crowleycomputers.com/handouts.htm

2.

3.

1.

Formulas: All formulas begin with = or basic math function a. Simple operators Monthly weather (1) + averages.xls (2) (3) * multiply (4) / divide (5) ^ exponent (6) = < > comparison b. Order of operator precedence (or Algebraic Order of Operations, for those of you as old as me!) determines what order calculations occur. (1) Calculations do not occur from left to right. (2) 3+4*5 does not equal (3+4)*5 (3) When you’re not sure, use (parenthesis) to force order of calculation. c. Edit via double clicking on cell, clicking on formula bar or F2

Operators (in order of evaluation) Reference Range : Intersection Space Union , Arithmetic Negation Percentage % Exponentation ^ Multiplication * Division / Addition + Subtraction Text Join & Comparison (equal precendence) Equal = Greater than >...

...Week 6
Excel
PF008 A14
Introduction to Spreadsheets
A spreadsheet is an electronic file that contains a
grid of columns and rows used to organize related
data and perform calculations.
Excel is a spreadsheet program used to create and modify
electronic spreadsheets.
An electronic spreadsheet makes data-entry changes easy,
and if the formulas are correctly constructed, the results
recalculate automatically and accurately.
A worksheet is a single spreadsheet that typically contains
descriptive labels, numeric values, formulas, functions, and
graphical representations of data.
A workbook is a collection of one or more related
worksheets contained within a single file. Each worksheet
is identified by a sheet tab. By default, new workbooks
have three worksheets.
The whole file is a workbook
Worksheet
Planning Structure of Worksheets
1. State the purpose of the worksheet
2. Decide what input values are needed
An input area is a range of cells containing values
3. Decide what outputs are needed
An output area is a range of cells containing
results
4. Assign the worksheet inputs and results
Use rows and columns
5. Enter the labels, values, and formulas
6. Format the numerical values
7. Format the descriptive titles and labels
8. Document the worksheet
9. Save the completed workbook
Exploring the Excel Window
• Worksheet rows lie horizontally
• Worksheet columns lie vertically
• A...

...Spreadsheet Evaluation
To create an ICT solution to record and analyse costs for the Actors Associates business I chose Microsoft Excel because it presents the information in a clear way which is easy for the reader to understand. It also performs quick and easy calculations.
Use of Formulae.
In my excel spreadsheet, I used different formulae to perform calculations. Some of the formulae I used include addition and multiplication, which i believe are input correctly. I used replication to prevent inputting the same formula repetitively. This also saved time but the only problem that would have occurred, would be if the original formula was incorrect and it was replicated throughout the whole column. I also used if statements and absolute cell references in the worksheets. If statements check if a statement is true or false and inputs the data appropriate. An absolute cell reference is useful to refer to another and replicate the formula without changing that cell reference, e.g =($M$4*J4). Another useful formula I used was to linking worksheet within my spreadsheet. For example, =COST!E5. This allowed me to update data in a cell in one worksheet and have the changes applied to the appropriate cell in other worksheets.
Data Validation.
Data validation is a tool on excel which makes it possible to control the values input into a given cell. I used this tool to prevent...

...Financial Analysis using Excel
Function
Information requirements for function
Dates
Prices and par values
Frequency, and related
Yields and interest/ discount rates Discount
Any amount paid at maturity ?
Frequency of payme nts pa
First coupon
Redemption amount
Investment amount
# of periods
Settlement
Par/coupon
Maturity
COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPPCD COUPNUM DURATION MDURATION PRICE PRICEDISC PRICEMAT RECEIVED YIELD YIELDDISC YIELDMAT ACCRINT ACCRINTM INTRATE DISC
Excel for Professionals 2002 VJ Books. All rights reside with the author.
Interest
Basis
Issue
Yield
Price
Financial Analysis using Excel
Financial Analysis using Excel Financial Analysis using Excel
Volume 6 in the series Excel for Professionals Excel for Professionals
Volume 1: Excel For Beginners Volume 2: Charting in Excel Volume 3: Excel-- Beyond The Basics Volume 4: Managing & Tabulating Data in Excel Volume 5: Statistical Analysis with Excel Volume 6: Financial Analysis using Excel Published by VJ Books Inc
All rights reserved. No part of this book may be used or reproduced in any form or by any means, or stored in a database or retrieval system, without prior written permission of the publisher except in the case of brief quotations...

...APPENDIX
B
Excel Template Instructions
for the Glo-Brite Payroll
Project (Using Excel 2010)
The Excel template for the Payroll Project is an electronic version of the books of
account and payroll records. This is not an automated payroll system, but an
example of how you might use a spreadsheet program to keep payroll records
and account for payroll transactions.
You will need to follow the instructions in the textbook to complete the project. The instructions provided below will enable you to use the Excel template in
place of the journal, general ledger, payroll register, and employee’s earnings
records. Other forms, such as tax forms, are required for the Payroll Project. You
will use those provided in the book.
GETTING TO KNOW EXCELExcel ﬁles are called workbooks. A single workbook can store many worksheets,
which are stored like pages in a notebook. The workbook for this project has
four worksheets: the journal, the general ledger, the payroll register, and employee’s earnings records.
Each worksheet is made up of rows and columns. Rows are numbered from 1
to 65,536, and columns are labeled with letters. Column 1 is A, Column 26 is Z,
Column 27 is AA, and so on. The intersection of a row and column is called a cell.
Cells have addresses based on the row and column in which they appear. Each cell
can hold a number, text, a mathematical formula,...

...Situation 1
The general approach for facilitating this customer over the telephone is to explain the functions that would help to organize his spreadsheet for his monthly expense reports. I would explain to him that he can add formulas to the spreadsheet that will add up totals for him. He can set it up so it gives him the total expenses by day, week, and month. He can also set it up so that it calculates the totals spent on just meals, hotels, and transportation. He can also calculate what was spent on fuel, phone, entertainment, and misc expenses. I would also explain how using filters can help to narrow down data for an easy view without having to search through a lot of data to see what he needs for instance, if he just wanted to know what was spent on food. I would then explain that he can add charts and graphs to show what was spent in each category whether it is by day, week, month, or quarter. He would have an understanding of how graphs and charts are a quick and easy way to analyze information.
The possible challenges to doing this over the phone are for the customer to see how a spreadsheet would be useful. I can explain all the features but if he were able to see a spreadsheet and all the possibilities that are offered, he would be able to better understand the features I am explaining to him.
Strategies to overcome the challenges would be to ask a lot of questions of what he needs to accomplish what he is looking for, also to use simple terms...

...Microsoft Excel Exercise
IT/206
September 19, 2011
Patrica Byerly
Microsoft Excel Exercise
Situation 1: A business professional wants to use Microsoft Excel to organize data for the monthly expense report. He has never used Microsoft Excel and is inquiring about the functions that would support him with this task.
The approach that should be used to for this customer is to explain the functions that would help the customer to organize his spreadsheets for his monthly expense report. I would also explain to the customer that he may add formulas to the spreadsheet that will add up the totals for him, to help him get it done quicker. I would tell the customer that he could set it up to give him the total expenses by the day, week and also the month. I would also tell the customer that he could also set it up to calculate the totals spent on transportation, food, and motel expenses. Additionally, he could also calculate the fuel, entertainment, phone, and misc. expenses. I would also alert the customer that using filters will help narrow down data for an easy view without having to look all over the data to find what he is looking for, such as how much was spent on the motel stay. I would then tell the customer that he can add graphs and charts to show what was spent in each category, whether it was by the day, week, or month. I would let him know that if he decides to use graphs and charts it...

...DISCUSSION:
After using both languages, Microsoft Excel and MATLAB (MATrix LABoratory), on the same module experiments from 1 to 4, I observed the following:
* For Microsoft excel,
* It uses spreadsheet application where each elements or data values are distributed in cells.
* Most statistical computations are easy to solve due to the manner of data organization where data are inserted on cells arranged in numbered rows and letter-named columns.
* It is easy to plot the selected values by just choosing the selected data then use the chart tools/design tab.
* The Disadvantage of this language are as follows:
* Difficulty in debugging the error
* Limited set of statistical tools
* In the case of excessively large results, Excel returns #NUM! instead of an answer
* Inability to handle large datasets
* For MATLAB,
* It uses matrix based algorithms hence each element or data values are treated in an m row by n column, such that a single integer is considered a matrix of one row and one column.
* Manipulation of vectors is easily done by one command.
* To plot, matlab needs to use certain algorithms which are compiled in an M-file.
* The Disadvantage of this language are as follows:
* Expensive wherein every single toolbox has to be paid due to its licensure
* It uses a huge amount of computer memory; hard...

...Exploring Microsoft Office 2010 Volume 1
Excel Chapter 1
Testbank
1) A spreadsheet
A) is the core of a slide presentation.
B) is an electronic file that is used to write text and graphics on the web.
C) is an electronic file that contains a grid of columns and rows for related data.
D) is another word for a letter written on a computer.
Answer: C
Diff: 1
Reference: Introduction to Spreadsheets
Objective: 1
AppChap: Excel 1: Introduction toExcel
2) A spreadsheet PROGRAM is defined as
A) a software application used to create and modify spreadsheets.
B) a software application used to create and modify text-based documents.
C) a software application used to create and modify video presentations.
D) a software application used to create and modify a database.
Answer: A
Diff: 1
Reference: Introduction to Spreadsheets
Objective: 1
AppChap: Excel 1: Introduction to Excel
3) A worksheet is defined as
A) the background color of a cell.
B) the current cell location of the insertion point as indicated by a dark border.
C) a single spreadsheet that often contains formulas, functions, values, text, and visual aids.
D) an unfinished project.
Answer: C
Diff: 1
Reference: Introduction to Spreadsheets
Objective: 1
AppChap: Excel 1: Introduction to Excel
4) A workbook is defined as
A) similar to a spreadsheet but bound...