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

Google Spreadsheets Tips: Add custom charts

In the past months I dedicated several post about Google Spreadsheets about how to use basic formulas, how to design a project plan structure and how to implement a gantt charts using Widgets. Yesterday my friend Ivan ask to me to dedicate a post about how to add custom charts and, in particular, dynamic charts which update themselves when an user update a table with input data.

This tutorial explains how to add custom charts on Google Spreadsheets using Widgets. If you are an Excel user you'll find this very simple!

Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account.


1. From table to chart
Image to have this table with the following data about a list of products:

For each product I want to display, in the same chart, price, cost and the difference between price and cost. Ho can I do? A good way to do it is using an Area Chart.

2. Select Chart
To add an Area Chart on Google Spreadsheets click on Insert gadget and from the Gadget window select Charts > Area Chart. The chart will be added on you active sheet. Now you have to set all required parameters (Range is necessary!):

If you take a look at the table at the step 1, cost and price values are in the column D and E, so the range is:

Sheet1!D3:E10

...where Sheet1 is the name of the sheet where is the table. Now click on Apply and Close to add your chart on the current sheet. Simple, no?


3. Dynamic Charts
Now, image to have the following table which simulate the selling trend of a set of products (products details at step 1). When an user change quantity (column K) total values in the table will be updated with the new data. Price is taken from the table at the step 1 (column E), using Vertical Lookup function (Vlookup) using the ID Product (column I) as search criteria.

Total (column J) is calculated as Quantity * Price. I want to add a chart which display how revenues are allocate (for each product) on the total revenue. You can do it easy using a Pie Chart. Click on Insert gadget link and from the Gadget window select Charts > Pie Chart. The chart will be added on you active sheet. Now you have to set all required parameters (Range is necessary!):

If you take a look at the simulation table, total values are in the column J and products names in the column I, so the range is:

Sheet1!I4:J6

...where Sheet1 is the name of the sheet where is the table. Now click on Apply and Close to add your chart on the current sheet. At this point every times you change quantity values (column K) your chart will be updated with the new data.

Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account.


Related content
Google Spreadsheets Tips: Gantt Chart (Microsoft Project-like) using Widget
Use Google Spreadsheets to get financial informations about companies
Gantt Chart using Google Spreadsheets and conditional formatting
Google Spreadsheets: formulas tutorial

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

Google Spreadsheets Tips: Invert word position using formulas

Are you a Google Spreadsheets users? Take a look at this tips to invert words into a string.

Ok guys, finally I have a litte bit of time to add a new post on my blog. Today I want to return to talk about Google Spreadsheets, illustrating simple tips very useful to invert automatically (using some basic formula) the order of words in a cell.

You can also use all formulas in this example in Excel.

Take a look at the spreadsheet here

Download Excel file

Step 1 - Input data

For example, image you have a column with several names (name + surname):

Jack Bauer
Christian Troy
Gregory House
Micheal Scofield
Lincoln Burrows
...

...and for some reason you need to invert the order in surname + name. You can do it manually... but in case of more then ten names could spend a lot of time to do it. A good solution is using some formulas


Step 2 - Find a criteria to separate words
What is it the criteria you can use to split single words? In this example is the space (" ") between name and surname. You have only to find the "position" of the space (" ") to have a reference to split the content in the cell. You can do it using find() formula in this way:

=find(" ";B5;1)

This forumlas return the index (position) of the space (" "), contained into the text in the cell B5, starting to the position "1".

...for the cell B5 (Jack Bauer) the space is at position 5 (index = 5).

Step 3 - Extract words
Now, in a new column you can extract the surname and in another column the name. To extract the surname you can use right() formula combined with len() formula:

=right(B5;len(B5)-C5)

...where len() formula return the lenght (number of chars) of the text contained in the cell B5.

To extract the name you can use left() forumla:

=left(B5;C5)

Step 4 - Concatenate extracted words in a new order
Finally, you can concatenate words you extracted in the order "surname and name", using concatenate() forumla:

=concatenate(D5," ",E5)

...concatenate surname (D5), a space (" ") and name (E5).

It's all!

Take a mind, you can also use the same formulas in Excel but remember only to separate formula parameters using ";" instead of ",".

Related Content

See also:

• 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: Excel Gantt Chart Template

In the past days I received some requests from my readers to deploy a simple Excel template to manage a project with a Gantt chart (in the right side of the sheet).

So I prepared this spreadsheet you can download and use in your job. The sheet contains only the following fields: WBS, Task Owner, Status, Start Date, Finish Date, Percentage of completion.



Download Excel Template

WBS structure is organized in two levels: activities and tasks. Each activity contains one or more task.
Each task has an owner and a status (I suggested:in progress, delay, completed, suspended, but you can change it like you prefer) and a percentage of completion.

Add a new task
To add a new task, copy a row with a task and paste it where you want. Remeber to update WBS number for the new task.

Add a new Activity
To add a new activity, copy a row with an activity and paste it where you want.

I hope you can find it useful. For infos or request please contact me :)


Related Post
Project Management: organize a project plan
Project Management: a project plan with Excel (template)
Gantt Chart using Google Spreadsheets and conditional formatting
Implement a Project Plan and manage activities with Google Spreadsheets

Google Spreadsheets (Apps) now supports Safari

It's true! I tried some seconds ago... With my big surprise, finally Google Apps supports Safari.

So, all Mac users (Safari lovers) can edit Google Spreadsheets files directly with their own preferred browser, without switch every time to Firefox.

Related Posts

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

Use Google Spreadsheets to get financial informations about companies

In the past days I wrote some posts about Google Spreadsheets and how to use some common formulas like countIf(), vlookup(), sumIf().

This post explains how to use GoogleFinancial() and GoogleLookUp formulas to get financial informations about a list of companies in Google Spreadsheets.

Take a look at this spreadsheet

Spreadsheet structure
My spreadhseet's structure is very simple. It contains a column (column A) with company's name and other columns (B:L) with the financial data I want to get - using GoogleFinancial() formula - for each company (price open, current price, highest price of the day, erning-per -share...).



To get these informations, take a mind GoogleFinancial() formula take two parameter: symbol (the company code used to identify the company in the market) and an attribute (kind of data you want to display). So, first step, we have to get the symbol (company code) using another useful formula, GoogleLookUp().

Use GoogleLookUp() formula to retrieve company code
To find the Company Code (column B) I used GoogleLookUp() formula, a very useful function which use the web to retrieve information about a serie of questions you have (for example about countries and territories, planets, companies, ecc...).
In my example I want to retrieve the company code for each company in the column A. I used this formula:

=GoogleLookup(A6, "ticker")

...where A6 is the reference to the cell which contains the company name, and "ticker" is the attribute you have to use with GoogleLookUp() formula to get the company name.

In my example the previous formula is like:

=GoogleLookup("Apple Inc", "ticker")

and the result is:

AAPL

You can find a detailed guide about GoogleLookUp() formula, with an explanation about all attributes you can use, here.

Get Financial Data using GoogleFinancial() formula
So, for each company, we have the company code (column B) and now we can use GoogleFinancial() formula to get financial informations about our companies. In my example, column C display the currency in which the stock is traded, simply using this formula:

=GoogleFinance($B6, "currency")

...and the result is:

USD

In my spreadsheet I used the following formulas:

Currency: =GoogleFinance($B6, "currency")
Price Open: =GoogleFinance($B6, "priceopen")
Price: =GoogleFinance($B6, "price")
Price-to-Earnings: =GoogleFinance($B6, "pe")
Erning-per-share: =GoogleFinance($B6, "eps")
Beta: =GoogleFinance($B6, "beta")
Volume AVG =GoogleFinance($B6, "volumeavg")
High (highest price the stock traded at for the current day) =GoogleFinance($B6, "Hig")
Low (lowesest price the stock traded at for the current day) =GoogleFinance($B6, "low")

Take a look at this spreadsheet

You can find a full guide aboutGoogleFinancial() formula here.

Gantt Chart using Google Spreadsheets and conditional formatting

Today's lesson explains how to implement a dynamic Gantt Chart using Google Spreadsheets and conditional formatting (change with rules option).

Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account

Implement the gantt chart
For explain the topic of this post I prepared a spreadsheet whit a simple work breakdown structure, with only four columns (WBS, activity, start date, finish date).

The question is: how do you do to change a date and update automatically the gantt chart?

You can think to solve the problem in this way, using a simple IF statement: for each activity, if the date's value, contained into the row 2 (in gray), is equal or grater than the activity's start date or the same date's value (row 2) is equal or less then the activity's finish date, add an "X" into the cell. Otherwise leave the cell blank. Formula for the cell C4 is:

=if(AND((E$2>=$C4),(E$2<=$D4)),"X","")

You can copy and paste this formula into the other cells.

So, if a cell satisfies the previous rule, you can use conditional formatting, to change the cell's background from the default color (white) to another color.

Click on cell background color icon and select Change with rules.



Select the following rules:

Text is exactly = X

... then, select the background color you want to apply to the cell.

How you can see in the first image, I used two color: yellow for activities and green for activities'tasks. You can repeat the previous rule changing the color from yellow to green for your tasks.

Take a look at this spreadsheet here.

Similar posts

Implement a Project Plan and manage activities with Google Spreadsheets
Project Management: a project plan with Excel (template) Google Spreadsheets: formulas tutorial

Google Spreadsheets: formulas tutorial

After my previous post about how to implement a Project Plan and manage activities with Google Spreadsheets, I received some emails which asked to me to explain some useful formulas used frequently into spreadsheets. I published an example's spreadsheet which you can see here whith an example about these functions:

- Count If
- Vertical Look Up
- Sum If
- Count Blank

You can use these functions, with the same syntax, using Excel (remember only to use ";" to separate functions' attributes and not ",").


Download this tutorial for Excel

Take a look at Google Spreadsheet file

On-line presentation

If you want you can also take a look at this presentation:




Count If
countif() is a very useful function which you can use to count an element in an interval. In the following example I'm looking foor how many times is repeated the name "Lara" into the interval. The result is 2.

Formula is:

=countif(B13:B17,B8)

...we can "traslate" this formula in this way: count how many times the value contained in the cell B8 is repeated in the interval B13:B17.


Vertical Look Up
vlookup() is another useful function which I use frequently to manage data in a spreadsheet. You can use it to find a value (not repeated) in an interval and return a value contained ina column in the same row of the found value. In the following example I found City and Age for "Jason". The result is Dublin and 34:

Formula is:

=vlookup(F8,F13:G17,2,)

... search for the value in the cell F8 (Jason), in the interval F13:G17, and return the value contained into the interval column with index 2 (Column G).


Sum If
sumif() executes a conditional sum of values contained in an interval based on some criteria:

Formula is:

=sumif(J11:K15,J6,K11:K15)

...sum in the range J11:K11 the values contained in the column Points (interval K11:K15) where the value in the interval J11:J15 is equal to J6.


Count Blank
countblank() count how many blank (empty) cells there are in an interval:


Formula is:

=countblank(N11:N15)

Count blank cells in interval N11:N15.

I suggest you to download the spreadsheet in .xls format or take a look at the Google Spreadsheets file here.

Gantt Chart with EditGrid online spreadsheets

Today I tried EditGrid to implement a simple Gantt Chart. EditGrid is a free and very powerful on-line application, which you can use to create and manage your spreadsheets.

I found immediatly a clear interface, simple and more similar then Google Spreadsheets to Excel and, moreover, I found a more fast and better response then Google Spreadsheets during the use and a full and more complete control for cells' formatting (border, background, number format...).

Take a look at the spreadsheet

My Gantt chart is a simple list of activities with a start date and a finish date.


Activity's Start Date is calculated like the minimum value of all Start Dates of tasks that compose it.

=min(C6:C9)

Activity's Finish Date is calculated like the maximum value of all Finish Dates of tasks that compose it.

=max(D6:D9)

Task's Duration is calculated using datediff() function:

=datedif(C7,D7,"d")

Unfortunately what it lacks is conditional fromatting to apply to cells (present in Google Spreadsheets), so you have to rapresent the Gantt Chart manually.

In general, I think EditGrid has many points to its favor respect its more famous adversary in terms of usability and funcionalities and it can be a real alternative to Excel more than Google Spreadseets.

Related Posts

Project Management: organize a project plan
Implement a Project Plan and manage activities with Google Spreadsheets
Project Management: a project plan with Excel (template)

Implement a Project Plan and manage activities with Google Spreadsheets

In the past days I added some post about the topic project management: organize a project plan and implement a project plan with Excel. I added also an Excel template that you can download here. In this post I'm going to explain how to implement a project plan using Google Spreadsheets. You can see the example at this link.

Copy and use this spreadsheet

Create a copy of this spreadsheet for your Google Apps account


File structure
My Google Spreadsheets file has two woorksheet WBS (work breakdown structure of the project) and User View (a list with resources to assign to each task.)


Work breakdown structure
In WBS sheet you have the following columns: WBS (task ID), Task (task description), Pred (predecessor), % (percentage of completion), Owner (resource assiged to the task), Role (owner role);


Owner role is calculated looking for the name insert into the column Owner into the sheet User View using VLOOKUP function (vertical lookup), for example:

=vlookup(E5,'user view'!A:B,2,)

...it search the value contained into the cell E5 on the sheet user view in a colum's interval from A to B, and return - if the value in E5 exist in the column A - the result from the column with index 2 (column B).

Sheet WBS has also other columns like star date, finish date, re-planned star date, re-planned finish date, project delay, and a section to assign a variable cost + fixed cost + a manual adjust to each task.

Take a look at the spreadsheet for more info about all columns.


Resources
All resources (task's owner) are located into a second worksheet User view. I have added some basic columns and the hour cost for the resource.


Total Task is calculated automatically from the sheet. I have simply added a function, COUNTIF, that count how time an user si repeted into the sheet WBS

=countif(wbs!E:E,A3)

... it updates the counter if finds in the column E of the sheet WBS a value equal to the value contained into the cell A3.


Excel or Google Spreadsheet?
Sincerly, I found Google Spreedsheets a good application to create spreadsheets with a medium level of complexity, but the "response", for an Excel-addicted, is not the same. In any case it's a useful service and, above all, it's free!

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 :)