# Area Chart Problem

#### tabbymulla

##### New Member
The chart on the right is the required version but when I include complete series values from B to F column as series data, it shows a slope at end as shown in left chart. How can I it be corrected?

If it can only be corrected by limiting the series value range from B to D column like i did for chart on the right, is there any way that chart automatically selects that range based on the value of B1. for e.g. using INDEX and MATCH function.I cannot get INDEX and MATCH function to work when I use in chart but works when I use it in sheet cell.

I have already tried leaving the cells empty and also using "Show empty cells as Gaps" option.

E4=IF(E\$2<=\$B\$1,D4+E3,NA()) #### pgc01

##### MrExcel MVP
Hi

For ex. for the first series define the name

Name: Series1Y
Refers to: =OFFSET(Sheet1!\$B\$3,,,,COUNTIF(Sheet1!\$B\$3:\$F\$3,">0"))

and then in the chart use for the series values:

=Sheet1!Series1Y

#### tabbymulla

##### New Member
Hi

For ex. for the first series define the name

Name: Series1Y
Refers to: =OFFSET(Sheet1!\$B\$3,,,,COUNTIF(Sheet1!\$B\$3:\$F\$3,">0"))

and then in the chart use for the series values:

=Sheet1!Series1Y
Kindly elaborate. I am not familiar with this type of referencing in chart. I mean in which cells I should use that OFFSET formula and how should I enter this data so that the chart recognizes the series. Thanks
K

#### pgc01

##### MrExcel MVP
Kindly elaborate. I am not familiar with this type of referencing in chart. I mean in which cells I should use that OFFSET formula and how should I enter this data so that the chart recognizes the series. Thanks
K
Hi

The formula is not to be written in a cell. YOu should define a name with it.

In ribbon->formulas->Name Manager press New and enter

Name: Series1Y
Scope: Sheet1 (or other worksheet if that's the case)
Refers to: =OFFSET(Sheet1!\$B\$3,,,,COUNTIF(Sheet1!\$B\$3:\$F\$3,">0"))

and then press OK

Your see in the list of names in the Name Manager the name you have defined.

Now go the the chart, select the chart, ribbon->Design->Select Data

click on the series, press the button Edit and in the Series Values box write

=Sheet1!Series1Y

Press OK and you should your series values stopping at the value before the zero.

(I assumed that the zeroes are at the end)

• tabbymulla

#### Jon Peltier

##### MrExcel MVP
I rearranged your data a bit. Your actual data is in the top four rows.

Below that I have YTD data, but double the number of columns, because sometimes it helps to make the data crazy so the chart comes out right, and easy.

The formula in B7, copied into B7:F8 is:

=IF(INDEX(\$B2:\$F2,B\$6)=0,0,SUM(INDEX(\$B2:\$F2,1):INDEX(\$B2:\$F2,B\$6)))

This gives a YTD only if the current month is nonzero.

The formula in G7, copied into G7:K8 is:

=IFERROR(IF(INDEX(\$B2:\$F2,B\$6+1)=0,0,SUM(INDEX(\$B2:\$F2,1):INDEX(\$B2:\$F2,B\$6))),0)

This gives a YTD only if the following month is nonzero.

The formula in B9, copied into B9:K9 is:

=SUM(INDEX(\$B4:\$F4,1):INDEX(\$B4:\$F4,B\$6))

Then I selected A6:K9 and inserted a stacked area chart (Chart 1).

I changed the chart type of the Target series to a line (Chart 2).

Then I formatted the horizontal axis, and changed the axis type from Automatic to Date (Chart 3). I could get away with this because the X values were numerical. Excel plots all values for a given date a the same point along the axis, and it sorts the data by X values first. So the X values are plotted 1,1,2,2,3,3,4,4,5,5. And at X=3 we have a YTD value then a zero value, at the same horizontal point, which makes the vertical cutoff we need. • tabbymulla

#### tabbymulla

##### New Member
Hi

The formula is not to be written in a cell. YOu should define a name with it.

In ribbon->formulas->Name Manager press New and enter

Name: Series1Y
Scope: Sheet1 (or other worksheet if that's the case)
Refers to: =OFFSET(Sheet1!\$B\$3,,,,COUNTIF(Sheet1!\$B\$3:\$F\$3,">0"))

and then press OK

Your see in the list of names in the Name Manager the name you have defined.

Now go the the chart, select the chart, ribbon->Design->Select Data

click on the series, press the button Edit and in the Series Values box write

=Sheet1!Series1Y

Press OK and you should your series values stopping at the value before the zero.

(I assumed that the zeroes are at the end)
Your assumption is correct and the arrangement works.
Thanks.

#### tabbymulla

##### New Member
I rearranged your data a bit. Your actual data is in the top four rows.

Below that I have YTD data, but double the number of columns, because sometimes it helps to make the data crazy so the chart comes out right, and easy.

The formula in B7, copied into B7:F8 is:

=IF(INDEX(\$B2:\$F2,B\$6)=0,0,SUM(INDEX(\$B2:\$F2,1):INDEX(\$B2:\$F2,B\$6)))

This gives a YTD only if the current month is nonzero.

The formula in G7, copied into G7:K8 is:

=IFERROR(IF(INDEX(\$B2:\$F2,B\$6+1)=0,0,SUM(INDEX(\$B2:\$F2,1):INDEX(\$B2:\$F2,B\$6))),0)

This gives a YTD only if the following month is nonzero.

The formula in B9, copied into B9:K9 is:

=SUM(INDEX(\$B4:\$F4,1):INDEX(\$B4:\$F4,B\$6))

Then I selected A6:K9 and inserted a stacked area chart (Chart 1).

I changed the chart type of the Target series to a line (Chart 2).

Then I formatted the horizontal axis, and changed the axis type from Automatic to Date (Chart 3). I could get away with this because the X values were numerical. Excel plots all values for a given date a the same point along the axis, and it sorts the data by X values first. So the X values are plotted 1,1,2,2,3,3,4,4,5,5. And at X=3 we have a YTD value then a zero value, at the same horizontal point, which makes the vertical cutoff we need. Thank you Sir. Very out of the box approach to the problem.

PS: Big fan of Peltier Tech Blog.

#### pgc01

##### MrExcel MVP
I'm glad it helped. Thanks for the feedback.

1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...