TRIM()function in Excel

This formula removes all spaces from text except normal single spaces between words.

Therefore, this formula only removes the redundant leading and trailing spaces of characters in a cell.

When a user imports a set of data from another application, irregular spaces always exist after the last character of cells. Therefore this formula is necessary to remove those spaces. This is of utmost importance, because a string without space such as “IT” and a string with irregular spaces at the end such as “IT “ will not be treated as identical match in spreadsheet. Thus, if you do a lookup or countif function on “IT”, the “IT “ will be neglected, lead to inaccurate outcome.

TRIM(text)

=TRIM(" DESKTOP ")

The result will be “DESKTOP”.

The text can be a reference to a cell.

keywords: Text, Data

Free Download: CFD Simulation Spreadsheet!

This spreadsheet allows you to enter the price of shares and outlay you ready to put in, and then work out your potential profit/loss depending if you go “short” or “long”.

Please note yellow shaded cells are for data entry.

Download here. Enjoy! Don’t forget to leave your questions on the comment section…

Keyword: Download, Contracts for Difference, CFD

Using IF in Excel

IF function is used to conduct an evaluation of whether a value or formula is TRUE or FALSE.

The function allows user to specify the outcome of TRUE and FALSE.

For example, if the value of reference cell is TRUE, execute formula A, if the value of reference cell is FALSE, execute formula B.

=IF(logical_test, outcome if TRUE, outcome if FALSE)

logical_test can be any value or argument which will be evaluated as TRUE or FALSE. This is very much like a condition, if condition is met then TRUE else FALSE.

Therefore, in the above formula, if logical_test is TRUE, execute “outcome if TRUE”, else execute “outcome if FALSE”.

An example, assuming cell A2 is “110” and cell A3 is “104”

=IF(A2>100,”Greater than 100”,”Less than 100”)

The result of the above formula will be “Greater than 100”

=IF(A3>110,”Greater than 110”,”Less than 110”)

The result of the above formula will be “Less than 110”.

Keyword: Logical

Logical Function AND() & OR() in Excel

The AND formula returns TRUE if all arguments are TRUE and returns FALSE if one or more arguments are FALSE. The OR formula returns TRUE if at least one argument is TRUE and only returns FALSE if all arguments are FALSE.

AND(logical1,logical2, ...)

OR(logical1,logical2, ...)

EXAMPLE:

Assuming cell A2 has a value of 110 and cell A3 has a value of 104, the following formulas and their respective answers:

=AND(A2<100,a3>100) Ans: TRUE

=AND(A2>110,A3>100) Ans: FALSE

=OR(A2<100,a3>100) Ans: TRUE

=OR(A2>110,A3>100) Ans: TRUE

=IF(OR(A2<110,a3>100),”Big”,”Small”) Ans: Big

Please note > is greater than, >= is greater than or equal to

Keyword: Logical

Check the content of cell using ISBLANK()

ISBLANK(value)

This formula checks if a cell is blank, if yes returns TRUE, else FALSE. This formula is particularly useful to stop another formula to execute. For example, if a cell is empty, leave another cell empty, else execute a summation function. Such as:

=IF(ISBLANK(A2),””,A1+A2)

Other similar formulas are:

ISNA(value) check if a cell is #N/A

Application: =IF(ISNA(A2),””,A1+A2)

ISERR(value) check if a cell is error except #N/A

Application: =IF(ISERR(A2),””,A1+A2)

ISERROR(value) check if a cell is any types of error including #N/A

Application: =IF(ISERROR(A2),””,A1+A2)

ISNUMBER(value) check if a cell is numerical

Application: =IF(ISNUMBER(A2),A1+A2,””)

ISTEXT(value) check if a cell is text

Application: =IF(ISTEXT(A2),A2,A1+A2)

Using COUNTBLANK to count number of blank cells

This formula counts the number of empty cells in a specified range of cells. It’s a very straight-forward formula. This formula is one of the many Information formulas available in Excel.

Information formulas serve the purpose of returning all sorts of information regarding a table or a set of cells.

COUNTBLANK(range)

RANGE is the range of cells to be counted. It could involve multiple rows and columns. Some examples are A2:C6 or A2:A50.

For instance, if range of cells A2:C6 has 2 blank cells. The outcome of COUNTBLANK(A2:C6) is 2.

Using TODAY() to automatically update your spreadsheet dates

This is a relatively straight forward formula.

TODAY()

By putting this formula in a cell, the cell will display the current date. Therefore, It updates automatically everyday.

Some useful application:

Number of days between today and 25/05/06:

Assuming cell B1 =TODAY()

=DAYS360(“25/05/06,B1)

The above formula works out the number of days between current date and 25/05/06. Therefore, if today is 29/05/06, the result will be 4.

Keywords: Time, Date

Date & Time Function: DAYS360()

This formula returns the number of days between two specified dates assuming 360-day a year (twelve 30-day months).

DAYS360(start_date,end_date,method)

Method is a way to specify whether to use US or European system. When omitted, or FALSE, the formula will display in US system. TRUE will display European system.

For example: DAYS360("05/05/06","01/12/06")
Outcome = 206

This means there are 206 days between the above mentioned two dates. Please note the "Start Date" and "End Date" could be a cell reference with value of dates instead of putting in dates into the formula itself.

Keywords: Date, Time