Everand Logo

Welcome to Everand!

    • Discover millions of ebooks, audiobooks, and so much more with a free trial

    From $11.99/month after trial. Cancel anytime.

    Excel Functions for the Daily User
    Excel Functions for the Daily User
    Excel Functions for the Daily User
    Ebook239 pages1 hour

    Excel Functions for the Daily User

    By Palani Murugappan

    ()

    Read preview

    About this ebook

    For most of us today, the personal computer has become part and parcel of our lives at home and the office. Our daily routine involves a fair deal of analyzing data, converting it to information, and interpreting it. Microsoft Excel is used to perform the number crunching to achieve the desired result. What this book focuses on is the many functions that can be used on a daily basis to analyze the data further before making a decision.

    It touches on examples for calculating a salesperson’s commission, looking up a table to retrieve employee’s details such as contact number, salary, age, etc., analyzing the best mode of delivery for a package, retrieving supplier’s credit limit, and performing data validation for a given range or list. Yes, all this is possible using Microsoft Excel’s built-in functions, the main focus of this book.

    The book assumes that the reader is already familiar with the basics of Microsoft Excel such as copying, editing, formatting, and performing basic calculations.
    LanguageEnglish
    PublisherLulu.com
    Release dateJul 16, 2015
    ISBN9781329389731
    Excel Functions for the Daily User

    Read more from Palani Murugappan

    Related to Excel Functions for the Daily User

    Related ebooks

    Business For You

    View More
    View More

    Related podcast episodes

    Related articles

    • UNLIMITED

      Miscrosoft Excel

      Feb 8, 2021

      Miscrosoft Excel
      5 min read

    • UNLIMITED

      Excel and Calc

      Aug 12, 2019

      Excel and Calc
      5 min read

    • UNLIMITED

      Merge Two Membership Spreadsheets

      May 11, 2022

      Running a small club is an admin challenge in itself, but what happens when two clubs merge and you need to create a single master Excel sheet for members? Thankfully, Excel has tools to help you manage this process, although you’ll need to do some w

      2 min read

    • UNLIMITED

      Visualising Experimental Results

      Mar 10, 2020

      All the examples in the main part of this tutorial generated results purely within LibreOffice Calc, using the laws of maths. However, it is also useful for creating graphs of experimental results, perhaps combined with equivalent results from simula

      1 min read

    • UNLIMITED

      MICROSOFT EXCEL & LIBREOFFICE CALC

      Jun 2, 2021

      Both Excel and Calc let you temporarily hide specific rows simply by selecting the relevant rows, right-clicking that selection within the rows column, then clicking Hide (Excel) or Hide Rows (Calc). However, if you want to calculate the sum of only

      1 min read

    • UNLIMITED

      Make Office Better

      Dec 21, 2022

      Make Office Better
      2 min read

    • UNLIMITED

      Make Office better

      May 22, 2023

      Presentations with lots of slides can be tricky to navigate. To make things easier, try adding a ‘table of contents’ slide at the start. First, select the View tab, then click Outline View – this displays a list of all your slides down the left side

      2 min read

    • UNLIMITED

      Make Office Better

      Mar 10, 2021

      LIBREOFFICE CALC & MICROSOFT EXCEL Calc and Excel both let you generate random numbers. The RAND() formula returns a random number between 0 and 1, while RANDBETWEEN(x, y) lets you specify a range for the random numbers (replace the ‘x’ with the lowe

      2 min read

    • UNLIMITED

      Make Office Better

      Mar 15, 2023

      OnlyOffice 7.3 (released in January) includes a set of new formulas that were already available in Microsoft Excel. Two of these formulas make it much easier to chop unwanted text from cells: TEXTBEFORE and TEXTAFTER. As its name suggests, TEXTBEFORE

      2 min read

    • UNLIMITED

      Make Office Better

      Sep 22, 2021

      Make Office Better
      2 min read

    • UNLIMITED

      Make Office Better

      Mar 29, 2023

      Make Office Better
      3 min read

    • UNLIMITED

      Make Office Better

      Nov 4, 2020

      Make Office Better
      2 min read

    • UNLIMITED

      MICROSOFT EXCEL & LIBREOFFICE CALC

      Aug 25, 2021

      When you have a large number of cells containing people’s names, you may find some are all lower case, others all upper case, and some are a mix of both. Using the PROPER formula in Excel and Calc you can quickly convert them all to title case (in ot

      1 min read

    • UNLIMITED

      Formatting in Numbers

      Nov 4, 2019

      Formatting in Numbers
      2 min read

    Related categories

    Reviews for Excel Functions for the Daily User

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Excel Functions for the Daily User - Palani Murugappan

      Syntax

      CHAPTER ONE

      cover

      PREFACE

      Good day!

      Welcome to my latest offering, Microsoft Excel Functions for the Daily User.

      Many books have been written on Microsoft Excel. However, not many emphasizes solely on Microsoft Excel’s built-in functions. This book does just that. The basics of Excel’s functions are introduced initially, covering functions such as SUM, MIN, MAX, AVERAGE, and the logical function IF. Readers are then guided to the more advanced functions, namely the Lookup and Reference functions. Examples of the VLOOKUP, HLOOKUP, OFFSET, INDIRECT, CHOOSE, and others are covered with many practical examples on how these functions can be applied. Common error messages are also explained and why it occurs.

      Instead of relying on text instructions to accomplish a task, this book shows you how and when the function is used. Along the way, the examples illustrated with graphics wherever possible, is used to enhance the understanding of the reader. Most of the examples are explained in detail and the resulting answer.

      Learning something new should always be exciting. That is the precise reason why this book would suit most people who know the basics of Microsoft Excel and want to tap on the power of its built-in functions to help in decision making.

      This book was written using Microsoft Excel XP. However, most of the functions covered in this book can also be used in Microsoft Excel 97 and 2000.

      So, do not wait to try the functions! Switch that PC and move on with the examples!

      Palani Murugappan

      [email protected]

      Part 1:Introduction

      Excel Functions – An introduction

      Excel consists of hundreds of built-in functions. When you install Microsoft Excel, these functions are installed too (with the exception of the Add-In functions).

      So, what then is a function? A function in Excel is a predefined formula that returns a specific result. It mostly performs calculations that would otherwise be difficult in doing it manually. Without the use of functions, the normal way of performing calculations is using the arithmetic operators such as plus sign ( + ), minus sign ( - ), multiplication or asterisk sign ( * ), division or forward slash ( / ), exponentiation or caret sign ( ^ ), and the percentage sign ( % ). Examples of these signs are illustrated below. Assume cells A1 and A2 have the values 6 and 3 respectively.

      With the built-in functions, you do not have to manually construct formulas using the above operators. For example, instead of manually calculating the sum of two or more numbers, you could use the SUM function to calculate the result. Similarly, to find the average of two or more numbers, you could use the AVERAGE function. These are simple examples only. More examples on the other functions available and how they will be used are explained later.

      Before proceeding to any of the examples, it should be noted that this book was written using the functions available in Microsoft Excel XP. As such, some of the screen layouts may be different in accessing the Insert Function (Excel XP) and Paste Function (Excel 97 and 2000) dialog boxes. However, despite the different versions of Excel, all produce the same result. It is only the manner of accessing the functions that differ.

      To understand how functions work, a simple exercise of calculating the summation of two numbers is explained. In this example, the SUM function is selected. Assume cell A2 has the value 6 and cell A3 the value 3. Select cell A4. This is where the value of the SUM function will be pasted.

      Before proceeding with the selection of the SUM function, one should know that functions in Excel fall into several categories. To view these categories, click the Insert Menu. Select Function.

      Alternatively, you could click the Insert Function ( ) button located on the Formula bar to activate the Insert Function dialog box. Both of the procedures will open the Insert Function dialog box as illustrated below.

      Note that in the above Insert Function dialog box, the last function used will be displayed on the top of the Select a function list. In this case, it is the SUM function. Your screen may show another function.

      To view the other function categories, click the Or select a category drop down list. This will produce the following categories as illustrated.

      Notice that for every function selected, there is a description of that selected function. For example, if the SUM function is selected, a brief description is outlined below the Select a function list as illustrated.

      A point to note is that all functions consists of two elements i.e. the function name and an argument list. In the above SUM function, the function name is SUM. The argument list is number1, number2, etc. The argument list usually includes one or more operands enclosed in parentheses. Depending on the argument, it can be numeric or text constant, or a single reference or multiple cell references.

      Coming back to the Insert Function dialog box, upon selecting the function, click the button. The Function Arguments dialog box is displayed.

      Observe that in the above Function Arguments dialog box, Excel has selected the range of the two cells and calculated the summation value of the cells A2 and A3. The argument list, in this case the values of 6 and 3 is selected as the first argument i.e. Number1. The final value of 9 is displayed as the Formula result.

      Also, observe the Formula bar. The selected function, SUM, is displayed with the argument list i.e. (A2:A3). This is illustrated below.

      Click the button to close the Function Arguments dialog box. The final result of 9 is displayed in cell A4 as illustrated.

      The above example illustrates the basis of using Excel functions. Some of the other points that you should know of functions is that all functions must be preceded by an equal sign ( = ). Next, the function name and arguments can be typed in either uppercase or lowercase characters. Multiple arguments must be separated by commas. Note that certain functions do not require arguments. However, it is still necessary to include and empty argument list i.e. ( ).

      Order of operators

      Before performing any calculations, you should know the order of the operators. For example, assume you have the following data in cells A1, A2, and A3 as illustrated.

      If you want to calculate the result of 6 + 3 x 2, the normal mathematical way of interpreting it is reading the numbers from left to right and performing the calculations one at a time. The answer to this is 18. However, in Excel, this method does not hold. Assume you had typed the formula as the way it is read in cell C1 i.e. this would be interpreted as =A1+A2*A3.

      Observe that the value 12 is returned in cell C1, which is not quite the expected answer. To get the right answer, use parentheses or brackets. Thus, the above formula should read as =(A1+A2)*A3. Type this formula in cell C2 and observe the result. It should be as illustrated below.

      The example above shows that if the brackets are not specified, Excel calculates the result of A2*A3 and adds this value to cell A1, which gives a final value of 12. Thus, it is important for you to know the order of the operators.

      Another example using the above data is calculating the negated value

      Enjoying the preview?
      Page 1 of 1