Technology & Engineering
Excel Formulas
Excel formulas are expressions used to perform calculations, manipulate data, and automate tasks within Microsoft Excel. They can range from simple arithmetic operations to complex functions, allowing users to analyze and process large amounts of data efficiently. By using formulas, users can create dynamic and interactive spreadsheets that can adapt to changes in input data.
Written by Perlego with AI-assistance
Related key terms
1 of 5
5 Key excerpts on "Excel Formulas"
eBook - ePub- Michael Alexander, Richard Kusleika(Authors)
- 2014(Publication Date)
- Wiley(Publisher)
Chapter 1: Introducing Excel Formulas
Microsoft Excel is, at its core, a calculation engine. Like a calculator, it accepts a question (such as 2+2) and gives you an answer. When you’re working with a calculator, these questions are called mathematical operations. In Excel, you call these formulas.Excel allows you to use formulas to perform not only mathematical operations but also a myriad of other complex actions, such as parsing textual values, searching for certain values in a range of data, performing recursive calculations, and much more.To leverage the full power of Excel Formulas, you need to understand how Excel Formulas work as well as some of the ground rules for working with formulas. The goal of this chapter, therefore, is for you to get acquainted with the fundamentals of using Excel Formulas.Feel free to skip over this chapter if you already have a solid understanding of formula mechanics and want to get right into real-world examples, which start in Chapter 2 .You can download the files for all the formulas at www.wiley.com/go/101excelformula .Creating and Editing Excel Formulas
An Excel spreadsheet is simply a collection of cells that you can use to enter data. Each cell is given a name based on its position in the spreadsheet. The top-leftmost cell is called cell A1. It’s the cell located in column A, row 1. When you enter a value in cell A1 (for example, the number 5), that cell’s value becomes 5. If you enter the number 10 in cell B1, that cell’s value becomes 10. You can use then use these values in a formula.For instance, you can click cell C1 and begin typing =A1+B1, as shown in Figure 1-1
No longer available |Learn more- Michael Alexander, Dick Kusleika(Authors)
- 2022(Publication Date)
- Wiley(Publisher)
Part II F ormulas and worksheet functions are essential to manipulating data and obtaining useful information from your Excel workbooks. The chapters in this part start with a solid foundation in using Excel Formulas and gradually introduce more sophisticated concepts such as the new dynamic array functionality in Excel. IN THIS PART Chapter 9 Introducing Formulas and Functions Chapter 10 Understanding and Using Array Formulas Chapter 11 Using Formulas for Common Mathematical Operations Chapter 12 Using Formulas to Manipulate Text Chapter 13 Using Formulas with Dates and Times Chapter 14 Using Formulas for Conditional Analysis Chapter 15 Using Formulas for Matching and Lookups Chapter 16 Using Formulas with Tables and Conditional Formatting Chapter 17 Making Your Formulas Error-Free Working with Formulas and Functions 211 CHAPTER 9 Introducing Formulas and Functions IN THIS CHAPTER Understanding formula basics Entering formulas and functions into your worksheets Understanding how to use cell references in formulas Using formula variables Building formulas with Excel table objects Correcting common formula errors Getting tips for working with formulas E xcel is, at its core, a calculation engine. Like a calculator, Excel accepts questions in the form of a formula (such as =2+2 ) and returns an answer. Formulas allow you to perform not only mathematical operations, but also a myriad of other complex actions. With formulas, you can parse textual values, look up data based on specific criteria, and perform conditional analysis. If it weren’t for formulas, a spreadsheet would simply be a fancy word processing document that has great support for tabular information. If you want to leverage the full power of Excel, it’s important to understand how formulas work. This chapter introduces formulas and functions and helps you to get up to speed with writing your own formulas and functions.
eBook - PDF- A Thorne(Author)
- 2025(Publication Date)
- Macmillan(Publisher)
Formulas are a helpful feature in spreadsheet programs, because if the value of a cell that is covered by a formula changes, the program updates the answer automatically. Note that for all calculations, there should be no words, abbreviations (e.g. kg or mm) or existing formulas in the cells you select. If they do, it will create error messages, as you will see in Section 1.15.6. Table 1.15.1: Basic calculations in Excel Operation Operator / formula Description Examples Addition Plus sign Add up cell contents. =A1+B1 SUM formula Add up a range of cells. =SUM(A1:E3) AutoSum formula Add up a column or row. =SUM(A1:A5) Subtraction Minus sign Subtract a value from another. =A1-B1 Multiplication Asterisk * Multiply a value by another. =A1*B1 Division Forward slash / Divide a value by another. =A1/B1 Exponents Caret ^ Raise a value to a power. =A1^2 Square roots SQRT function Find the square root of a value. =SQRT(A1) Percentages % Find a percentage of a value. =A1*25% The operations seen here can be combined into long calculations. It is then especially important to use brackets to keep relevant operations together, and to arrange them in the correct order. We will explain this in Section 1.15.2. 177 TVET FIRST Computing concepts and application skills You can add up cells anywhere on the spreadsheet. Key in their cell addresses, or simply click on them. There are three important rules: • First key in an equal sign in the cell in which the answer must go. • Click the first cell. Key in the operator. Enter after the last cell. • The cells must not already have a formula or any words in them. Figure 1.15.2: A sample spreadsheet Look at the spreadsheet above. By using cell addresses, we can do the same kinds of operations as in mathematics. Here are examples of calculations and their Excel Formulas. You will see the formula in the Formula bar when you click the answer cell afterwards.
eBook - PDFComputer Practice N5 SB
TVET FIRST
- S Sasti, D Sasti(Authors)
- 2021(Publication Date)
- Macmillan(Publisher)
Formulae are used to perform these calculations, which are used to generate new data or to help you to analyse current data. Table 3.3 shows symbols in MS Excel that perform simple mathematical operations. MS Excel enables users to perform any mathematical calculation, which is what makes the program so powerful and useful. Table 3.3: Symbols used for mathematical operations in a formula Symbol Mathematical operation Example Explanation of the formula + Addition = B1 + 20 We are adding 20 to the value stored in cell B1 – Subtraction = C3 – 5 We are subtracting 5 from the value stored in cell C3 / Division = D6 / 3 We are dividing the value stored in cell D6 by 3 * Multiplication = B1 * 4 We are multiplying the value stored in cell B1 by 4 ^ Exponents = E1 ^ 2 We are raising the value stored in cell E1 to the power of 2 % Percentages = F1 * 15% We are calculating 15 percent of the value stored in cell F1 Creating formulae You have to start your formula with the ‘=’ symbol followed by the mathematical operation for your formula. This is how MS Excel knows that you want to use a formula. You can refer to the cell contents or use constants in your calculations. • Click on the cell to display the result of the calculation. • Click in the Formula bar. • Type your formula, and press the Enter key. • MS Excel will calculate and display the result. • In Figure 3.62, the constant number 3 is added to the contents in the cell A2. 180 min Mathematical operation Contents from cell A2 to be used in the formula Constant value Start of a formula Figure 3.62: A formula explained 152 Topic 1: Computing concepts and application skills TVET FIRST Order of precedence in MS Excel formulae Excel uses the order of precedence to calculate a formula when it contains many mathematical operations: • First, evaluate items in parentheses or brackets. • Then exponents. • Then multiplication and division.
eBook - PDF- A Thorne(Author)
- 2017(Publication Date)
- Macmillan(Publisher)
90 Module 18 Topic 7: Advanced features of Microsoft Excel (a spreadsheet program) Use formulas to perform advanced calculations in a spreadsheet Module 18 This module shows you how to use basic arithmetical operations and other spreadsheet features in advanced calculations. Think about it • What is the difference between a formula and a calculation? • Why is the correct address of a cell in a spreadsheet important? • In what ways can combinations of arithmetical operations be used in a spreadsheet? Box 18.1: Using the keyboard in Excel calculations As a reminder, here are the main keys you will be using for calculations in this module: Ctrl ` to display formulas Shift and colon : for cell ranges Hyphen (dash) - to subtract cells Left bracket ( in some formulas Shift and asterisk * to multiply cells Forward slash / to divide cells Equal sign to start a formula Shift and plus sign + to add up cells Figure 18.1: Where to find the main keys on the keyboard Note: l The files in Microsoft Excel are called workbooks . l Each new workbook automatically has a blank worksheet (e.g. Sheet1) in it. l Fill a worksheet with data to create a spreadsheet . However, we will be using the terms “spreadsheet” and “file” generically. 91 Module 18 Unit 18.1: Doing advanced calculations using formulas A formula consists of letters and symbols that express a mathematical relationship. Formulas are used to perform calculations with the numbers in a spreadsheet. For this we need the “addresses” of the specific cells. The contents of an active cell appear in the Formula bar An active cell has a border around it Blank row 7 Blank column E Column name of the active cell The cell address of the active cell is displayed here Row number of the active cell Figure 18.2: Cell address, cell contents and the Formula bar Note on selection: To select a cell, click inside it.
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
