In this tutorial I used Zoho Spreadsheet, a free and powerful on-line spreadsheets tool, but you can reuse all concepts in the most common spreadsheet tools (Google Spreadsheets, EditGrid, Microsoft Excel, OpenOffice Spreadsheets, etc.)
You can download this tutorial for Microsoft Excel and OpenOffice Spreadsheets or take a look at the on-line spreadsheet.
1. vlookup()
Vertical LookUp - vlookup() - is a powerful formula to check if a specific value is contained in the first column of an array. The function then returns the value to the same line of a specific array column named by index. For example take a look at the following image.
Add this formula into the cell F2:
=VLOOKUP(D2;A1:B4;2;false)
"Translated" is: find the value contained in the cell D2 looking in the interval A1:B4 and return the value to the same line in the column with index 2 (in this case, A:index =1, B:index=2 so the formula return the value in the column B).
The formula returns #N/A if the name in input doesn't exist in the table:
Take a mind: You can use exactly the same formula with Microsoft Excel. But if you use Google Spreadsheets you have to change the syntax of all formulas in this tutorial changing the char which separates formula parameters (;) with (,). For example using Google Spreadsheets you may to use this syntax:
=VLOOKUP(D2,A1:B4,2,false)
2. type()
Type() returns the type of value. For example, if an error occurs, the function returns a logical or numerical value. In the VLOOKUP example, if you don't like to see #N/A you can use type() to create a custom message if the value doesn't exist in the table:
Add this formula in the cell F4:
=TYPE(F2)
...and the result in the cell F4 is 16 if F2 is equal to #N/A. Then in the cell F6 add this formula:
=IF(F4=16; "Name not found";"")
You can also embed into a single formula directly into the cell F2:
=IF(TYPE(VLOOKUP(D2;A1:B4;2;false))=16; "Name not found"; VLOOKUP(D2;A1:B4;2;false))
3. hlookup()
Horizontal look up - hlookup() - is the horizontal version of VLOOKUP. The formula searches for a value and reference to the cells below the selected area. The function returns then the value in a row of the array, named in the Index, in the same column. For example take a look at the following table:
Add the following formula in the cell B6:
=HLOOKUP(A6;B2:D3;2;false)
If the name you are looking for in the cell A6 doesn't exist in the table the result will be #N/A.
4. Subtotal()
Subtotal() calculates subtotals in a specific range of cells. If a range already contains subtotals, these are not used for further calculations. Take a look at this table:
Add this formula into the cell B2:
=SUBTOTAL(9; B3:B4)
The first parameter (9) is a number that stands for the function SUM. You can change it with the following values, depending on the operation you have to do:
- 1 AVERAGE
- 2 COUNT
- 3 COUNTA
- 4 MAX
- 5 MIN
- 6 PRODUCT
- 7 STDEV
- 8 STDEVP
- 9 SUM
- 10 VAR
- 11 VARP
The result will be 12+23 = 35
Then, copy and paste the previous formula into the cells B5 and B8. Now to obtain the total of all subtotals add this formula in the cell B11:
=SUBTOTAL(9; B2:B10)
5. Choose()
Choose() returns a value from a list of up to 30 values. The value to return is identified with an index (an integer from 1 to 3o). For example, in this example I want to choose the value contained in the range A2:A9 in the position 4 (index=4):
Copy and paste this formula in the cell E2 (the index of the value you have to choose is contained in the cell C2):
=CHOOSE(C2; A2;A3;A4;A5;A6;A6;A8;A9)
The result is "Micheal".
Take also a look at the following links to find other resources on Woork about the topic "spreadsheets":
Google Spreadsheets Tips: Add custom charts
Google Spreadsheets Tips: Gantt Chart (Microsoft Project-like) using Widget
Use Google Spreadsheets to get financial informations about companies
Google Spreadsheets Tips: invert word position using formulas
Google Spreadsheets: formulas tutorial
Gantt Chart with EditGrid online spreadsheets
Implement a Project Plan and manage activities with Google Spreadsheets
Project Management: a project plan with Excel (template)
Gantt Chart using Google Spreadsheets and conditional formatting
Project Management: Excel Gantt Chart Template
No comments:
Post a Comment