Basics of Excel functions and relations.

Through this post, I would like to introduce the Built in Functions of Microsoft Excel to a beginner. Let me put some excel functions that could be of use to those who what to make a start.

Before touching upon some of the functions, some introductions. The first thing that I would recommend for a beginner is to browse through the list of functions and its uses. At this stage, forget how it to be used, but keep an eye one what all you can do using functions. For that, go to INSERT -> FUNCTIONS. You will see the following dialog. Here you can either select ALL, or a specific category like TEXT. Now select a function and at the bottom, you will see what it will do. To find more detailed explanation, click HELP ON THIS FUNCTION.



Note: Click the cell with Function(s) and press F2 and you will be able to see the references in different colors.




Some simple functions:


1: Sum.
To find sum of a few numbers.
Eg: SUM(A1, D5, G1:G15)


2: Min.
Minimum of a list of numbers


3. Max.
Maximum of a list of numbers


Some more functions:


4: Concatenate:
Joins several strings into one single string.
Here each string can be a reference to another cell (like A5) or a manual one (like "Hello"). Note that the manual entries should be encapsulated in double-quotes.

NOTE: there is a simpler way to achieve this. just type & in between each string entry. So..
=Concatenate(A5," is the son of ", A10) is equivalent to
=A5&" is the son of "&A10


5: Left, Mid and Right
Left function allows you to get a specified number of characters from the left side.
Right function allows you to get a specified number of characters from the right side.
Mid function allows you to get a specified number of characters from a specified starting position.

Eg:
Assume A5 => "I am not a fool"
Assume A6 => "Joe"

Left(A5, 5) => "I am "
Left(A5, 5)&": "&A6 => "I am : Joe"

This following is an exercise for you.
Left(A5, 5)&Right(A5,6)


6: Substitute
Replaces an old text with a new one.

Assume you have to change a part number format.
What have is 75423.5638.6453 and what you want is 75423-5638-6453. (Replace all .s with -s)
=Substitute(75423.5638.6453, ".", "-") => 75423-5638-6453

Also,
=Substitute(75423.5638.6453, ".", "") => 7542356386453


7: Replace
Replaces a text with another one.
The difference from Substitute is that here you are specifying the positions of the text to be replaced.


8: Upper, Lower and Proper.
Changes the case of the string to the respective ones.

Upper(AbcD) => "ABCD"


Some Special Functions:


8: VLookUp
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.



VLookUp(Value In Column 1, Range of the Table, The Column number of the data to be taken)
based the feed value, the row will be selected and the data from the correct column is extracted.

VLookUp(0.675, A1:C10, 3) => 250

Also..

A15 => 0.946
A16 => 2

VLookUp(A15, A1:C10, A16) => 2.17
is the viscosity for a density of 0.946.

NOTE: Alternately, you can use HLookUp to fix the column and get the data from row.



9: IF
Gets a value based on whether a condition is true or false.

If(Condition, command if condition is true, command if condition is false).

A1 => 10
A2 => First
A3 => Second

If(A1>5, A2, A3) => First
If(A1<=5, A2, A3) => Second


10: SumIf
Adds the cells based on a given criteria.



SumIf(Criteria Range, Criteria, Sum Range)
if Sum Range is not specified, Criteria Range is taken as Sum Range.

SumIf((A2:A5, ">160000", B2:B5) => 63,000
Add cells in column B if corresponding cell in Column A >160000.
SumIf((A2:A5, ">160000") => 900,000
Add cells in column A if corresponding cell in Column A >160000.

NOTE: A similar function COUNTIF counts based on condition.


11: ISBlank
Checks if a cell is blank or not.

A1 = ""
A2 = 10

IsBlank(A1) = TRUE
IsBlank(A2) = FALSE

If( IsBlank(A1), "", 100 / A1) => ""
If( IsBlank(A2), "", 100 / A2) => 10

What if you don't do that check!!!
100/A1 => #DIV/0!
100/A2 => 10.

See the first one returns an error, which you normally don't want to happen. So the check helps to prevent that.



Enough of the functions. You can browse the remaining yourself.
Couple of other related points also.





Relative motion of references.

This is a very interesting aspect of how you can use the functions effectively. And interestingly, less commonly used.
The normal references that you take move with the copying. I will explain.

let B2 is A1 + A2 + B1
If you copy B2 to B3, the relation will automatically be changed as A2+A3+B2.
If you copy B2 to C2, the relation will automatically be changed as B1+B2+C1.
This is the normal way it functions. If copy one cell to the right, all references move one cell to the right. If you copy 2 cells to the bottom, all references move 2 cells to the bottom as well.

You can control this movement. In the sense, you can restrict some or all the movement by putting a $symbol.

a reference that is A1 is free to move in both directions.
a reference that is $A$1 is prevented from moving in both directions.
a reference that is $A1 is allowed to move up or down but not right or left.
a reference that is A$1 is allowed to move right or left but not up or down.

I will take the original example again.
let B2 is $A$1 + $A2 + B$1

copy that to C2 and you will get this.
$A$1 + $A2 + C$1

copy that to B3 and you will get this.
$A$1 + $A3 + B$1

If Row-1 and Column-A are the heading elements, this relation will give you the sum of the heading cells + A1.

Note:
Pressing F4 repeatedly will cycle through each type.




I hope that this will allow you to jump start your excel learning.
Excel is a vast ocean. But don't let that bother you. Keep it simple, Make one step at a time and don't forget to make one new step each day. After sometime, you will be amused at what you are able to do.

Let me know if you have any comments / Qns.