Embedded Files
General Excel functions:
Text functions
=Upper(text)
This function is used with text entries. It will change the case of text in a cell to uppercase letters.
You could insert a new column to create uniform codes and copy and paste these cells as VALUES. Then you could delete the original column.
Example:
Note: The function has no effect on numbers
=Lower(text)
This function is used with text entries. It will change the case of text entries to lowercase.
Example:
=Proper(text)
This function changes entries to Title Case.
=Concatenate(text1,text2,...)
The concatenate function joins several text strings into one.
Result:
Peter Smith
NOTE: You can also use =A1 & “ “ & B1
=LEN(text)
=len(“Peter Smith”) will return 11
=Find(find_text,within_text,[start_num])
NOTE: The start number can be left out, if you want to find the text from the start of the phrase
=LEN(text)
=len(“Peter Smith”) will return 11
=Find(find_text,within_text,[start_num])
NOTE: The start number can be left out, if you want to find the text from the start of the phrase
=Trim(text)
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
It also removes extra spaces at the start and the end of a phrase.
The underscored character represents a space below:
NOTE: This is a very handy function to use in conjunction with a vlookup function.
=Substitute(text, old_text, new_text)
This is a very handy function to use when text is imported from other applications, i.e. MS Access where LINE BREAKS were used and not ENTER.
Use the SUBSTITUTE function to replace a LINE BREAK with a character, i.e. *. This will enable you to split the information into separate columns.
In Excel the shortcut ALT + ENTER creates a line break. The ASCII character for this is Alt 0010. i.e. Substitute(C2,char(10),”*”)
Example 2:
To replace a DESCRIPTION with a CODE in a spreadsheet: Substitute(C2,”Gauteng”,”GAU”)
Number functions
=ABS(num)
This function only looks at the number and not whether it is positive or negative number.
The ABS() function will change a value to a positive number. For example:
=ABS(20) will return the value 20
=ABS(-20) will also return the value 20
NOTE: The ABS() function is useful for showing the difference between two values regardless of whether the difference is positive or negative.
=ROUND(num)
The ROUND() function will round a value to the nearest n decimal places, where n can be specified by the user.
The syntax for this function is =ROUND(value, number of decimal places).
Example:
=INT(num)
The INT() function returns the integer part of a value.
=INT(12.99999) will return 12
=INT(-12.99999) will return -12
NOTE: Both the ROUND and the INT functions are useful when working with calculations where the decimals are irrelevant or insignificant.
=TRUNC(num)
The =TRUNC() function truncates the value to the number of decimal places specified in the formula. The =TRUNC() function will NOT Round values.
Example:
=TRUNC(134.788,2) will result in 134.78
=RANDBETWEEN()
Examples:
To generate a random number greater than or equal to 50, but less than 100:
=RANDBETWEEN(50,100) - this could result in i.e. 78
NOTE: The returned value will change if the F9(update key) is used, as it is uses a figure from the internal clock of the computer. You would have to copy and paste as
The INT function is also handy when you need to get the fraction part of a number, example
=102.67 – INT(102.26) will result in 0.67
=Value(text)
When data is copied from other applications, some of the values that appear to be numbers could be text values. The value function will convert a text value to a number.
Example:
=value(00001) result: 1
Using a NESTED IF statement
An example of a nested IF:
=IF(A2<10000,"",IF(B2 = "Y",A2 - C2,IF(B2 = "N",A2 + D2)))
Creating a nested IF function, using the Formula Wizard:
Click the Insert function (fx) button on the formula bar.
Select the Logical function category
Select the IF function and click OK.
Type the first condition.
Click in the Value if true box and enter the true part of the statement.
If you need a nested IF click the IF again as indicated below and type a second condition otherwise type an expression.
The Wizard shows that this is the second IF level.
You can either select another nested IF, or you can complete the If-False section.
NOTE: Each IF statement can contain up to 7 nested IF’s.
IF Statement: Using AND, OR or NOT:
SUMIF(range,criteria,[sum_range])
The SUMIF adds figures in a range, based on criteria, e.g.
=sumif(A1:A10,”Pencil”,B1:B10) This formula will look in the range A1:A10 for the word Pencil and add only the corresponding values in column B.
NOTE: Inverted commas must be used to specify the criteria, unless a cell reference is used.
The sumif function can also used on one range of numbers, e.g. =sumif(B1:B10,”>=50”)
COUNTIF
The countif function will count the number of entries in a column based on criteria, e.g.
=countif(A1:A10,”Pencil”) will count how many entries were found with the word pencil.
Date Functions
In Microsoft Excel, dates and times are treated as numbers. Times or dates can display invarious ways, depending on the format that is applied to the cell.
When you type a date or time that Excel recognizes, the cell's format changes from the General number format to a built-in date or time format. The format is usually based on the settings in the Control Panel of the computer.
If Excel does not recognize the date or time format, the date or time is entered as text. The date/time values can then not be used in formulae or formatted in a specific way.
NOTE: To type the date and time in the same cell separate the date and time with a space.
NOTE: To type a time based on the 12-hour clock, type the time, then a space and then AM or PM (or A or P).
Examples:
14:00
8:00 AM
3:00 PM
3:00 (this will automatically be seen as 3:00 AM)
21/10/99 10:00 PM
NOTE: Regardless of the format used to display a date or time, Excel stores all dates as serial numbers and stores all times as decimal fractions. To display a date as a serial number or display a time as a fraction, change the cell number format to General.
Dates and times can be added, subtracted, and included in other calculations. To use a date or time in a formula, enter the date or time as text enclosed in quotation marks or alternatively refer to the cells in a formula.
For example, this formula would display a difference of 5:
="25/12/99" - "20/12/99"
NOTE: In Excel, the start date of all dates is January 1,1900
=NOW()
Returns today's date and the current time.
The format of the date and time depends on the way the cell is formatted.
=TODAY()
Returns today's date. The cell value will automatically pick up the current date when you open the workbook.
=MONTH()
Returns only the month as an Integer value, e.g.:
=DAY()
Returns only the day as an Integer value, e.g.:
=YEAR()
Returns only the Year as an Integer value, e.g.:
=DATE(year,month,day)
Returns the serial number of a particular date.
Examples:
=date(1999,08,17), will return a date in the format that was applied, e.g. 17 Aug 1999
=date(year(B1),month(B2),day (B3)), will compile a date from different cells, if those cells contain dates.
OR
TIP: To display a DATE as a month only, use a Custom Format on the date and enter MMMM for APRIL or MMM for APR
Example Format(B1,”MMMM yyyy”) will result in the August XXXX(current year)
Page updated
Google Sites
Report abuse