Solved

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

Posted on 2014-11-10
5
129 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 39

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 39

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 25

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

770 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