Microsoft Excel - Working with Formulas and Functions

Perhaps you’ve been struggling with your Excel document and just do not understand Formulas and Functions enough to get your task done & done.  Well maybe a few simple and basic How to Formulas and Functions tutorial steps can help. 

We’ll start with identifying the components of a worksheet.

Writing Formulas

A Formula is an instruction entered into a cell to perform a calculation. You create Formulas with hard numbers such as 1+2, or using cell references such as A1+B1, so that the results of a Formula automatically update when the data in the referenced cells change. A cell reference may refer to data in the current worksheet, data on a different worksheet within the same workbook, or data in another workbook.

You create Formulas by following a few simple steps:

• Select the cell where the result should display. This is called the destination cell.
• Type an equal sign (=) to begin the calculation. The equal sign allows Excel to distinguish a Formula from other cell values such as text or numbers. It also changes the worksheet status from Ready to Enter.
• Enter the first cell reference or number in the equation. Either type the cell reference from the keyboard or point to the cell to select it. When typing, the worksheet status is Enter. When pointing, the worksheet status is Point.
• Type the appropriate mathematical operator such as a plus or minus sign.
• Type or point to the next cell to be referenced. Repeat these steps until the Formula is complete.
• Finish the Formula by pressing ENTER or by clicking the Enter button  P on the Formula Bar.

Finishing the Formula returns the status of the worksheet to Ready.

Next, the mathematical operators you use in creating an Excel Formula are:

How much do you understand Functions?  Maybe this will help.

Excel provides hundreds of built-in Formulas, called Functions. The SUM Function is the most commonly used Function in Excel.  This syntax totals selected cells:

=SUM(FIRST CELL:LAST CELL)

The colon (:) between the two cell addresses indicates a range incorporating all cells between the first cell reference and the second cell reference, and includes the cell references listed in the range.

To add non-contiguous cells, separate cell addresses with a comma. The syntax is:

=SUM(FIRST CELL, NEXT CELL, LAST CELL)

Let’s start with Manually Entering Functions using SUM, AVERAGE, MIN and MAX

Using the Excel Spreadsheet below as an example, we will calculate the data using SUM, AVERAGE, MIN and MAX.  Also, see the Formula syntax in the example.

SUM Function:	In cell B11, type =SUM(B5:B9), and then click Enter (check mark on Formula Bar)                    The sum of 1,377 is displayed
AVERAGE Function:	In cell B13, type =AVERAGE( then use the mouse to select the range B5:B9.  Click Enter.                    The average is displayed as 275.4.                    Excel completes the final parentheses in the argument.
MIN Function:	In cell B15, type =MIN( then use the mouse to select the range B5:B9.  Click Enter.                    The lowest value in the range is displayed as 123.
MAX Function:	In cell B17, type: =MAX( then use the mouse to select the range B5:B9.  Click Enter.                    The highest number in the range is displayed as 678.

Adding and Subtracting Dates

All Excel dates are numbers that are formatted to display as dates. This allows Excel to add and subtract dates like other numbers.  The Formula syntax in the example will help when adding and/or subtracting Dates.

Adding Dates
1	Click cell C6
2	In cell C6, enter a Formula to subtract C4 from C5 (specify the later date first), then click Enter.  (check mark on Formula Bar)                                                                                       =C5-C4                                                           The number of days between the two dates displays

In cell C12, enter a Formula to add the values in cells C10 and C11, then click Enter.                                                                 =C10+C11                                                                 Excel calculates the due date

In cell C19, enter a Formula to add the values of cells B19 and B16, press F4 to make the reference to B22 absolute, and then click Enter.                                                 =B19+$B$16 (absolute reference)

Use the Fill Handle to copy the Formula in cell C19 to cells C20 and C21

Using the Current Date

The TODAY function displays the current date as set on your computer’s system clock. Use this function to display the current date, or to calculate deadlines or number of days from the current date. Since the result of this calculation is dependent on the system clock, there is no set argument for this function. The argument is represented by a set of blank parentheses.

=TODAY()

Today's Date

1.   Click cell to enter date and time

2.   On the Formulas tab, in the Function Library group, click the Date & Time options button

An alphabetical list of date and time functions displays.

3.   Select Today

Excel inserts =TODAY() in the cell and displays
 the Function Arguments message displays.

4.   Click OK 

The current date displays in the active cell. 

The Formula Bar displays =TODAY(). 

Today +30

Click cell to enter date, type =today()+30, and then click Enter.

30 days from today’s date displays in the current cell.

60 days prior to today

Click cell to enter date, type  =today()-60, and then click Enter.

60 days prior to today’s date displays in the current cell.

Calculating Years and Months

The DATEDIF function provides a way to calculate the number of months or years from a specific date. This is useful in calculating a person’s age or the number of months of employment.

NOTE: Although DATEDIF is a very useful date function, Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios.

The syntax of this function is:

=DATEDIF(start date, ending date, result format)

For example: =DATEDIF(A12, TODAY(), “Y”)

This function subtracts the starting date from the ending date and renders the results in either years (“Y”), months (“M”), or days (“D”). The commas separate the parts of the argument.

Calculating a Person’s Age

1.      Click cell B12, then to start the function, type  =DATEDIF(

2.      Click cell A12 to insert this cell reference as the starting date.

3.      Type a comma to separate the parts of the argument.

4.      Type today() to specify today’s date as the ending date.

5.      Type a comma to separate the parts of the argument.

6.      Type “Y” to specify the result in years (including the quotation marks).

7.      Type ) to close the argument.

8.     Click Enter.

                =DATEDIF(A12,TODAY(),"Y")
                 The age is displayed in years.

9.      Use the Fill Handle to duplicate this function for any remaining dates.

Calculating the Number of Months

1.      You want to determine how many months from the Start date to today

2.      Click cell F12, and to start the function, type =DATEDIF(

3.      Click cell E12 to insert this cell reference as the starting date.

4.      Type a comma (,) to separate the parts of the argument.

5.      Type today() to specify today’s date as the ending date.

6.      Type a comma (,) to separate the parts of the argument.

7.      Type “M” to specify the result in months (including the quotation marks)

8.      Type ) to close the argument, and then click OK.

                    =datedif(E12,today(),"M")

               The number of months is displayed

9.      Use the Fill Handle to duplicate this function for any remaining dates.

Excel 2016 was used to create these tips; however, the commands will work with prior versions of Excel.  I am a Microsoft Office Specialist certified trainer.  In my day-time life, I am a Technology Trainer in Los Angeles, California.

©2018 Radiance Smith (aka Radiance Lite)