***z-above-h1.shtml***
The Vlookup formula returns the information you requested from a vertical lookup. Below is an example of how it works:
Part Number Qty Price
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
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
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
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
There are many more special excel spreadsheet formulas, but these are usually the most widely used.
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:
- Vlookup
- IF formula
- Sum
- Average
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.