Google Spreadsheets has been getting better and better over the last couple of years, and they have added many features that I have waited for, however there is still one much requested feature that they have not yet implemented.
The charts within Google Spreadsheets are not able to manipulate the data, they can simply report on it. Many people, myself included, have wanted a chart that is able to aggregate data and report on the totals obtained. For example a call log spreadsheet where employees use a form to enter data about calls they have received. Let's say the spreadsheet has the following columns:
Date | Name | Call Length | Notes
Now let's say that the employees each receive between 10 and 20 calls per day for varying lengths of time. We want a chart that will give us a pretty graph on how much time was spent on the phone by each employee for each day. But since all the data is spread out by call there is no chart that can currently provide this.
To solve this problem we must first create a new sheet with the following columns:
Date | Joe | Sue | Greg | John
Where the names are an exhaustive list of the employees receiving the calls. It's not a pretty method of doing things, but it is the method I have found to work. The Date column will have the formula
=Unique(MainSheet![DateCellStart]:MainSheet![DateCellEnd])
The cells below it will be auto filled with the CONTINUE formula and google spreadsheets will keep them updated with a unique list of dates from your original spread sheet. In each of the employee columns you will put the value:
=SUMIF(MainSheet![DateCellStart]:MainSheet![DateCellEnd] , [UniqueDateColumnAndCell], MainSheet![Call Length Cell])
This will tell google spreadsheets to hunt through the main date column and find all the dates that match the unique date referenced. It will then sum up all the call length values that correspond to those dates. Now we have a spreadsheet that has the data all summed up nicely in a way that can be easily read by either a human or the pretty google charts. Simply apply a chart to the columns in the newly created sheet and you are done.
A collection of problems and discoveries usually related to the tech industry in some way.
