Excel 2010: Chart that fills in space below and above 2 lines
Posted on 2014-11-10
I have a client logs daily attendance for his students in an Excel file. He wants me to make a chart that represents when average attendance falls above or below his expected ranges. As such, he would like for it to contain the 3 items listed below. Will you read the below and let me know how to do this? I know how to make the 3 lines, but making the 3rd line fill in whenever it is above or below the first two, has me stumped.
1) Min Expectation Line: horizontal line that represents the min he expects attendance to be. (i.e., 60%).
2) Max Expectation Line: horizontal line that represents the max he expects attendance to be. (i.e., 80%).
3) Average Line: A line that represents the average for all his students each day, but fill in the line whenever it is above line 1 above or below line 2 above.
A sample of what the data looks like is below.
Date MinExpectation Max_Expectation Average_All_Students
1/6/14 .6 .8 .54
1/7/14 .6 .8 .75
1/8/14 .6 .8 .68
1/9/14 .6 .8 .85
1/10/14 .6 .8 .57
1/11/14 .6 .8 .92
Example: On 1/11/14: the chart would fill in below line 3 and above line 2.
Example: On 1/10/14: the chart would fill in above line 3 and below line 1.
Example: On 1/9/14: the chart would fill in below line 3 and above line 2.
Example: On 1/6/14: the chart would fill in above line 3 and below line 2.