Excel 2007 : Graphing question

[SumiLou]

Hey everyone,

I'm a new member, and new to Excel 2007, so I apologize in advance for any mishaps. I would absolutely love and appreciate if anyone could help me with my seemingly simple yet complicated graphing question! I have been trying for a couple of days now to figure out how to represent the data in the way I need it, and just can't figure it out.

I have a table of weather measurement data (Fields 1-3 are measurements like Temperature and Wind Direction) that looks kind of like this. It is not a table of the measurements themselves, but only a table of the availability of that data:

Locations Year Field 1 Field 2 Field 3

A 1900 0 1 1
A 1950 1 1 0
A 2000 0 0 0

B 1900 1 1 1
B 1950 0 0 1
B 2000 1 1 1

C 1900 1 0 0
C 1950 0 0 0
C 2000 1 1 1

Where the 1 represents that measurement data regarding the Field is available and 0 represents that measurement data for the Field isn't available.

I am trying to make a graph for the table to show when information is available and when it isn't. I imagine it to look something like this. The x is there when some data is available (based on the 1's from the table), and there is a blank when there is none (based on the 0's from the table).

A x x ----


B x x x


C x ---- x

1900 1950 2000


Since location A has only 0's for the year 2000, and location C has only 0's for the year 1950, they are represented by blanks in the graph. Everything else gets x's because there is some data available, even if some Fields are missing (have 0's) for that year. So to have a blank, all Fields must be missing (all must have 0). If even one Field is available (has a 1) then there is an x.

I just need a visual display of the years where measurement data is available and isn't available for each location.

I have no idea how to draw up a graph in Excel to do this and am so frustrated trying to figure it out. Would anyone else have any ideas? I'm sorry if this is really confusing-- I might not have explained things properly, my brain is fried!

Thank you!

[teylyn]

Hi and welcome to the forum.

something like the attached maybe?

I've inserted a summary column C where a value is calculated based on whether or not the neighbouring cells have at least one value greater than 0. If there are no readings, the cell will show #N/A.

Then column C is charted in an XY scatter chart in three different series, A, B and C. Since each series has a different value for the Y , they all align nicely in rows.

Is that what you're after?

Check back if you have questions

cheers

[SumiLou]

That works! Looking at your attachment was especially helpful, I'll have to do that too next time. Thank you SO much!!

[teylyn]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[SumiLou]

Ok, I will. Thanks, teylyn.

Just a quick note to anyone who reads this looking for a similar solution. I did everything suggested, except that I wanted to show the gaps in each series instead of a continuous line. To do this I changed the cells showing N/A in the sum column to blanks instead. When graphing this, in the add data box you can choose to show blank cells as gaps. Worked really well!