Google Spreadsheets / Charts Help

April 29, 2008 1:07 PM

Let's say I have a two column chart, with the following values:

Col. 1..........Col. 2
-------...--------
2.................A
5.................A
7.................C
1.................B
9.................C
... ...

Without sorting (i.e., automatically), I would like to be able to, for example, plot all values of column 1 for which column 2="C". Preferably in Google Spreadsheets. I guess what I'm looking for is a way to incorporate logic functions into charts. Does anyone know how to do this, or can you recommend software that can?

I'm using Google Forms to compile user-entered data into a spreadsheet and then would like to display that visually, broken down by different categories.

Thanks for any advice.

posted by underdetermined to Computers & Internet (10 answers total)

Response by poster: Whoops...sorry. I guess that column layout didn't work. The basic idea is that column 1 has numerical data in it and column 2 has categorical data (i.e., A, B, or C).
posted by underdetermined at 1:08 PM on April 29, 2008

I haven't used Google spreadsheets, but I think a filter is what you want.
posted by echo target at 1:18 PM on April 29, 2008

Response by poster: You mean filter the original data into another worksheet and then use that as the source data for the plot?
posted by underdetermined at 1:23 PM on April 29, 2008

Maybe. I don't know Google's app well enough to say. Perhaps someone else has done this successfully.
posted by echo target at 1:31 PM on April 29, 2008

If I understand what you're asking, pivot charts in Excel would do it. Not sure what the possibilities are through Google docs but you could try searching their help/talk sections with the pivot chart term.
posted by yarrow at 2:05 PM on April 29, 2008

I don't know of any basic spreadsheet programs that will allow you to enter formulas as the range for a chart. Certainly not Excel, Numbers, or Google Spreadsheets. You're going to have to use filters or formulas on the data itself and then plot the output.
posted by junesix at 2:07 PM on April 29, 2008

Assuming that you want to display the distribution of Column 1 for different values of Column 2:

In stata, this would be as simple as

kdensity column1 if column2=="C" [if you like kernel densities]

Or even

histogram column1, by(column2) [or something very similar, if you like histograms]

stata is very not free. stata is not a spreadsheet.
posted by ROU_Xenophobe at 2:32 PM on April 29, 2008

If you have excel, play around with the pivot chart function. If you drop Col 1 into the data area and both col 1 and col 2 into the category area, you'll get something close to what you're asking for in this example. You can then use the little drop down arrow in the category area by the col 2 label to filter for the Cs. If the values in Col 1 aren't unique, you might want another column of data that is unique to use for the category axis instead so that you don't wind up grouping by col 1 values. Anyway, mess around, it may do what you're aiming at.
posted by yarrow at 3:34 PM on April 29, 2008

Maybe I'm not understanding the question correctly - could you select all the data in both columns, sort by column 2 in ascending order, and select the data you need to plot a graph from?
posted by Phire at 9:44 PM on May 2, 2008

ACK. Disregard answer. I completely missed the bit about not sorting, apologies.
posted by Phire at 9:45 PM on May 2, 2008