Until now we have used only simple functions such as SUM and AVERAGE, MIN and MAX.

Excel, however, is capable of complex functions.

A complex function is a function that can not be duplicated by mathematical formulas. For example, IF and VLOOKUP.

The logical IF function is one of Excels most valuable functions.

This function has the following form:

IF(logical test, value true, value if false)

The function returns a value if true if logical test is TRUE and returns value if false if logical test is FALSE.

Up to seven IF functions can be nested to construct more elaborate tests.

For example, let’s suppose you have a list of grades and wish to equate percentages with a number or letter grade (ie 90.5=5).

We can set up a complex function to do that.

Open the grade basic file.

Select cell C2 and put and = in the formula bar and click the Fx button.

Choose the Logical Function Category and the IF Function.

Create the following expression by using the boxes: IF(B2>=90,"5","")

We can use Complex Operators including AND, OR and NOT to determine the grades of those who got grades lower than 90.

In this case we will use the AND operator.

For the 4s we would write =IF)AND(B2<90,B2>=80),"4","")

You try it for the remaining grades.

The last step is to combine all of the formulas into a single formula that will calculate the grades for everyone.

We would enter the following formula:

=IF(B2>=90,"5",IF(B2>=80,"4",If(b2>=70,"3",IF(B2>=60,"2","1"))))

This has been just one simple example.

With a little imagination you can come up with more uses for Complex Functions.