Solved

Excel 2010: Chart that fills in space below and above 2 lines

Posted on 2014-11-10
5
131 Views
Last Modified: 2014-11-24
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.
0
Comment
Question by:ouestque
  • 2
5 Comments
 
LVL 40

Expert Comment

by:als315
ID: 40434426
Look at sample. Stacked bar chart is used
EE-28554487.xlsx
0
 

Author Comment

by:ouestque
ID: 40437134
Thanks als315. Nonetheless, I'm looking for something closer to the chart I have attached. See the chart on the left. Is this possible?

Keep in mind the chart on the left has a freeform shape over the gaps, I am trying to get the chart to fill in those parts automatically.
EE-28554487-Update.xlsx
0
 
LVL 40

Expert Comment

by:als315
ID: 40437957
May be in Excel 2015 it will be possible :)
Now you can split each point into many (line approximation of your data with step - one hour or even less) and use same idea.
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40450234
Hi,

can you check this thread of that is what you are looking for?

Jon is the top expert in charts.

http://peltiertech.com/fill-under-between-series-in-excel-chart/
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question