Everand Logo

Welcome to Everand!

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

    Only €10,99/month after trial. Cancel anytime.

    50 More Excel Functions: Excel Essentials, #4
    50 More Excel Functions: Excel Essentials, #4
    50 More Excel Functions: Excel Essentials, #4
    Ebook176 pages1 hour

    50 More Excel Functions: Excel Essentials, #4

    By M.L. Humphrey

    ()

    Read preview

    About this ebook

    Microsoft Excel is an incredibly powerful tool. And even knowing just the basics of how to use it can help tremendously with your career and your finances.

     

    But mastering Excel functions can help you take that basic understanding to an entirely new level.

     

    In 50 More Excel Functions, M.L. Humphrey continues with the work started in 50 Useful Excel Functions and walks you through fifty more useful Excel functions.

     

    By the time you finish this guide you will understand how formulas and functions work within Excel, have a solid grasp of fifty more Excel functions, know how to find other functions you might need, and know how to troubleshoot a function that isn't working for you.

     

    So if you're ready to take your understanding of Excel to that next level, then this is the book for you.

    LanguageEnglish
    PublisherM.L. Humphrey
    Release dateJan 1, 2019
    ISBN9781386049951
    50 More Excel Functions: Excel Essentials, #4
    Author

    M.L. Humphrey

    Hi there Sci Fi fans, my name is Maurice Humphrey. I am a Vermont native, husband, father, grandfather, well over 60, Navy veteran, retired IBM engineer, retired printer repairman, Graduated: Goddard Jr. College, VT Technical College, and Trinity College. Over the years I've written technical articles, taught technical classes, and presented at technical conventions. I've been reading science fiction for over 50 years now. First books were "Journey to the Centre of the Earth" by Jules Verne and "The Stars Are Ours" by Andre Norton. I've read and collected many great stories, and a considerable amount of junk ones as well. I'd say by now that I probably have a good idea of what I consider a good story.

    Read more from M.L. Humphrey

    Related to 50 More Excel Functions

    Titles in the series (4)

    View More
    View More

    Related ebooks

    Teaching Methods & Materials For You

    View More
    View More

    Related podcast episodes

    Related articles

    • Article

      Make Office Better

      Sep 22, 2021

      Make Office Better
      2 min read

    • Article

      Miscrosoft Excel

      Feb 8, 2021

      Miscrosoft Excel
      5 min read

    • Article

      See Ways To Illustrate Your Data

      Sep 6, 2021

      Microsoft has launched a tool in Excel that provides suggestions on how to illustrate your data. To use it, you’ll either need to be running Excel 2019 or the online version of Excel at www.snipca.com/39295 (it works with free accounts – click ‘Creat

      1 min read

    • Article

      Add Data Bars To Cells

      Jul 28, 2021

      Add Data Bars To Cells
      1 min read

    • Article

      Summary Sheet In Numbers

      Sep 24, 2019

      Summary Sheet In Numbers
      2 min read

    • Article

      Create An Attractive Data Dashboard In Excel

      Sep 22, 2021

      What you need: Windows 7, 8.1 or 10; Excel 2010 onwards Time required: 20 minutes A dashboard is a great way to visualise the data in your Excel spreadsheets, so you can track and analyse the information they contain. Dashboards can include charts, g

      3 min read

    • Article

      Make Office Better

      Aug 25, 2021

      MICROSOFT WORD & EXCEL Regular Wikipedia users should consider installing an Office add-in that adds content to your your documents and spreadsheets. First, open Word or Excel, then click the Insert menu, followed by ‘Get Add-ins’. In the screen that

      2 min read

    • Article

      Copilot Pro For Excel

      Mar 7, 2024

      Unlike the other Copilot Pro tools, Copilot for Excel is labelled prominently as “beta”. But even in this qualified state, it has the promise of being a game-changer for anyone who needs to work with data but doesn’t want to become an expert in writi

      2 min read

    • Article

      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

    • Article

      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

    • Article

      Make Office Better

      Nov 4, 2020

      Make Office Better
      2 min read

    • Article

      Make Office Better

      Mar 29, 2023

      Make Office Better
      3 min read

    • Article

      50 Ways To Work faster

      Jul 18, 2023

      50 Ways To Work faster
      16 min read

    • Article

      50 Ways To Work Faster

      May 11, 2023

      50 Ways To Work Faster
      19 min read

    Related categories

    Reviews for 50 More Excel Functions

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      50 More Excel Functions - M.L. Humphrey

      HOW FORMULAS AND FUNCTIONS WORK

      If you are writing a basic mathematical formula in Excel you do so by starting your entry in a cell with a plus (+), a minus (-) or an equals (=) sign. Unless you have a good reason for doing so, like years of ingrained habit, I recommend just using the equals sign.

      So if I want to add two values together in Excel, I would enter something like this into the cell:

      =2+3

      I could also do so using cell references if those values were already stored in cells in my worksheet:

      =A1+B1

      (If you aren’t familiar with cell notation in Excel, see Appendix A.)

      When I hit Enter or otherwise leave that cell, Excel will display the result of the formula in the cell. In the top example, that means it would display the value 5 in the cell. Excel will, however, retain the formula that was used to calculate that value. You can either double-click in the cell or click on the cell and look to the formula bar to see the formula.

      Formula Bar

      Excel formulas can use basic mathematical notation or they can use functions to perform specified tasks.

      To add two numbers together in Excel you use the plus (+) sign between the values like I did above. To subtract one number from another you use the minus (-) sign. To multiply two numbers you use the asterisk (*) sign. To divide two numbers you use the forward slash (/). So:

      =3-2 would subtract 2 from 3

      =3*2 would multiply 3 times 2

      =3/2 would divide 3 by 2

      As I mentioned above, your formulas can either use cell references or numbers. So:

      =A1-B1 would subtract the value in Cell B1 from the value in Cell A1

      =A1*B1 would multiply the value in Cell A1 by the value in Cell B1

      =A1/B1 would divide the value in Cell A1 by the value in Cell B1.

      Excel can handle as complex a formula as you want to throw at it. You can combine in one cell a formula that adds multiple values, divides values, subtracts values, and multiplies values as well as any number of other mathematical tasks or functions.

      If you’re going to combine calculation steps within one cell, you need to be careful that you properly place your parens so that calculations are performed in the correct order. There is a help document on this titled Calculation operators and precedence in Excel that lists the order in which calculations are done by Excel and also lists a number of operators (such as > for greater than) that are useful to know when working with formulas and functions in Excel.

      If you’re building a really complex formula it’s always a good idea to test it as you go to make sure that all of the components are working properly and that the end result is the expected result. So I will build each component separately before combining them all in one cell.

      Formulas in Excel go beyond the basic mathematical formula you learned in school. They can handle date-based, text-based, and logic-based calculations as well as mathematical calculations. They do this through the use of Excel functions.

      Functions are essentially programmed shortcuts that do specific tasks. For example, the SUM function will add all of the values in a range of cells that you identify. Or the CONCATENATE function will take a set of inputs (usually text) and combine them together in one cell.

      There are hundreds of functions in Excel that you can use in your formulas.

      To use a function, you start a formula with the equals sign, type in the name of the function, use an opening paren, provide the inputs required for that function, and then use a closing paren.

      So to sum a range of cells from A1 through A3, you would type

      =SUM(A1:A3)

      The equals sign tells Excel this is a formula, the SUM portion tells Excel that we’re using the SUM function, the opening paren says we’re going to list inputs for that function, the A1:A3 tell Excel which cells to apply the function to, and then the closing paren says that’s the end of the function. It doesn’t have to be the end of the formula.

      (As we’ll discuss at the end, you can combine functions within one formula.)

      I could have

      =SUM(A1:A3) + SUM(C1:C3)

      That’s sloppy notation. I could as easily have written =SUM(A1:A3,C1:C3) and had the same result. But the point here is that a formula starts with an equals sign and then you use functions as part of that formula by using their name followed by opening and closing parens and providing the required information for the particular function within the parens.

      Don’t worry. We’re going to walk through lots and lots of examples of this. You’ll get it if you don’t now.

      Just remember to think of a function, whether it handles text or is logical or performs a mathematical function, as part of a formula. In other words, as part of something that is being calculated based upon your inputs.

      Garbage in, garbage out. If you give the function the wrong inputs, you will get the wrong results. So if you get an error message (which we’ll discuss at the end) when using a function, check that the information you input into your formula is formatted properly and is of the right type. That’s usually where things go wrong.

      Alright. Next we’ll talk about where to find functions in Excel. But remember, you need an equals sign (=) to start a formula and then you can use numbers, cell references, operators, or functions to build that formula.

      WHERE TO FIND FUNCTIONS

      In this guide we’re going to cover fifty Excel functions, but there are far, far more functions than that in Excel. And chances are at some point you’ll need one I didn’t cover here.

      In newer versions of Excel, you can go to the Formulas tab to see what Excel functions are available to you. There is a section called Function Library that lists various categories of functions. Mine shows Recently Used, Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, and then there’s a dropdown for More Functions that shows the categories Statistical, Engineering, Cube, Information, Compatibility, and Web.

      Formulas Tab

      Click on the dropdown arrow next to any of the categories and you’ll see a listing of functions that fall under that heading.

      Now, unless you know what you’re looking for, this listing probably won’t help you much because the functions are named things like ACCRINT and IFNA. You can hold your cursor over each of the names and Excel will provide a brief description of the function for you, but for some of the lists that’s a lot of functions to look through.

      Function dropdown description

      Each description also includes a Tell Me More at the end of the description. If you click on that option, the Excel Help screen will appear. You can then click on Excel functions (alphabetical) and choose your desired function from the list. This will show you additional information on the function and how it works.

      Excel Help for AND function

      Instead of that, I would recommend that you use the Insert Function option which is also available in the Formulas tab on the far left-hand side.

      Be sure you’re clicked into an empty cell on your worksheet and then click on Insert Function. This will bring up the Insert Function dialogue box.

      Insert Function dialogue box

      In the top section under where it says Search for a function you can type what you’re looking to do and then click on Go. (Be sure that the category dropdown right below the search box is set to All unless you know for certain what category your function falls under.)

      Excel will provide a list of functions that it thinks meet your search criteria. (Sometimes this list is very far off, so don’t just accept the first choice blindly.) You can left-click on each of the listed functions to see a brief description of the function. This appears below the box where the functions are listed.

      You will also see for each function a list of the required inputs for that function.

      For COUNTIFS you can see in the screenshot above that the first input required is the criteria range and that the second input required is the criteria and that the description of the function is Counts the number of cells specified by a given set of conditions or criteria.

      (In this guide I have listed this information for each function at the top of the function’s page.)

      If you need more information on a function, you can click on the Help on this function link in the bottom left corner of the dialogue box. This will bring up the Excel Help box for that particular function.

      Otherwise, you can just click on the function you want and choose OK.

      This will insert the function into whichever cell you’d been clicked into before you chose Insert Function. You

      Enjoying the preview?
      Page 1 of 1