Excel: Advanced Formulas and Functions

Excel provides an enormous number of established formulas and assistance in auditing and calculating your data. The primary groupings are financial, logical, text, date and time, lookup and reference, math and trigonometry, statistical, engineering, cube, and file-related information.

alt text


Contents

Financial Formulas

Financial functions are probably one of the most commonly used groups. You can calculate payment plans, interest rates, depreciation, and the yield on securities (just to name a few!). Excel simplifies the process by providing fill-in-the-blanks.


The following example returns a loan payment. The lower chart shows the balance if you pay a different amount.

Enter principle, interest and term in the yellow highlighted cells (C1, C3, C4). The PMT formula should refer to these cells and look like this: =PMT(C3/12,C4,C1). Enter the “actual payment” amount in D2.

Copy the following formulas into the table and drag down to populate the table.

  • In cell E7 enter "=C1"
  • In cell B8 enter "=$D$2"
  • In cell C8 enter "=(E7*$C$3)/12"
  • In cell D8 enter "=B8-C8"
  • In cdll E8 enter "=E7-D8"

Image:Xls2 loan.jpg

Text Functions

Concatenate

The concatenate function strings together the contents of a series of cells (text1, text2). The order that you select the cells is the order that they are combined into the resulting cell.

Syntax: CONCATENATE(text1,text2,...)

Shortcut: The symbol “&” can also be used instead of the concatenate function (=A2&B2).

Example The following examples combines fields to create FullName and Address fields.

Cell Formula C2 = CONCATENATE(A2," ",B2) note that [text2] is [quote space quote] G2 = CONCATENATE(E2,”, TX”,F2) note that [text2] is [quote comma space TX space quote]

Image:Xls2 concatenate.jpg

Left, Right

LEFT and RIGHT are useful if you wish to remove extra characters from a cell AND if you are able to specify how many characters to remove from the left or right. The formula requires the cell reference (text) and the number of characters to return (num_chars).

MID performs a similar task of returning reduced characters. This function contains 3 qualifiers: cell reference (text), the position of the character where you wish the text to begin (start_num), and the number of characters to return (num_chars).

Syntax: =LEFT(text,num_chars) or RIGHT(text,num_chars) =MID(text,start_num,num_chars)

Example Cell B2: =LEFT(A2,5) Cell E2: =MID(A2,1,5)

Image:Xls2 left-mid.jpg

Conditional Functions

Conditional functions, like conditional formatting, are great features to help you highlight or manipulate select information based on specified criteria. Excel evaluates the source against the criteria, and returns a value if the logical test is “true” and a different value for “false”. In the same way, Excel will perform a function, like adding or counting, based on the logical test.

  • The elements “value_if_true” and “value_if_false” may be a static value or another formula.
  • Up to 7 functions may be nested to create some very elaborate tests.
  • If, Countif, and Sumif perform the logical test using single criteria.
  • Countifs, and Sumifs perform the logical test on a range of cells that meet multiple criteria.

If

IF is straightforward. The reference cell is tested against criteria and will return a value or perform another function if the test returns true or false. “Logical_test” includes both the cell reference and the criteria, such as “B4 is less than 20.”

Syntax: IF(logical_test,value_if_true,value_if_false)

Example In this example, we are testing against the width of a book. If the width is under .375 in (3/8 in), the book requires a pamphlet binding (pam). If the width were equal or over .375 in, the book would require library binding (LB).

Cell Formula C2 =IF(B2>0.375,"pam","LB")

Image:Xls2_if.jpg

Countif, Countifs

Countif and Countifs literally count the number of times the test returns “true.” Other “count” functions: count – counts the number of cells that contain numbers, counta – counts the number of cells that are not empty, and countblank – counts the number of empty cells.

Syntax: COUNTIF(range,criteria) COUNTIFS(range1,criteria1,range2,criteria2,...)

Example: In the next example, we are counting how many books require 1) pamphlet binding (pam), 2) Library Binding (LB), and 3) how many books need both Library Binding and Spine Repair (LB/Repair).

Cell H2 =COUNTIF($C$2:$C$5,"pam") Cell H3 =COUNTIFS($D$2:$D$5,"good",$C$2:$C$5,"LB") Cell H4 =COUNTIFS($D$2:$D$5,"poor",$C$2:$C$5,"LB")

Image:Xls2 countif.jpg

Sumif, Sumifs

These functions return a sum of numbers that meet specified criteria. Range is the range of cells to compare against the criteria. A range of cells presented in this way: A2:A100 and an entire row looks like this: A:A. Criteria may be a value or range (“=30” or “=black” or “<2009”). Sum_range is used if the actual items to be added are in a different range than the compared range. If nothing is entered here, the original range is summed.

Syntax: SUMIF(range,criteria,sum_range) SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

note: see that the sum_range comes FIRST in the SUMIFS formula.

Example: Calculate the cost of book repair based on condition of the item.

Image:Xls2 sumif.jpg

More Functions

There are many extremely useful functions - following are just a few more examples. Search the Excel Help for “functions” and you’ll find the “List of all functions by category” for a full list of statistical, database, math, financial, and many, many, many more function types.

Len

Syntax: =LEN(text)

  • Returns the number of characters in a text string – spaces count as characters. Suggestion: use to determine lengths of each line of address on a label. The US Post office only allows 46 characters per line for mass mailings (as of 2008). Another use is to determine number of characters in a text block for web or print content.

Proper

Syntax: =PROPER(text)

  • Capitalizes the first letter of every word (as in “Rebecca Holte”).

Trim

Syntax: =TRIM(text)

  • Removes extra spaces from text strings – leaves a single space between words (“Rebecca       Holte” = “Rebecca Holte”).

Rounding

  • Adding/multiplying numbers obtained from formula sums, you may see different values than expected, due to the how many decimal points are used and when rounding occurs. You may wish to use a rounding or even/odd function. For “number” you can enter an actual number or cell reference, and “num_digits” indicates how many decimal places you require.

Roundup and Rounddown Syntax: = ROUNDUP(number,num_digits)

Even and Odd Syntax: =EVEN(number)