Age Calculation

You can calculate a persons age based on their birthday and todays date.

The calculation uses the DATEDIF() function.

The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.

(Makes you wonder what else Microsoft forgot to tell us!)

You can put this all together in one calculation, which creates a text version.

Age is 51 Years, 2 Months and 30 Days

="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Another way to calculate age

This method gives you an age which may potentially have decimal places representing the months. If the age is 20.5, the .5 represents 6 months.

ABS

What Does it Do ?

This function calculates the value of a number, irrespective of whether it is positive or negative.

Syntax =ABS(CellAddress or Number)

Formatting

The result will be shown as a number, no special formatting is needed.

Example

The following table was used by a company testing a machine which cuts timber.

The machine needs to cut timber to an exact length.

Three pieces of timber were cut and then measured.

In calculating the difference between the Required Length and the Actual Length it does not matter if the wood was cut too long or short, the measurement needs to be expressed as an absolute value.

Table 1 shows the original calculations.

The Difference for Test 3 is shown as negative, which has a knock on effect

when the Error Percentage is calculated.

Whether the wood was too long or short, the percentage should still be expressed as an absolute value.

COUNTIF

What Does It Do ?

This function counts the number of items which match criteria set by the user.

Syntax

=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)

The criteria can be typed in any of the following ways.

To match a specific number type the number, such as =COUNTIF(A1:A5,100)

To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,"Hello")

To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5,">100")

Kindly post your Comment below.

AutoSum Shortcut Key

Most of us may be using this option.

Instead of using the AutoSum button from the toolbar,

you can press Alt and = to achieve the same result.

Try it :

Move to a blank cell in the Total row or column, then press Alt and =.

or Select a row, column or all cells and then press Alt and =.

SUM using names

You can use the names typed at the top of columns or side of rows in calculations simply by typing the name into the formula.

Try this example:

Go to cell C16 and then enter the formula =SUM(jan)

The result will show.

This formula can be copied to D16 and E16, and the names change to Feb and Mar.

I would request you to post here in the comment section.

Show all formula

You can view all the formula on the worksheet by pressing Ctrl and `.

The ' is the left single quote usually found on the key to left of number 1.

Brackets in formula

Sometimes you will need to use brackets, (also known as 'braces'), in formula. This is to ensure that the calculations are performed in the order that you need. The need for brackets occurs when you mix plus or minus with divide or multiply.

Mathematically speaking the * and/are more important than + and - .
The * and / operations will be calculated before + and -.

Example1: The Wrong answer !

10
20
2
Total 50 = C12+C13*C14
You may expect that 10+20 woould equal 30 and then 30*2 would equal 60

But because the * is calculated first Excel sees the calculation as 20*2 resulting in 40 and then 10+40 resulting in 50

Example 2: The correct answer.

10
20
2
Total 60 = (C27+C28)*C29

By placing brackets around (10+20) Excel perfoms this part of the calculation first, resulting in 30 Then the 30 is multipled by 2 resulting in 60.

Time Calculation

Excel can work with time very easily. Time can be entered in various different formats and calculations performed. There are one or two oddities, but nothing which should put you off working with it. See the TimeSheet example for an example.

Typing time

When time is entered into worksheet it should be entered with a colon between the hour and the minutes, such as 12:30, rather than 12.30

1:30 12:30 20:15 22:45

Excel can cope with either the 24hour system or the am/pm system. To use the am/pm system you must enter the am or pm after the time. You must leave a space between the number and the text.

1:30 AM 1:30 PM 10:15 AM 10:15 PM

Finding the difference between two times

You can subtract two time values to find the length of time between.

Start End Duration

1:30 2:30 1:00 =D24-C24

8:00 17:00 9:00 =D25-C25

8:00 AM 5:00 PM 9:00 AM

If the result is not shown correctly, You may need to reformat the answer. Look at the section about formatting further in this worksh

eet.

Adding time You can add time to find a total time.

This works well until the total time goes above 24 hours.

For totals greater than 24 hours you may need to apply some special formatting.

Start End Duration

1:30 2:30 1:00

8:00 17:00 9:00 7:30 AM 5:45 PM 10:15

20:15

Formatting time

When time is added together the result may go beyond 24 hours. Usually this gives an incorrect result, as in the example below. To correct this error, the result needs to be formatted with a Custom format.

Example 1 : Incorrect formatting

Start End Duration

7:00 18:30 11:30

8:00 17:00 9:00

7:30 17:45 10:15

Total 6:45 =SUM(E49:E51)

Example 2 : Correct formatting

Start End Duration

7:00 18:30 11:30

8:00 17:00 9:00

7:30 17:45 10:15

Total 30:45 =SUM(E56:E58)

How To Apply Custom Formatting

The custom format for time use a pair of square brackets [hh] on either side of the hours indicators.

1. Click on the cell which needs the format.

2. Choose the Format menu.

3. Choose Cells.

4. Click the Number tag at the top right.

5. Choose Custom.

6. Click inside the Type: box.

7. Type [hh]:mm as the format.

8. Click OK to confirm.

Please use this method, if any one has any new idea, comments or queries in this regard, i would request you to post here in the comment section.

Split Forename and Surname

Finding the First Name
Full Name First Name
Alan Jones Alan =LEFT(C14,FIND(" ",C14,1))
Bob Smith Bob =LEFT(C15,FIND(" ",C15,1))
Carol Williams Carol =LEFT(C16,FIND(" ",C16,1))
Finding the Last Name

Full Name Last Name
Alan Jones Jones =RIGHT(C22,LEN(C22)-FIND(" ",C22))
Bob Smith Smith =RIGHT(C23,LEN(C23)-FIND(" ",C23))
Carol Williams Williams =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last name when a Middle name is present

The formula above cannot handle any more than two names.
If there is also a middle name, the last name formula will be incorrect.
To solve the problem you have to use a much longer calculation.

Full Name Last Name
Alan David Jones Jones
Bob John Smith Smith
Carol Susan Williams Williams
=RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name

Full Name Middle Name
Alan David Jones David
Bob John Smith John
Carol Susan Williams Susan
=LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

Please use this method, if any one has any new idea, comments or queries in this regard, i would request you to post here in the comment section.