Nothing Special   »   [go: up one dir, main page]
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.

    Microsoft Excel Functions Vol 2
    Microsoft Excel Functions Vol 2
    Microsoft Excel Functions Vol 2
    Ebook293 pages3 hours

    Microsoft Excel Functions Vol 2

    By Palani Murugappan

    ()

    Read preview

    About this ebook

    SIMPLIFIED ADVANCED EXCEL FUNCTIONS A step-by-step approach By Palani Murugappan
    LanguageEnglish
    PublisherBookBaby
    Release dateAug 8, 2014
    ISBN9789671275665
    Microsoft Excel Functions Vol 2

    Read more from Palani Murugappan

    Related to Microsoft Excel Functions Vol 2

    Related ebooks

    Teaching Methods & Materials For You

    View More
    View More

    Related podcast episodes

    Related articles

    • UNLIMITED

      Miscrosoft Excel

      Feb 8, 2021

      Miscrosoft Excel
      5 min read

    • UNLIMITED

      Make Office Better

      Mar 29, 2023

      Make Office Better
      3 min read

    • UNLIMITED

      Make Office Better

      Jun 7, 2023

      Make Office Better
      2 min read

    • UNLIMITED

      50 Ways To Work Faster

      May 11, 2023

      50 Ways To Work Faster
      19 min read

    • UNLIMITED

      50 Ways To Work faster

      Jul 18, 2023

      50 Ways To Work faster
      16 min read

    • UNLIMITED

      Make Office Better

      Jan 4, 2021

      Sync changes between documents Copying text from one document to another isn’t difficult - you can press Ctrl+C (copy) then Ctrl+V (paste). But what if the text you’ve previously copied regularly changes (such as with a running total in a fundraising

      3 min read

    • UNLIMITED

      50 Ways To Work Faster

      Jun 19, 2023

      50 Ways To Work Faster
      19 min read

    • UNLIMITED

      Make Office Better

      Dec 4, 2024

      LibreOffice 24.8, released in August, added the XMATCH function to Calc, making it a more credible alternative to Excel. At its simplest XMATCH lets you find the position of a specific item within a range of cells. But you can perform more complex ta

      2 min read

    • UNLIMITED

      Get Started With PCalc

      Apr 19, 2021

      Get Started With PCalc
      2 min read

    • UNLIMITED

      Get Started With PCalc

      Feb 9, 2021

      IT WILL TAKE 5-10 minutes YOU WILL LEARN How to use PCalc’s basic features and start using the app YOU’LL NEED PCalc (£9.99), OS X 10.9 or later If you want to run some calculations and conversions on your Mac, Apple’s Calculator app and even Spotlig

      2 min read

    • UNLIMITED

      Make Office Better

      Dec 21, 2022

      Make Office Better
      2 min read

    • UNLIMITED

      Make Office Better

      Dec 7, 2022

      Make Office Better
      2 min read

    • UNLIMITED

      Make Office Better

      Mar 30, 2022

      The latest version of LibreOffice (7.3, released in February) lets you add hyperlinks to shapes you create in Writer. First, add your shape by clicking the Insert tab and selecting Shapes. Now click Insert again, select Hyperlink, then type your URL

      2 min read

    Related categories

    Reviews for Microsoft Excel Functions Vol 2

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Microsoft Excel Functions Vol 2 - Palani Murugappan

      2

      PREFACE

      Hello once again!

      Welcome to the second of my Microsoft Excel Functions offering titled Simplified Advanced Excel Functions.

      Many books have been written on Microsoft Excel. However, not many emphasize on Microsoft Excel’s advanced built-in functions. This book does just that. It covers functions such as FV, PV, NPV, PMT, NPER, IRR, SLN, VDB, NOW, DATE, WEEKDAY, CONCATENATE, FIND, REPLACE, and many more.

      Readers are then guided to these functions using a step by step approach. The book is catered for new and experienced Excel users. New users should not feel intimidated by these functions.

      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 these advanced functions! Turn on your PC and get a head start with the examples!

      Palani Murugappan

      palani12@yahoo.com

      DEDICATION

      For Senthi Rajah

      To whom I have always looked up as a fatherly figure

      Functions in Excel

      Functions in Excel are a defined set of formulas that is used to perform calculations using specific values known as arguments, in a particular order or structure. Functions accept a number or numbers as arguments and return a value. This value can be another number or a logical value such as TRUE / FALSE.

      Excel’s functions are categorized into many categories such as Financial, Date & Time, Mathematics & Trigonometry, Statistical, Lookup & Reference, Database, Text, Logical, and Information.

      This book will emphasize on some of the above categories, namely Financial, Date & Time, and Text.

      All examples and exercises in this book were accomplished by using Microsoft Excel XP.

      Invoking a function

      The quickest way to invoke a function is by clicking the Insert Function ( ) icon button located to the left of the Formula Bar, as illustrated below.

      Alternatively, instead of the above, click the Insert Menu. Select Function.

      This displays the Insert Function dialog box as illustrated.

      To view the categories available, click the Select a category drop down list. A complete list of all the categories available is displayed.

      Select a category. For example, select the Financial category. A complete list of all the financial functions available is then displayed in the Select a function list box as illustrated.

      For the purpose of illustration, assume the function IRR is selected. Clicking the button will then display the Function Arguments dialog box as illustrated.

      This is where you key in the values for the arguments as in the above.

      Once you become familiar with some of the commonly used functions, you need not go through this procedure. Rather, you may type in the formula of the function directly into a specific cell. For example, instead of using the Function Arguments dialog box to insert the IRR function, you could select a specific cell, for example C4 and type the function formula as illustrated.

      Press the Enter key. The IRR value returned is 53% (in this example)

      Accessing Microsoft Excel Help

      There will come a point in time when you need to use a function or gather more information of a function that you want to use. The best way to do this is to use the built-in Microsoft Excel Help. To access this, click the Help Menu. Select Microsoft Excel Help as illustrated.

      The What would you like to do? help text box is displayed. Type your command here in the text box provided. For example, to gather further help on the IRR function, type IRR as below.

      Click the button. Another identical help text box with options for the IRR function is displayed.

      Click on the IRR worksheet function option. The Microsoft Excel Help window displays the information for the IRR function as illustrated.

      Using the above steps, you can search for more information or help on other Excel functions and features.

      Financial functions

      Excel’s financial functions are of great help to users who frequently use complex financial equations manually. Many a times, most of these complex equations or formulas can be replaced with the appropriate financial function.

      For example, Excel provides several financial functions that help solve problems associated with annuity. An annuity is a series of regular cash flows over a period of time. An example of annuity is the cash flow for a car repayment that occurs according to a regular time interval or period.

      Excel also includes financial functions to analyze uneven cash flows and to calculate depreciation using the double-declining balance depreciation method.

      FV function

      The future value (FV) function is used to calculate the future value of an investment based on periodic intervals, constants repayments, and constant interest rate. It is what a lump sum or series of cash flow will grow to by the end of a specific time period taking the interest factor into account.

      The mathematical equation for the future value can be described as:

      FV = (-pmt + (1+rate)nper + rate * (1+rate)nper * pv)/rate

      The syntax for the FV function consists of five arguments and is as follows:

      FV(rate, nper, pmt, pv, type)

      The first argument rate is the interest rate per period which is constant.

      The second argument nper is the total number of payment periods in an annuity.

      The third argument pmt is the constant payment made for each period and cannot change over the life of the annuity. It would normally contain the principal and interest but no other fees or taxes.

      The fourth argument pv is the present value (PV). It is the lump sum amount that a series of future payments is worth today. If pv is omitted, it is assumed to be zero.

      The fifth and last argument type indicates when payments are due and is denoted by a 0 (payment due at the end of period) or 1 (payment due at the beginning of period). Also, if type=0, then interest is applied and payment due at the end of the month (assuming payment is on a monthly basis). An example of this is the case of credit card payments where the interest on the account is not applied until the period's end (nor is the payment due).

      It should be noted that for all arguments the cash paid out is represented by negative numbers, and cash received such as dividends is represented by positive numbers.

      To understand the use of the FV function, assume the following data set is provided.

      Based on the above data, what would be the future value for the amount deposited ($50,000) based on the interest rate of 8.75%?

      The above data can be translated to the following arguments.

      To calculate the future value, assume cell B12 is selected.

      Click the Insert Function ( ) icon button. The Insert Function dialog box is opened. Select the Financial category followed by the FV function as illustrated.

      Click the button. The Function Arguments dialog box is opened.

      Click the Collapse ( ) button to collapse the Function Arguments dialog box. Click on cell B6 to select the interest rate.

      Click the Expand ( ) button to expand the Function Arguments dialog box.

      Click to select the Nper text box. Collapse the dialog box. Select cell B7.

      Expand the dialog box.

      Similarly, using the above steps to expand and collapse the Function Arguments dialog box, select cell B8 for the Pmt value.

      For the Pv value, select cell B8, which is the amount deposited.

      Finally, for the final argument

      Enjoying the preview?
      Page 1 of 1