Making a graph with null data or text in Google Spreadsheet

November 3, 2010 8:14 PM

Google Documents changed the way it handles chart data and now I can't make a simple graph.

I track my lifting with Google Docs. Part of it is determining my lift:mass ratio. But I don't do every lift every day, so I would use a formula like =IF(G2=0, "N/A", G2/B2) to make sure that I didn't have graphs that dropped to zero every alternate day.

Previously, the chart function handled this fine. Wherever it saw the text "N/A", it wouldn't put a dot on the chart and the line graph was just fine. Now, with Google's cool new awesome amazingly new super-simple smart charts, it flips out over something this basic and tells me "The required data format for the line chart doesn't match the current data.
The first column should be a string, and contain the category label. Any number of columns can follow, all must be numeric. Each column is displayed as a separate line"

So what's the solution? I don't want to put in fake data, and I don't see any way to go back to the old charts that, you know, worked logically.

posted by Gandhi Knoxville to computers & internet (6 answers total)

I tried to reproduce your problem and couldn't. Here's the spreadsheet, with a line graph that's treating the string "N/A" as empty. Maybe I'm seeing a different version of Docs than you?

Anyway, a couple of suggestions:

Replace the string "N/A" with the empty string "". Hopefully the charts are smart enough to treat the empty string as data missing.

Switch from a line graph to a bar graph, and leave 0s in your columns with missing data. A 0 drawn in a bar graph is not distracting.
posted by Nelson at 7:33 AM on November 4, 2010

Interestingly, your data and chart works on your spreadsheet, but if I copy/paste it into my spreadsheet, all of sudden it magically doesn't work any more. And of course, if I copy/paste my data into a brand-new spreadsheet, it still doesn't work.
posted by Gandhi Knoxville at 7:47 AM on November 4, 2010

I was going to try to just do the bar graph thing, which is a possible but inelegant solution, and got this error when doing Find/Replace: Note: Text can't be replaced in formula cells. Thanks, Google, let me just do it manually 500 times.

Fuck it. The correct answer is "Google Documents is fucking garbage, and you shouldn't use it."

I'll just throw a Numbers spreadsheet on my Dropbox. Thanks anyway, Nelson.
posted by Gandhi Knoxville at 7:51 AM on November 4, 2010

Last comment, I put some data up on your open spreadsheet and you can see how it doesn't work. :/
posted by Gandhi Knoxville at 8:06 AM on November 4, 2010

I'm not sure I understand your find/replace problem. Do you understand how formula copying works with relative references? If cell J2 contains the formula "=IF(G2=0, "N/A", G2/B2)", and then you copy J2 to J3, then the text pasted in to J3 is magically changed to "=IF(G3=0, "N/A", G3/B3)". (See also the difference between G3/B3 and $G$3/$B$3). In general you edit a formula once, in one cell, then copy it down to the whole row. No need to do something "manually 500 times".

I looked at your edits to my spreadsheet and it seems to be working fine. I can't really "see how it doesn't work" since you didn't explain what you were looking for, but my guess is you're unhappy that no line shows up for column J, d/m, in the graph on the right. But the data is there in the chart. and if you turn on points you'll see where they are. The chart is unwilling to interpolate data between an existing point and a non-existing point, and for good reasons. You either need to code your own interpolation or else use a chart type that doesn't do interpolation. Turning points on reveals your data. Switching to bar charts reveals your data.

Your comment "Google Documents is fucking garbage" bummed me out, so I'm not going to spend more time here helping you. If you can't figure it out I suggest asking in the Google Docs forums or maybe trying superuser.com. Google Docs charting is limited, particularly compared to Excel, but it's a consistent and useful tool when used correctly.
posted by Nelson at 8:29 AM on November 4, 2010

Sorry to grump and bum you out, Nelson; I just don't see why they made it so difficult to convert the old, working docs over to the new system. How do you turn on points? I see no difference between the graph on the left from the one on the right, and yet, one has points on and one doesn't.
posted by Gandhi Knoxville at 9:07 AM on November 4, 2010