7 Cool ‘Must Know’ Excel functions

| February 22, 2012 | 0 Comments

At its heart, Excel 2010 is a great tool for making calculations and Excel functions are the key to getting the work done fast. Here I’ll show you some of the most effective functions that Excel has to help you make calculations using worksheet data.

1          Calculating workdays

To calculate the number of days between two dates and taking into account holidays use the =NETWORKDAYS function. Start by placing the dates for the holidays in a range of cells across a row or down a column. Select this range and name it holidays using Formulas > Define Name.

calculating number of workdays

This function will calculate the number of workdays between two dates placed in cell A1 and A2, taking into account the days you’ve described as being holidays:

=NETWORKDAYS(A1,A2,Holidays)

function =NETWORKDAYS(A1,A2,holidays)

If the NETWORKDAYS function returns an error make sure that you have the Analysis Toolpak installed by choosing File > Options > Add-ins > Excel Add-ins > Go.

trouble shooting NETWORKDAYS function

Make sure the checkbox for the Add-in is selected:

select analysis toolpack

2          Calculating Averages

The AVERAGE function can be used to calculate the average of a series of numbers. The syntax is =AVERAGE(StartCell:EndCell), where StartCell is the first cell in the range and EndCell is the last cell.

Be aware that when you make an AVERAGE calculation, blank (empty cells) are ignored. So, for example, the average of four cells, three of which contain the number four and one of which is blank, is four.

If you place a zero in the empty cell then the Average is 3. If you intend blank cells to represent the value zero you should place a zero in all empty cells before making the calculation.

blank cells vs zeros

3          Date functions

Two of Excel’s functions =NOW() and =TODAY() allow you to insert the current date and time or just the current date into a cell. If you do this and see a number of around 39,000 this is the number of days since the 1st of January 1900 and is how dates are calculated. Simply format this number as a date using the Format > Cell > Number Format options.

4          Referring to a cell

One difficulty which stumps even seasoned Excel users is how to refer to the contents of one cell in the current cell. For example, if cell A2 should contain the same contents as cell C2 simply type =C2 in cell A2. Linking cells like this ensures that when the value in C2 changes, the value in cell A2 will change to match it. You can achieve a similar result across worksheets by prefixing the cell reference with the worksheet name. For example, this formula refers to the contents of cell C2 on Sheet1: =Sheet1!C2.

5          Multiply and Add

Often when you are making calculations you need to multiply the contents of one column by the values in a second column and then add the results. You might do this, for example, if you have a list of product numbers in stock and you need to multiply these by the cost price and sum it to obtain an inventory value. To do this use the SUMPRODUCT function, the syntax of which is =SUMPRODUCT(FirstRange,SecondRange). For example, =SUMPRODUCT(A2:A25,B2:B25) will multiply each of the cells in the range in column A by the corresponding cell in the range in column B and then total the result.

6          Making choices

The IF function lets you make a choice in your worksheet. For example, if cell A2 contains a value of Y or N depending on whether a discount is applicable, you can use the value in that cell to calculate the discount in another cell. If the discount is 10% this formula will calculate the discount on a price in cell A3:

=IF(A2 =”Y”,0.1*A3, 0)

The formula checks cell A2 to see if a discount is to be allowed and, if so, the discount is calculated using the total price in cell A3. If not, the discount is set to 0.

7          Finding Roots

Excel has a special function SQRT for finding the square root of a number. So, for example, the square root of 25 can be calculated using =SQRT(25). There is, however, no corresponding function for calculating the cube or any other root of a number. You can, however, calculate this using a simple formula if you know that the square root of 25 can be written mathematically as =25^(1/2). By extension, the cube root can be calculated by typing =25^(1/3).

Tags: , , , , , , , , , , ,

Category: Excel

About the Author ()

Helen Bradley is a lifestyle journalist specializing in Photoshop, Lightroom, photo editing, web design, Visual Basic and Office software. She writes how to articles, tips and projects and produces how to videos for computer magazines, newspapers and online services in the USA, Australia, Canada and the UK. She writes for PC World, SmallBusinessComputing.com, Practical Photoshop and Digital-Photography-School.com.

Leave a Reply