Adjusting charts to reflect data properly

**BillBeachwood** · 02-02-2009, 09:11 PM

This is probably not doable, but here it goes:

I've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.

I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).

The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.

This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "$0.00" for periods of time where nothing has sold.

To have a Price per square foot of $0.00 is incorrect since in reality nothing was sold.

I would like to be able to eliminate the portions of the graph that have the "$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.

I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?

If anyone has any ideas, I would really appreciate it.

Thanks in advance for all your help.