Useful formulas and resources for becoming a spreadsheets master

This tutorial is a step-by-step guide which illustrates how to use some useful formulas in order to simplify the way you work with spreadsheet tools. I also added some interesting links to my previous posts about the same topic.

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

Why netbooks can kill MacBook Air

Longtime I was looking for an ultra portable alternative to my iMac to use mostly when I am not at home. My first intention was to buy a MacBook Air: great design, great quality... but also "great" price. Some week ago a friend of mine suggested to me to take a look at some Netbooks, in particular at the Compaq Mini, and I remained really impressed by this litte jewel. So, yesterday I decided to buy it.

First impressions: I can say the Compaq Mini is not a simple low-cost pc (399 EUR versus 1.699 EUR of a MacBook Air!) to use only to connect you to the internet. Compaq Mini is a powerful netbook you can use to do everything you do with your primary desktop PC or notebook. These are some of main characteristics:

- Processor 1.6GHz Atom N270
- 1GB RAM
- 60GB drive
- 1,024 x 600 display
- Bluetooth
- WiFi
- Integrated webcam
- Built-in microphone
- Windows XP

The first impression is to have a "micro" PC with all features of a normal notebook or desktop PC. You can use all your preferred applications such as Dreamweaver, Photoshop, Excel, Word, Outlook... without problems. The display is very clean and luminous, perfectly readable. This is a screenshot of my desktop:

Take a look at the full size picture

Let me do this little clarification: price difference between Compaq Mini and MacBook Air is obviously motivated by a lot of factors. But if you are just looking for a portable alternative to your primary computer... I think a MacBook Air is very too expensive, considering you can have "same" functionalities with a considerably lower price in a low-cost pc.

Applications: In these hours I'm stressing my new netbook working with some applications I often use in my work and to update my blog with new posts and tutorials. I installed old versions of Photoshop and Dreamweaver (Photoshop CS and Dreamweaver 8) I used some year ago to design websites, and Microsoft Office 2007 which I bought in order to use it on my iMac with a version of Windows XP I never installed :). System response is awesome. You can work with Dreamweaver 8 and Photoshop CS at the same time without problems.

I'm writing this post on Blogger using Internet Explorer 8 and at the same time using Photoshop to modify images and Media Player 11 to listen music from a web radio. I am not noticing any slowdown or delay.

Take a look at the full size picture

Microsoft Office 2007: It's a pleasure to use Office applications in this little netbook. Word, Excel and Outlook work perfectly. If you prefer to use open source alternatives try OpenOffice, but if you have Microsoft Office I suggest you to install and use it. It's without doubt the best office suite in circulation and is not so "heavy" to compromise system performances.

Take a look at the full size picture

Mobile Phone integration: integration with Mobile Phone is very simple. I'm using ActiveSync 4.5 to syncronize contacts, messages, activities and everything with my Samsung SGH-i780 mobile phone which I also use to connect me to internet (if there is not a Wi-Fi hotspot available), and browsing the Net with the same speed of an ADSL connection. In this way I can work to my blog, talk with my friends using Skype, take a look at my banking account and make a lot of other things in every place where I am.

After all, Compaq Mini is an ultra portable and convenient alternative with a killer price to carrying your primary notebook PC everywhere you go. If you want to know more info about this little jewel I suggest you to take a look at the HP official page.

How to create an awesome eBook Template using Word or Apple Pages

Today I received some requests about The Woork Handbook template I published yesterday. Some readers asked to me to distribute that template for their eBooks. So in this post I want to illustrate some guidelines to create an awesome eBook template, quickly and in few steps.

I prepared the template which you can download (for Apple Pages and Microsoft Word) and reuse it for your eBooks (I used Apple pages to write the eBook!).

Download Apple Pages template Download Microsoft Word template

eBook Format

You can print the eBook on A4 format page (2 pages for each facade). This is the screenshot:

Document structure

The document is divided in sections. Each section is a chapter. Use "section break" to separate each chapter in this way:

Adding a section break:

- Apple Pages: Insert > Section Break
- Microsoft Word: Insert > Break > Section break (next page)

Page header

This is a not necessary element but i like have a short description in the header of the my pages. It's a simple text on top of your page with above a line which separete the header from the rest of the page:

In general "page header" isn't placed on the first page of the chapter:

- Apple Pages: select layout button on the inspector and flag the option: First Page is different.

- Microsoft Word: double click on the page header and select different first page from the header/footer toolbar.

Chapter structure using document styles

When you work with a text document (using Word, Apple Pages, OpenOffice...), a very good practice is to define your custom styles for the elements of your pages. In this way, every time you decide to modify the look of an element (for example "chapter title"), it will be update automatically in all elements of your document which use that style. For example take a look at the following picture which represents a generic chapter structure:

We have the following elements with related styles:

Chapter title -> Heading 1
Article title -> Heading 2
Paragraph header -> Sub header paragraph
Normal text -> Body
Code box -> Code
Footnotes -> Notes
Article footer -> Small header + small body

You can define your custom style in this way:

- Apple Pages: View > Show Style Draver and then selecting a style and modifying style properties, or creating a new style.
- Microsoft Word: Format > Style and then selecting a style and modifying style properties, or creating a new style.

Note: I used Qlassik font for the title of chapters and articles. You can download it here. In this version of template I used Arial in case you don't have Qlassik font.

Table of contents

Adding a TOC it's very simple:

- Apple Pages: Insert > Table of content.
- Microsoft Word: Insert > Document element > Table of content

Your TOC will be updated automatically every time you add a new content in your document but (this is really important!) you have to use style elements created for the chapter title and article title (Heading 1 and Heading 2).

It's all! I hope this template can help you to prepare quickly your eBook.

Every suggestion is appreciated :)

Note: I used Apple Pages to create my eBook, and I simply converted Pages template for Word. I experienced there are some litte problems with formatting which you convert a document in Word from Apple Pages... tell me if you have them!

Sync Windows Mobile devices with your Mac

It was long time that I was looking for a good tool for synchronizing my Samsung device (Windows Mobile 6 based) with my iMac. Finally I found SyncMate, a great application which helps you to sync your Mac with your Windows Mobile devices for free.

SyncMate is a free useful tool for synchronizing Windows Mobile devices with you Mac. Free Edition provides basic sync features for applications like Address Book, iCal, and the possibility to read SMS directly from your Mac. This is an screenshot of this application:

You can also buy an Expert Version of SyncMate with advanced sync features for iTunes, Internet Explorer, iPhoto, Mail, Notes...

I suggest you to try it. It's absoultely useful.

Download SyncMate

Google Spreadsheets Tips: Gantt Chart (Microsoft Project-like) using Widget

Google Spreadsheets is a powerful web application to do everything you do using Excel. A nice way to use it is to manage a project plan with a gantt chart (microsoft project-like) and share it on-line with your team.This is a free and versatile solution to do that. This tutorial illustrates how to use Google Spreadsheets Viewpath Gadget to implement it.

Take a look at this spreadsheet


Step 1: Project Plan structure
First step is defiing your project plan structure. I suggest you to use this basic structure:

Column A: Task ID (WBS) (an unique ID which identifyes each task with a progressive number).
Column B: Task description (a short description of the activity).
Column C: Percentage of completion (0%-100%).
Column D: Predecessor (finish-start relationships between tasks).
Column E: Start date (task start date).
Column F: Finis date (task finish date).

The result is something like this:

All previous fields are required for viewpath gadget but, if you want, you can add other columns with other infos (for example the name of a resource assigned to each task, task duration in terms of day, ecc...). When your project plan structure is completed you are ready to add Gantt Chart using Google Spreadsheets Viewpath Gadget.

Step 2: Add Gantt Gadget
Click on Chart icon and select Gadget option.

In the Gadget window find and select Gantt Chart gadget (by Viewpath) and click on Add to spreadsheet button:

In the option window assign all required parameters to the correct column of your project plan structure (for example Start dates in this example are in the column E):

After assigned all columns, click on apply button and see the result. Now you can save your project plan , publish and share it with all member of your team.


Related Content
See also the following link about this topic:

• 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

Project Management: a project plan with Excel (template)

In the previous lesson I explained some general rules about how to organize a project plan structure and implement it with an excel worksheet to manage your projects. I implemented the Excel worksheet that contains two sheets: WBS and HR. WBS is the work breakdown structure of our project and HR contains data about the resources assigned to each task.

Download Project Plan Template (excel)


WBS Sheet: Activity-Task status
Columns A:F contains general information about Activities and Tasks:

Before assigning a resource to a task, you have to add all information about the task's owner into the sheet HR.

HR: Define Project Resources
In this sheet you can add resources and assign them to your project's tasks:


Task Assigned counts the number of task assigned to each resources. It is calculated using COUNTIF() Excel function. This is an example of use of this function:

=COUNTIF(WBS.D:D;B5)

... in other words: "update the counter only if, in the interval of reference, WBS.D:D (column D of the WBS worksheet), find a value qual to the cell's value B5 in the sheet HR".


WBS Sheet: Project Dates
Columns G:I contain informations about activity start-date/finish-date/total days. For each task, you have only add a value for Start Date and Finish Date. Days will be calculated automatically such as difference between columns H - columns G.


WBS Sheet: Re-planned Date
Columns K:Q, contains info about re-planned dates and the project delay.
If you want to re-plan start-date or finish-date of an activity, you have check the column K with an "X" into the cell relative to the task you want to re-plan.


So, you set SD Delay (start day delay - in terms of days) and/or FD Delay (finish day delay - in terms of days) - Column L:M. Re-Start Date, Re Finish Date, Re-Days (replanned days) and Project Delay will be calculated from the sheet.

WBS Sheet: Costs Control
Columns S:X contains informations about cost management. You can specify hours and hour cost for each resource assiged to a task.

You can also add Fixed Cost and an Adjust (flat) to simulate cost's trend.

Download Project Plan Template (excel)

More updates about this topic coming soon :)

Innovation Inbox #2

Great product design brings together the perfect marriage of form and function.

In this issue, we highlight cutting-edge products and technologies, for an inside look at how and why they were developed.

Enjoy!

Alltel's new Celltop

Company: InformationWeek

What is it: Allow customers a new way to organize content on their phone

Bona Fide: Puts interactive, changeable windows or "cells" on your phone's display. The idea is to let you scan through such information without having to press several buttons.

RFID Audiobar

Company: Mogens Jacobsen

What is it: A physical bar-like social environment that enable visitors to interact with sounds via RFID tagged bottles

Bona Fide: Each bottle is labeled with different keywords, by moving the bottles around, visitors can play sounds that match the keywords

QR Codes…The Next Big Thing From Japan?

Company: Various Japanese Companies

What is it: A new type of bar code that can hold more information than in the past, like alphanumeric characters and even Japanese text

Bona Fide: Can be used to direct mobile phone users to special websites to download brand ads, ringtones, character logos, viral videos, branded flash games and more.

Billboards with Face Recognition

Company: Microsoft

What is it: a prototype advertising system that uses a small video camera and facial-recognition so ftware to try to determine a viewer's gender and select an appropriate ad to display

Bona Fide: May be the future of the advertising industry, allowing agencies to provide targeted messaging in a real world environment such as Amazon and Google do with search

The Olive Martini Glass

Company: Beth Fuller

What is it: Redesign of the martini glass

Bona Fide: Designed to be held comfortably in the palm of the hand, while there is a hole in the lip of the glass to accommodate olives on a toothpick