Area Chart Problem

[tabbymulla]

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]

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]

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]

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)

 

[Jon Peltier]

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]

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]

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]

I'm glad it helped. Thanks for the feedback.