Tag Archives: Excel Functions

Get more out of formulas in Excel using functions

It’s easy to fall into a habit, and manually type in a formula and copy and paste it within a worksheet. If so, you may not be getting the most out of the Formula tab on the Ribbon. From here, you can save yourself time by using Excel’s ready made formulas using Functions.

The Formulas tab has the regularly used calculations and set them up as functions in 12 different categories.  So you have these at your fingertips to apply to a range of cells that you specify.

The 12 categories include: financial, date and time, maths and trig, statistical, database, text, look up and reference, logical, information, engineering, cube and compatibility.

Each of these categories has a drop down list with a selection of common functions, ready for you to apply to your selected cells.

functions-microsoft-excel-training-course

The Formula tab has functions ready for you to apply.

For example, if you need to calculate the cost of loans, or interest, yield and depreciation, you can access these functions via the Financial Function.

If-then statements are in the Logical Functions – so you can analyse data setting up logical statements, setting out whether a value is true or false, or using AND or OR.

If you regularly want to work out averages, minimums, maximums, or interest rates, these are stored in the Statistical Functions.

To get the most of Excel’s functions, it is worth exploring what the Formulas tab has to offer.  An excellent way to find out more and learn how to use them is to attend a  Microsoft Excel training course, so you can see what is available and practise using them before using them on your own live data.  From Intermediate Excel to Advanced and beyond, our courses cover Formulas and Functions in more detail http://www.microsofttraining.net/excel-training-london.php

Using COUNTIFS with Excel 2003

Excel 2007 introduced the function COUNTIFS to count cells based on multiple criteria.

For example suppose you wish to count how many times the client Fowler buys more than 250 shares. The answer for tha data below turns out to be 2 using the Countifs function entered in B13.

Similarly for Owen the result turns out to be 1. 

 

 

 

 

 

 

 

 

 

Even though COUNTIFS is not availailable with Excel 2003 there is an alternative way to perform the same calculation using the SUMPRODUCT array formula.

The formula

 =SUMPRODUCT(–(A2:A11=”Fowler”),–(C2:C11>=250))

typed into C13 produces the same result. The mysterious looking — operator calculates if true or false for each of the cells in the range A2:A11 returning 1 if true and 0 if false. The same goes for the range C2:C11 and the Sumproduct array function sums all the true, true (1*1) combinations.

Note – To show how – works try typing –(A2:A11=”Fowler”) into a cell followed by Ctrl+Shift+Enter. This convets the formula into the array formula {=(A2:A11=”Fowler”)}

Excel 2010 Basics and Formulas

Like an interactive sheet of graph paper, Excel spreadsheets are divided into rows and columns that intersect to create boxes known as cells. Columns are labeled alphabetically (A, B, C…), and rows are labeled numerically (1, 2, 3…). You can enter alphanumeric data into any cell and use formulas and functions to perform calculations on that data. Then you can create charts and graphs to display the results.

For example, to create a simple monthly household budget, just type “Budget” in cell A1, and press ENTER. In cells A3 to AX (X stands for the row number of the final cell you fill with data), enter common expense categories such as mortgage, rent, food, utilities, and entertainment. In cells B3 to BX, enter projected expenses. Two cells below BX (if X equals 15, this cell would be B17), enter the formula to total your expenses: =SUM(B3:B15). Or on the ribbon, on the Formulas tab, click the Insert Function button Insert Function button or AutoSum symbolAutoSum symbol button in the ribbon to access this function. Then when you adjust any number in cells B3 to BX, you will see your expense total change automatically.

Note that Excel offers a full range of custom programmable formulas that can be inserted to organize and compute data. These instructions can be used to process simple functions such as adding, subtracting, and multiplying values or calculate dates, dollar amounts, and averages. Use more advanced formulas to generate auto-formatted findings, change text from uppercase to lowercase, or combine data from multiple columns into a cohesive whole. Learn how to access common Excel.