Tails of line charts

**matt4003** · 07-23-2010, 02:14 PM

Hi All,

I have a series of charts that have some formating that I would like cleaner. I have been looking and looking for this solution, so far no luck.

Basically, my "big" spreadsheet has lots of data points which all stretch across the same x axis (which is several years long). All data for the chart is gathered from a master table that uses lookup's to find its specific data, so there is always some data point either "0" or "-" generated from the formula. The line chart creats a tale from the last "real" data point to the "0" or "-". I have lots of data points and lots of charts.....how do I get ride of this tail without having to manually adjust each data point or compromise the data??

Attached is a example file. The RED tails I would like to have automatically gone.

Please let me know your thoughts.

Cheers,
Matt

**shg** · 07-23-2010, 02:21 PM

Replace the zeroes with =NA()

**matt4003** · 07-23-2010, 02:27 PM

Hello shg,

Great to hear from you again. I hope all is well!!

Thanks for the hint. But I can't get that to work in the test file.

Is the formula suppose to look like: =IF(ISNUMBER(Sheet2!G2*1.1),(Sheet2!G2*1.1),NA())

Cheers,
Matt

**shg** · 07-23-2010, 03:01 PM

Close:

=IF(ISNUMBER(Sheet2!B2), 1.1 * Sheet2!B2, NA())

**matt4003** · 07-23-2010, 03:07 PM

Thanks shg,

That works in my test file! Thanks.

I tried the approach in my actual file, where the formula is a little more complicated and it didn't work:
=IF(ISNUMBER(MATCH(AG$1,FCSTMonth,0)),SUMIF(FCSTCust,$A4,INDEX(FCSTData,0,MATCH(AG$1,FCSTMonth,0))), NA())

These are named ranges:
FCSTMonth
FCSTCust
FCSTData

Am I just placing the NA() wrong again?

Cheers,
Matt

**shg** · 07-23-2010, 03:11 PM

Use the Evaluate Formula button to step through your formula and see what it's doing.

**matt4003** · 07-23-2010, 03:31 PM

shg,

I am not entirely sure why the ISNUMBER logic. When I take out that whole first part, IF(ISNUMBER(MATCH(AH$1,FCSTMonth,0)) I seem to get the same results.

So if the formula was a little more straight forward like: =SUMIF(FCSTCust,$A4,INDEX(FCSTData,0,MATCH(AG$1,FCSTMonth,0)))

Where would the NA() go? I keep getting "too many arguements.

Cheers,
Matt

**matt4003** · 07-23-2010, 03:47 PM

Hello shg,

Well I figured out, with your NA(), how to augment the statement so I can include the original ISNUMBER and the SUMIF.

=IF(SUMIF(FCSTCust,$A4,INDEX(FCSTData,0,MATCH(C$1,FCSTMonth,0)))=0,NA(),IF(ISNUMBER(MATCH(C$1,FCSTMo nth,0)),SUMIF(FCSTCust,$A4,INDEX(FCSTData,0,MATCH(C$1,FCSTMonth,0))),0))

Basically, adding an additional IF statement to include the look for "0" and returing NA() if true, if not, then evaluate the original statement.

Seems to work.

If you have a more elegant or less expensive formula, let me know.

Many thanks!!

Cheers,
Matt