With new enhanced and efficient editing capabilities, you can now make the most of your spreadsheet in Google Docs. Now writing formulas and applying functions are as easy as in Excel. Google Docs recently added new functionality that will allow you to formulate datasets instantly. In this post we will be putting some light on using formulas and functions in Google Docs Datasheet.

Along with validating data, inserting gadgets and adding  new sheets,  you will see another addition – Formula Bar.

By default, the formula bar is hidden, to make it apparent go to View menu and click Show formula bar.

Now lets try to make an easy Google Docs Spreadsheet containing formulas and functions. We will be creating a student grading system in it, having fields Name, Course, Marks, Grade and Prize Money.

Our main emphasize will be on Grade and Prize Money field, where we will be evaluating the grades secured by students and their resultant prize money. We have also included a criteria field in the same spreadsheet.

For evaluating grades we will be writing VLOOKUP function in first column as;

=VLOOKUP(D2,\$H\$1:\$I\$6,2,TRUE)

While applying formula in the spreadsheet, it will show all recommendations by displaying the complete syntax of it. It will automatically yield the desired result, as shown in the screenshot below.

Now drag the plus sign towards the end of the column to apply it over.

For evaluating prize money, we have also included a criteria table. Just repeat the procedure of applying VLOOKUP formula as we did in the Grade column.

Now we will evaluate Status of the students that will either be Pass or Fail. For this we will be adding a simple formula, which will check Grade field values against it and show the status.

=IF(E2=”A”,”Pass”,IF(E2=”B”,”Pass”,IF(E2=”B+”,”Pass”,IF(E2=”C”,”Pass”,IF(E2=”C+”,”Pass”,IF(E2=”D”,”Fail”))))))

The evaluated status of the students is shown in the screenshot below.

With enhanced capabilities of Google Docs, you can now create, collaborate and importantly formulate spreadsheets on the fly without having Excel installed on your system.

• EWH

=IF(E2=”A”,”Pass”,IF(E2=”B”,”Pass”,IF(E2=”B+”,”Pass”,IF(E2=”C”,”Pass”,IF(E2=”C+”,”Pass”,IF(E2=”D”,”Fail”))))))

equals

=IF(E2=”D”,”Fail”,”Pass”)

Much easier.