Area Chart Problem

tabbymulla

New Member
Joined
Aug 29, 2019
Messages
9
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
Joined
Apr 25, 2006
Messages
19,749
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
Joined
Aug 29, 2019
Messages
9
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
Joined
Apr 25, 2006
Messages
19,749
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,728
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

New Member
Joined
Aug 29, 2019
Messages
9
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
Joined
Aug 29, 2019
Messages
9
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top