Excel
Spreadsheet Formulas
Excel
spreadsheet formulas allow
the user to manipulate large amounts of data to produce accurate
reports, while saving valuable time.
Microsoft excel is really a database and a report maker. Excel
spreadsheet formulas
allow us to draw information out of a database, or to conveniently
report information.
The most popular formula to extract information from a database is:
The most popular formulas to manipulate report information are:
The Vlookup
formula returns the information you requested from a
vertical lookup. Below is an example of how it works:
Part Number
Qty
Price

a 
b 
c 
1 
Part 123 
45 
14.50 
2 
Part
234 
72 
23.76 
3 
Part
456 
81 
111.23 
Above is our database of information. In
a separate cell below, we have part numbers, but want to find the price
of the
part without manually going through our database and picking out the
price:
Part
Price

f

g 
1 
Part
234 
=vlookup(f1,a1:c3,3,false) 
2 
Part
456 
=vlookup(f2,a1:c3,3,false) 
We
enter the formulas above to extract the price for each part from the
database above. The formula in g1 looks in the
database for
data that matches f1, then at column 3 and returns that
data (in
this case 23.76).
This is a very small database, but if the
database was many hundreds of part number records, you can see the
advantage of using this formula.
The IF
formula allows the user to perform what if calculations on
data and extract data that is dependent upon some condition:
Part #
Qty Price

a 
b 
c 
d 
1 
Part 123 
45 
14.50 
=if(c1>80,
c1,0) 
2 
Part
234 
72 
23.76 
=if(c2>80,
c2,0) 
3 
Part
456 
81 
111.23 
=if(c3>80,
c3,0) 
In
this example, we want to find parts above that have a price
greater than $80.00. The IF formula above in cell d1 looks at
the
price in cell c1 and determines if it is larger than 80, if it is, then
it returns the value in cell c1, if not, then it returns 0.
The
formulas in cells d1 and d2 will return 0, because the value in column
c is less than 80. The formula in d3 will return 111.23
because
this value is greater than 80
The sum
formula simply adds a list of numbers. In the
example below the formula in c4 is adding the price amounts in cells
c1, c2 and c3:
Part # Qty
Price

a 
b 
c 

1 
Part 123 
45 
14.50 

2 
Part
234 
72 
23.76 

3 
Part
456 
81 
111.23 

4 


=sum(c1:c3) 

The average
formula returns the average of a list of numbers. In the
example below, the formula in cell c4 returns the average of the
amounts in cells c1, c2 and c3.
Part # Qty
Price

a 
b 
c 

1 
Part 123 
45 
14.50 

2 
Part 234 
72 
23.76 

3 
Part
456 
81 
111.23 

4 


=average(c1:c3) 

There are many more special excel spreadsheet formulas, but these are
usually the most widely used.