...Microsoft Excel: Fo rmulas, F or mulas.. . 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. 2. Schedule a. 9:00 Registration b. 9:30 Morning session c. 12:00 Departure 3. Handouts available in Adobe Portable Document Format (PDF) for download at www.crowleycomputers.com/handouts.htm 1. 2. 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...
Words: 1729 - Pages: 7
...Constructing Formulas for Mathematical Operations in Excel (Basic Tips and Techniques) Michelle A. Applequist Computer Information Systems (CIS105) Professor Hari Dhungana Strayer University September 1, 2009 Constructing Formulas for Mathematical Operations in Excel Microsoft Excel uses formulas to construct mathematical operations in a worksheet. After data have been entered into the worksheet, you can perform calculations, analyze data, and create charts. An Excel formula (calculations you create) and functions (formulas pre-existing in Excel) calculates the data entered in the worksheet. Formulas calculate numbers in a particular order. “Excel has one of the most comprehensive set of formulas, not only to perform calculations but also to manage data and records. It also has the ability to instantaneously re-calculate the results as the raw data changes” (Khoo, 2006-9, para. 2). To construct a formula after you have entered data, you must click in the cell that you want the results to appear in, and then type the formula. You can construct formulas by using the sum function, and editing numbers in a cell. It is stated that: Sum is an Excel function—a prewritten formula. Sum indicates the type of calculation that will take place (addition). When the sum function is activated, Excel looks above the active cell for a range of cells to sum. If there is no range above the cell, Excel will look to the left for a range of cells to...
Words: 694 - Pages: 3
...EXERCISE C.1 You are a Sales Analyst for an export company called “Bicol Food Corporation.” As part of your work, you are tasked to prepare a Daily Shipment Summary Report based on the sales data faxed to you by the Sales Managers. To start preparing your report, do the following: 1. Type the company name in cell B2 using all caps. 2. Type the report name in cell B3 using all caps again. In this case, the report name should be “Daily Shipment Summary Report – Pili Cake.” Make the report name more obvious by changing the font to bold, font 16. 3. Type the report coverage in cell B4. The report you are preparing is for January 6, 2013. 4. Type the following headers from B7 to I7. Distributor Name Distributor Country Quantity Shipped Unit of Measure Unit Cost (Php) FC Unit Unit Price in FC Exchange Rate 5. Fix the column headers by highlighting/selecting cell B7:I7. Do not deselect cells unless mentioned below. * Under Home > Alignment, select “wrap text” to make all words fit in one box. Change also the cell alignment to “center.” * Change the font to bold. * Under Home > Font, click the border icon. Select the one that shows bottom border to insert a border after the headers. * Deselect the rows. 6. From B9 to B15, encode the following distributors: James Company Pte. Ltd. Lim Enterprises Hodge Wholesale Goods Alison Retail Dealer Tanaka Distributors Fang Lau Company Ah Kong Goods ...
Words: 1166 - Pages: 5
...at a university. Your task is: 1. Using IF, filter the data for these students by major. You will need to add a column for each major and then use the IF function to filter the data by major. Your formula should use proper anchoring so it is copyable both down and across. 2. Calculate the overall average GPA as well as the average GPA for each major. 3. Use conditional formatting to shade cells pink if a GPA is less than 2.2 Your solution must look like this (including the formatting): Your solution must take into account the possibility that the data could change, i.e., do not “hard code” any data values. If the major of a student changes, your calculations should update to the correct values automatically. Additionally, if new rows are inserted or existing rows are deleted, your spreadsheet model should still work. Problem 2 (40 Points) In the sheet “Problem 2”, you are given information about a savings account. Use this information to create a savings calculator. Notice that some cells have been given names. Use these names in formulas where appropriate. You can view all named cells in the Name Manager under the Formulas tab. 1. Use formulas to fill in the columns as shown below, including the column for DepNo. Lookup the DATE formula to figure out how to fill in the Payment Date column. You...
Words: 1130 - Pages: 5
...Strictly from a mathematical viewpoint: What Equals 100%? What does it mean to give MORE than 100%? Ever wonder about those people who say they are giving more than 100%? We have all been in situations where someone wants you to give over 100%. How about achieving 101%? What equals 100% in life? Here's a little mathematical formula that might help you answer these Questions: If: A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Is represented as: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 2122 23 24 25 26 Then: H-A-R-D-W-O-R- K 8+1+18+4+23+15+18+11 = 98% and K-N-O-W-L-E-D-G-E 11+14+15+23+12+5+4+7+5 = 96% But, A-T-T-I-T-U-D-E 1+20+20+9+20+21+4+5 = 100% AND, look how far the love of God will take you L-O-V-E- O-F -G-O-D 12+15+22+5+15+6+7+15+4 = 101% Therefore, one can conclude with mathematical certainty that: While Hard work and Knowledge will get you close, and Attitude will get you there, it's the Love of God that will put you over the top! It's up to you if you'll share this with your friends & loved ones just the way I did....
Words: 261 - Pages: 2
...be astronomical as long as the process is correctly developed. Cellonics is the future. Transmitting data over radio waves and then defragmenting is soon to be a method of the past, Cellonics offers technology with the ability to increase modem speed by up to 1000 times. The breakthrough came from the study of human cells, when stimulated these cells generated waveforms separated by periods of silence. The Cellonics technology has developed a procedure which mimics these signals in a way in which they can be applied to the communications industry. This sophisticated technology accepts slow analog input and distributes rapid pulse waveforms encoding digital information and broadcasting it over communication channels. NDS mathematical formulas are applied to the cells to stimulate responses; this is a non-linear system so the output can exceed normal capacities while the application remains straightforward. Cellonics is set to revolutionize the communication industry. Businesses or corporations which communicate remotely will be able to increase their speed, reliability and decrease their power consumption. All of these factors combine to offer a lower overhead operating cost and in return a higher profit margin. The face of telecommunications is changing, Cellonics is leading the charge. References: Hariharan, S. (2009) Cellonics Technical presentation under supervision of Dr. M. S....
Words: 265 - Pages: 2
...DOK Question Stems DOK 1 • Can you recall______? • When did ____ happen? • Who was ____? • How can you recognize____? • What is____? • How can you find the meaning of____? • Can you recall____? • Can you select____? • How would you write___? • What might you include on a list about___? • Who discovered___? • What is the formula for___? • Can you identify___? • How would you describe___? DOK 2 • Can you explain how ____ affected ____? • How would you apply what you learned to develop ____? • How would you compare ____? Contrast_____? • How would you classify____? • How are____alike? Different? • How would you classify the type of____? • What can you say about____? • How would you summarize____? • How would you summarize___? • What steps are needed to edit___? • When would you use an outline to ___? • How would you estimate___? • How could you organize___? • What would you use to classify___? • What do you notice about___? DOK 3 • How is ____ related to ____? • What conclusions can you draw _____? • How would you adapt____to create a different____? • How would you test____? • Can you predict the outcome if____? • What is the best answer? Why? • What conclusion can be drawn from these three texts? • What is your interpretation of this text? Support your rationale. • How would you describe the sequence of____? • What facts would you select to support____? • Can you elaborate on the reason____? • What would happen if___? • Can...
Words: 361 - Pages: 2
...understanding of relationships between numerical, geometric, verbal, and symbolic form and arithmetic sequences in order to extend algebraic reasoning. Students will use words and symbols to describe the relationship between the terms in an arithmetic sequence and their positions in the sequence. After this assessment students should have the understanding of the relationships between numerical, geometric, verbal and arithmetic sequences in to extend algebraic reasoning. Learning Outcomes a. Students will be able to identify appropriate formulas for solving algebraic problems (knowledge). b. Students will use equations to solve problems (application). c. Students will formulate problem situations when given a simple equation and formulate an equation when given a problem situation (synthesis). Assessment Context Students will demonstrate different representations of data and generate formulas or expressions that represent the data. Students will be able to write a statement that represents the relationships of data...
Words: 1269 - Pages: 6
...Math Investigation of Painted Cubes Introduction I was given a brief to investigate the number of faces on a cube, which measured 20 small cubes by 20 small cubes by 20 small cubes (20 x 20 x 20). To do this, I had to imagine that there was a very large cube, which had had its outer surface painted red. When it was dry, the large cube was cut up into the smaller cubes, all 8000 of them. From there, I had to answer the question, 'How many of the small cubes will have no red faces, one red face, two red faces, and three faces? From this, I hope to find a formula to work out the number of different faces on a cube sized 'n x n x n'. Solving the Problem To solve this problem, I built different sized cubes: 2 x 2 x 2; 3 x 3 x 3; 4 x 4 x 4; 5 x 5 x 5; 6 x 6 x 6; 7 x 7 x 7; 8 x 8 x 8; 9 x 9 x 9, using multi-links. I started by building a cube sized '2 x 2 x 2'. As I looked at the cube, I noticed that all of the corners had three faces. I then went onto a '3 x 3 x 3' cube. As I observed the cube, I saw that the corners all had three faces, the edges had two, and the faces had one. I looked into this matter to see if this was true. As I went further into the investigation, I found this was true. This made it much easier for me to count the cubes, and be more systematic. Now I could carry on building the cubes, and be more confident about not missing any out. Whilst building the cubes, I also drew them and decided to color code the different faces...
Words: 4265 - Pages: 18
...WEEK 1 TUTORIAL 1.Stock Watcher for Mydin Zamri has created a basic stock watcher worksheet for Mydin that the uses to report on gains or losses from when the company purchased the stock and the last recorded date and price. Since Zamri is busy with other task, he has given you a snapshot of his spreadsheet (see Figure below) that you can use to recreate this spreadsheet for yourself. Here are some basic steps to follow: 1. Create a new workbook 2. Enter all the information provided in figure below. 3. Apply the Currency format to the respective columns 4. The data should be entered as a function. 5. Enter a formula for Gain/Loss (%) column 6. Format the percent in the Gain/Loss (%) column 2. Total ToyStory ToyStory, Inc is a regional pet toy supplier that tracks its businesses sales in a spreadsheet. The owner, Douglass Tatnall, has provided you with a skeleton worksheet, ToyStory_Data.xls (see below), with the totals for each quarter by sales region. Tatnall needs you to total each column and row, and then provide her with a clustered column chart of each region by quarter. Regional Sales Summary | | | | | | | 1st Quarter | 2nd Quarter | 3rd Quarter | 4th Quarter | Northeast | $ 119,900.00 | $ 125,170.00 | $ 133,700.00 | $ 139,550.00 | South | $ 50,000.00 | $ 52,000.00 | $ 49,601.00 | $ 54,500.00 | Midwest | $ 34,800.00 | $ 32,200.00 | $ 47,900.00 | $ 45,900.00 | West | $ 56...
Words: 256 - Pages: 2
...Assignment brief – QCF BTECAssignment front sheet | Qualification | Unit number and title | BTEC Level 3 Extended Diploma in Business | Unit 42: Spreadsheet Modelling | Learner name | Assessor name | Martyna Litvinaite | Irene McCourt Francesse Mooney | Date issued | Hand in deadline | Submitted on | 15/09/14 | Task 1 15/10/14 Task 2 12/12/14 Task 3 20/03/15 Task 4 29/05/15 | 15/10/1410/12/14 | | | Assignment title | GamerWorld | In this assessment you will have opportunities to provide evidence against the following criteria. Indicate the page numbers where the evidence can be found. | Criteria reference | To achieve the criteria the evidence must show that the learner is able to: | | Task no. | | Evidence | P1 | * explain how spreadsheets can be used to solve complex problems | | 1 | | Pages1-2 | P2P3M1 | * develop a complex spreadsheet model to meet particular needs * use formulae, features and functions to process information * refine a complex spreadsheet model by changing rules and values | | 2 | | 2 Spreadsheets | P4P5P6P8M2M3 | * use appropriate tools to present data * customise the spreadsheet model to meet a given requirement * use automated features in the spreadsheet model to meet a given requirement * export the contents of the spreadsheet model to an alternative format * analyse and interpret data from a spreadsheet model * compare different automation methods...
Words: 302 - Pages: 2
...Mathematical Writing by Donald E. Knuth, Tracy Larrabee, and Paul M. Roberts This report is based on a course of the same name given at Stanford University during autumn quarter, 1987. Here’s the catalog description: CS 209. Mathematical Writing—Issues of technical writing and the effective presentation of mathematics and computer science. Preparation of theses, papers, books, and “literate” computer programs. A term paper on a topic of your choice; this paper may be used for credit in another course. The first three lectures were a “minicourse” that summarized the basics. About two hundred people attended those three sessions, which were devoted primarily to a discussion of the points in §1 of this report. An exercise (§2) and a suggested solution (§3) were also part of the minicourse. The remaining 28 lectures covered these and other issues in depth. We saw many examples of “before” and “after” from manuscripts in progress. We learned how to avoid excessive subscripts and superscripts. We discussed the documentation of algorithms, computer programs, and user manuals. We considered the process of refereeing and editing. We studied how to make effective diagrams and tables, and how to find appropriate quotations to spice up a text. Some of the material duplicated some of what would be discussed in writing classes offered by the English department, but the vast majority of the lectures were devoted to issues that are specific to mathematics and/or computer science. Guest lectures by...
Words: 48549 - Pages: 195
...Unit 6 Advanced Spreadsheet Skills Assignment 6.1 This assignment covers the following grading criteria (P1, P2, P5, M1, and D1) The assessed tasks in this unit are based on the scenario below Scenario MNK is a local company who publish 10 different magazines/newspapers in a variety of languages specifically for the Bradford area. They have been approached by other cities interested in their work. MNK are therefore looking to expand their operation to at least one more large city. To achieve this they will need to apply for extra funding from their bank. To support the funding application, the company needs to analyse and review the sales data and costs for the last financial year, which is included in Appendix A. This will require the production of a working spreadsheet model which will enable the production of suitable reports and graphs of current and predicted sales and financial data. You have been approached by MNK to create this spreadsheet model. By using the MNK Sales Data provided, your spreadsheet will hopefully show that MNK is a well run business, capable of expansion. You need to carry out the tasks listed below. Successful completion of these tasks may enable you to achieve the following grading criteria: P1 - create a complex spreadsheet that is fit for purpose and check accuracy P2 - use formulae and functions to solve a complex problem P5 – use conversion facilities to export the contents of a spreadsheet to an alternative format ...
Words: 1012 - Pages: 5
...day. I guess you can say that this is where my love for math started. I am not saying that I never had to study or that I have never struggled with any math in my life, but for the most part, most of the math classes I have taken in my life have come pretty easily to me. I love when you are struggling with something in math and all of a sudden you have that a-ha moment where everything suddenly makes sense. In the past I have taken all the basics in math including Algebra and Geometry. However, these courses were also taken almost 20 years ago, and I have to admit that I am a bit rusty when it comes to all of the formulas and properties. Even though I may not remember all of the formulas, I still consider my previous courses a success. I may not remember the exact formula, but I do remember that the formulas exist and it is just a matter of looking the formula up and plugging in the right numbers to get the correct answer. For example, I remember how I used to love doing proofs in geometry, the longer the better. I probably could not jump right into one of those long proofs now, but I don’t think it would take me long to get back to being able to solve one. This is because math is always the same. It is not like English where the rules are always changing. Two plus two will always be four, and this is...
Words: 774 - Pages: 4
...- c(1-t) + m ] = c0 + I + G + X Denoting the RHS (the sum of the autonomous components) with the symbol A, we have Y = 1 / [1 - c (1-t) + m]* A which gives us the equilibrium level of national income, but can also be expressed as changes: ΔY = 1 / [1 - c (1-t) + m]* ΔA The expression { 1 / [1 - c (1-t) + m] } is the multiplier (K). Dividing both sides of the above equation by ΔA ECON 1002 Feb 2013 We have ΔY / ΔA = 1 / [1 - c (1-t) + m] which gives us our first formula for K. What about the second formula? What is z? It is the marginal propensity to spend out of national income, which include the propensity to consume and the propensity to import. As we saw above, consumption spending comes out of disposable income, and therefore, the propensity to consume out of national income is c(1-t). We also assumed that imports represent a leakage from the circular flow, therefore, – m represents the propensity to import. We therefore have z = [c(1-t) – m] Notice that the minus sign in front of z in the multiplier formula will change the sign of its two terms and gives us the exact expression of K we derived above. What is obvious from the multiplier? From this expression we derived for K, we see that when c increases, the multiplier will increase, and when t and m...
Words: 473 - Pages: 2
