tabbymulla

New Member
Joined
Aug 29, 2019
Messages
10
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())

yhtOU.png
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.

zWjQog7.png
 
Upvote 0
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.
 
Upvote 0
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.

zWjQog7.png

Thank you Sir. Very out of the box approach to the problem.

PS: Big fan of Peltier Tech Blog.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top