Dynamic area chart using IF statement

[joelarabia]

I'm having trouble drawing an area chart that ends sharply at a specifided value rather than drawing a diagonal line down to zero. Let me explain

I watched an Excellsfun youtube tutorial that used the IF statement to chart a subset of normal curve data as the second series in an area chart.

It looks like an easy way to add some interactivity. I enter a single value, and the IF statement checks to see if the data in the first sereis is less than or equal o that number. Once the first series data exceeds the number I entered, the IF statement populates the second series with "", which is to say a blank entery.

Here's my spreadsheet data and the chart it produces:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Series A	Height A	Height B			5.5
2	1	0.0407085	0.0407085		d	2.87228
3	2	0.0661077	0.0661077		x	5
4	3	0.0950994	0.0950994
5	4	0.1211884	0.1211884		Height A:	=NORM.DIST(A2:A11,$F$1,$F$2,FALSE)
6	5	0.1368053	0.1368053		Height B:	=IF(A2<=$F$3,B2,"")
7	6	0.1368053
8	7	0.1211884
9	8	0.0950994
10	9	0.0661077
11	10	0.0407085
12
13
14
15
16
17
18
19
Normal

Excel 2010

Cell Formulas
Range	Formula
B2	=NORM.DIST(A2:A11,$F$1,$F$2,FALSE)
B3	=NORM.DIST(A3:A12,$F$1,$F$2,FALSE)
B4	=NORM.DIST(A4:A13,$F$1,$F$2,FALSE)
B5	=NORM.DIST(A5:A14,$F$1,$F$2,FALSE)
B6	=NORM.DIST(A6:A15,$F$1,$F$2,FALSE)
B7	=NORM.DIST(A7:A16,$F$1,$F$2,FALSE)
B8	=NORM.DIST(A8:A17,$F$1,$F$2,FALSE)
B9	=NORM.DIST(A9:A18,$F$1,$F$2,FALSE)
B10	=NORM.DIST(A10:A19,$F$1,$F$2,FALSE)
B11	=NORM.DIST(A11:A20,$F$1,$F$2,FALSE)
C2	=IF(A2<=$F$3,B2,"")
C3	=IF(A3<=$F$3,B3,"")
C4	=IF(A4<=$F$3,B4,"")
C5	=IF(A5<=$F$3,B5,"")
C6	=IF(A6<=$F$3,B6,"")
C7	=IF(A7<=$F$3,B7,"")
C8	=IF(A8<=$F$3,B8,"")
C9	=IF(A9<=$F$3,B9,"")
C10	=IF(A10<=$F$3,B10,"")
C11	=IF(A11<=$F$3,B11,"")
F1	=AVERAGE(A2:A11)
F2	=STDEV.P(A2:A11)

As you can see, the second series should end with a straight vertical line at the value I entered, which is the last non blank value. However, the chart does not. It draws a line from the height of the last value to zero and the first blank value. Not exactly accurate as far as area is concerned.

I'm using Excel 2010.

Thanks in advance for your help

 

[pgc01]

As you can see, the second series should end with a straight vertical line at the value I entered, which is the last non blank value. However, the chart does not. It draws a line from the height of the last value to zero and the first blank value. Not exactly accurate as far as area is concerned.

I'm using Excel 2010.

Hi
Welcome to the board

That's the normal behaviour.

If you want a vertical line you can define the input range for that series as ending at the last value. In the case of your exampe,

- select the chat
- in the Design tab of the ribbon press the Select Data
- edit the source data for the Height_B series adjust the source values to
$C$2:$C$6

... and you should see the vertical line you want.

If the number of values in this series changes a lot, maybe you want to define a dynamic range for the second series, so that it automatically ajusts.

 

[joelarabia]

Thank you for your reply. I've added a dynamic range defined by the non-blank values in the column. It works well now.

 

[pgc01]

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