PLOTTING THE BASIC CURVE:
Since there is no specific equation available to plot the chart therefore I tried to create it in two parts:
1. First portion that resembles the Left half of a Normal Distribution.
2. Drawing a line using Curve from Shapes and then plotting points that follow the initially drawn curve.
(The second half of the curve resembles something like an exponential function being plotted.)
The first step to plot this curve was to get a “Line Chart” on the sheet so that whatever we plot we can see how it looks like on the graph.
1. Go to Insert>Chart>Scatter Plot and Press OK.
2. Once inserted, select the Chart, Right click and select the data present B3:B105 to be plotted as X-Value and A1:A105 as Y-Value.
3. Select the Curve, remove the Markers, and increase the line width to 2.25, Line color to Blue.
This sets up the Basic Curve, not exactly as it was in the referred picture, but a partly acceptable approximation of original curve.
[Note: In order to plot the normal curve, the excel function NORMSDIST() has been used that has the Syntex:
NORMSDIST = (X, Mean ,Standard_Deviation , Cumulatve)
…has been used. With Mean (X) ranging from -4 to +1.2 (at a decrement/ increment of 0.2 on each step), we get the first half, the right side of the curve. We have got our first 27 points for the curve through this process but we haven’t plotted it on the same scale (-4 to +1.2) instead on 1~27. The final Excel Formula look like this:
=NORMDIST(X , 0 ,1 , FALSE)
…where X is -4.0, -3.8, -3.6 to 0 and finally 1.2
The second half of the curve is plotted empirically using error & trail method. Once you get this curve, Copy this data & Paste Special > Values to get rid of the formulas and proceed with formatting of the curve.]
PLOTTING THE "YEARS TO MAINSTREAM ADAPTATION":
The points that are plotted on the basic curve have been divided into 04 categories i.e.:
1. Less than 2 Years (Triangular marker with Yellow Fill)
2. 02 to 05 Years (Round marker with without any Fill)
3. 05 to 10 Years (Round marker with Light Blue Fill)
4. More than 10 Years (Round marker with Dark Blue Fill)
5. Obsolete before Plateau (Not Plotted)
We setup a table that contains points for each of the four; the Values of X & the Corresponding values of Y to be found by using a VLOOKUP() and a cell where we can enter Reason.
VLOOKUP() has following syntax:
VLOOKUP = (Lookup_Value, Table_Array, Col_Index_num, Range_Lookup)
Here…
Lookup_Value: Is the value of X (1.0 to 21.40)
Table_Array: Is the corresponding values of Y from Column B (B3:B105) in Sheet1
Col_Index_num: Is 02 since we want to look into the second column
…hence the final formula becomes =VLOOKUP(X-Values, A3:B97, 2)
Using this formula we get all the points for all the four categories that are to be plotted on the graph.
In order to plot these four series, select the chart, Right Click & select “Select Data”, Add a New Series and point to the values of X & Y to add that series to the chart. Same steps will work for rest of the three series.
Now in order for series to appear as what they are in the original picture, select each of the series and:
1. Right click, select Format Data Series > Line Color > No Line
2. While remaining in the same dialogue box, select Marker Option > Built In > Type to select the corresponding type (Triangular or Circular) & Marker Fill > Solid Fill > Color to select the appropriate color for the series.
LABELING THE DATA:
Once we got the four series on our chart, we will have to “Name” each point as mentioned in Original picture. These Names must be entered in the space provided under “Reason” in the tables for each category.
In order to do this, we have two options:
1. We can either do it manually or
2. We can use an add-in that will do it for us.
Since there are many points to be mentioned on the graph, I preferred using add-in instead of manual work, but I will mention here both for the convenience of readers.
LABELING DATA MANUALLY:
1. Select a single point on the chart; so that only one point is select (not entire series).
2. Point the cursor to the formula bar and link it through a formula to cell containing the desired label. (see this picture)
3. Once linked all the points you are done with labeling.
LABELING DATA THROUGH AN ADD-IN:
An add-in that makes labeling a lot easier is available from Rob Bovey, Application Professionals. The add-in looks like this when installed.
It’s a free ware so fell free to download it and install (until you are using it on commercial basis). The add-in could help you by either “Add Labels” or “Manual Labeler”. I opted to use the “Add Labels” that will still make this easier for me, the following dialogue box is shown:
…keeping chart selected, select “Data Series” and Label Range (that is present under “Reason”) this will be finished. Repeat the task for all four data series. You have got labels to your four series. Adjust the position of the labels so that they denote overlap.
ADDING VERTICAL LINES:
The vertical lines present can be plotted as “Scatter graph with line” plotted on secondary axis of the chart. The five lines actually are ten points, two for each of them, plotted on the same scale as that of the primary chart, but on secondary axis and connected through a line…
In order to plot these five series, select the chart, Right Click & select “Select Data”, Add a New Series and point to the values of X & Y to add that series to the chart. Same steps will work for rest of the three series.
Both primary & secondary axis should have one scale, in this case Values on X-Axis are going from 0 to 25 and that of Secondary Axis varies between 0 to 0.45. Once you are finished with these five lines, you may hide the axis.
LABELING VERTICAL LINES:
Using the previously mentioned add-in we can label discontinues ranges as well. In this case, the point that needs to be labeled is actually the point touching the X-Axis, the lower end of the line. So select lower point of all fove lines and use “Manual Labeler” to add labels to it. See this pic:
The “manual labeler “ will ask you to select the series and the point to be labeled, once selected and pressed apply, the label will be placed on the point. You have to do it for all five points on X-Axis
(I used manual labeler here because labels are not contagious to each other, had they been, “Add Labeler” could have sufficed our need.)
THE FINAL STEP:
Increase the font size of the last five points so that they appear highlighted. Move the last line to the right most side so that you can only see the label but not the line. Place this chart on a separate sheet and freeze pan so that if any one scrolls the sheet, the chart retains its position. That all!
That’s all from me. Thank You.
This comment has been removed by a blog administrator.
ReplyDeleteGreat and that i have a nifty give: House Renovation What To Do First home renovation experts
ReplyDelete