Peltier Tech Blog - Peltier Tech Excel Charts and Programming Blog

Time 2020-07-17 21:03:12

Web Name: Peltier Tech Blog - Peltier Tech Excel Charts and Programming Blog

WebSite: http://peltiertech.com

ID:27531

Keywords:

Blog,Tech,Peltier,

Description:

My data data is in a two-column Table of dates and corresponding weights; I ve named the Table DataDateWeight. In my last article, I used a combination of Dynamic Arrays, XLOOKUP, and the new LET function to build a table in the worksheet, starting in cell D2, which has month as column headers and day numbers in the first column of each row. This will produce a grid of weights with a few missing values. I can feed this directly into Peltier Tech Charts for Excel, the VBA add-in I use to make box plots.Power Query is a powerful set of tools that let you import data from a variety of places and then manipulate that data to make it useful. In their infinite wisdom, Microsoft has incorporated Power Query into Excel (as well as Power BI); in their infinite ability to confuse, they have renamed the Excel version Get and Transform. They re really synonyms, since what Power Query does is Get and Transform data.The Applied Steps section in the task pane shows the contents of this query that I m writing. So far we see Source and Changed Type. When we started, Power Query used the Table as its source, then applied its best guess at what data type was present in each column. Looks good, as we can see from the Date and 123 (whole number) icons in the header row.Type the closing parenthesis, and the icon changes to a green checkmark, and the message becomes surprisingly comprehensible, No syntax errors have been detected.Click OK and the column is added. Congratulations, you ve just written your first statement in M Code. Note the new Added Column entry under Applied Steps.The abc icon in the new column header indicates that the Day column is formatted as text. Right click the column header, choose Change Type Whole Number.The icon changes to the 123 whole number icon. That s another M Code statement, by the way. You didn t even know you were writing it, but Power Query records every step you take, so you can repeat the query, or edit it as your model evolves.We can transform our Date column in place. On the Transform tab, click on Date, then choose Month Name of Month.Our dates have been converted into month names, and the data type icon has changed from a miniature calendar to the abc text icon. Nice, now let s abbreviate it. On the Transform tab, click Extract First Characters.With the Date column still selected, click on Pivot Column on the Transform tab.The Pivot Column dialog asks which column to use for values. If we made a Pivot Table in regular Excel, Month would be in the Columns area, Day in the Rows area, and Weight in the Value area. So select Weight and click OK.Power Query pivots our months into column headers, and it looks like we re done with our query. The column data types are all 123 whole numbers, and everything looks great.The Advanced Editor shows our entire query in M Code. If we wanted to and if we were smart enough, we could edit the code here.It s time to put the data back into Excel, so we can do something useful with it. The first button on the Home tab is Close Load. We re going to load the query into a Table on our worksheet. Out of the box, Power Query s default is to load into a Table, but you can change your default. My default is to load the data into the workbook s Data Model (like an advanced pivot cache) without a Table, so I ll click Close Load To In the Import Data dialog, I ve clicked Table and selected the cell where I want my Table to go.Click OK and wait a moment for Excel to refresh the query, and the Table appears. Power Query Tables use green formatting instead of the default Excel blue.The query does not immediately update when the data changes, so it s not dynamic the way my formula-based approach from the previous tutorial. However, I can refresh the query using the Refresh button on the Ribbon, or by right-clicking on the Table and choosing Refresh from the pop up menu.I can get to any queries in my workbook by clicking Queries Connections on the Data tab, which pops up a task pane.When I mouse over the query in the task pane, a preview of the query pops up, showing a portion of the query results and some information about the query. I can also edit or delete the query here, and take other actions. If a query is deleted, the table remains in the worksheet, but it is no longer linked to the query or its underlying data.There are many places where you can read about Power Query. The following is an incomplete list of blogs and tutorials written by colleagues.In the first comment, Derek asked for a box plot, so I quickly recompiled my data and built one.Several new features of Office 365 made it very easy to mush my data around, and I realized that this is a good example to show them in action. I used Dynamic Arrays, the XLOOKUP function, and the new LET function.Joe McDaid of Microsoft s Excel team wrote about Dynamic Arrays in Dynamic Arrays and New Functions in Excel! These formulas break the old paradigm of one formula per cell. In classic Excel, you needed a formula for every value you calculated. Sure, we had array formulas, but you had to include all output cells together and enter an array formula in all of the cells at once. If you didn t use the right number of formulas, or if your model expanded, you had to rebuild the formula. Ugh! Plus array formulas, amirite?The new Dynamic Arrays allow you to enter a formula into one cell. Excel figures out how many cells you need, and spills the formula into the required range of cells. One formula, multiple cells. And with a retooling of Excel s underlying grid, any formula can become a Dynamic Array.In Preview of Dynamic Arrays in Excel, Joe tells us that Dynamic Arrays have rolled out to Office 365 subscribers, with users in the Semi-Annual (Targeted) channel getting them starting last month. I presume that users in the Semi-Annual channel getting them soon as well.For years there has been a debate about which is better, VLOOKUP or INDEX/MATCH. I don t know why there was a debate, because INDEX/MATCH is clearly the superior choice. Like Zoolander, VLOOKUP can t turn left, and there were many things that could go wrong with your lookups. INDEX/MATCH has its own problems as well, mostly because of the default Match_Type setting which could lead to bad results.In Announcing XLOOKUP, again by Joe McDaid, we learned of this new lookup function that eliminates the perils and shortcomings of VLOOKUP. It s simpler to use and more flexible, and best of all, it begins with XL. You can read documentation in XLOOKUP Function. XLOOKUP is on about the same release schedule as Dynamic Arrays.According to Announcing LET, the new function allows you to assign a name to a value or intermediate calculation at the beginning of the formula, then use this named argument in the main calculation. The LET function improves performance by computing an intermediate calculation just once and allowing reuse of the computed value, and it helps readability of your formulas by allowing you to use descriptive labels for these intermediate calculations.LET is only available to Office Insiders at this writing, so you ll have to be patient.In my previous post, I examined fluctuations in my body weight between last September and this April. The data is in a two-column Table. I will construct a table in the worksheet, starting in cell D2, which has month and year as column headers and day numbers in the first column of each row. This will produce a grid of mostly weights with some missing values, and it feeds directly into Peltier Tech Charts for Excel, the VBA add-in I use to make box plots.I need to show the months from September through April across the top. I will use the new SEQUENCE function to make a list. In cell E2, I enter the formulaWhich tells Excel to make a sequence with 1 row, 8 columns, start the sequence at 9, and increment by 1. And you see the Dynamic Array in E2:L2 filled with this sequence. If the active cell is within this range, the entire Dynamic Array range is highlighted as shown.But Jon, you may say, you need the months to go up to 12, then start again at 1. And the answer is, no I don t. A few rows below the first Dynamic Array, I have written a temporary formula to check the dates:Which tells Excel to give me a date using 2019 as the year, E2# as the month, and 1 as the day. The hash or pound sign appended to E2# tells Excel to use the Entire Dynamic Array that is anchored in cell E2. So I ve entered this formula in cell E6 only, but it spills into the range E6:L6 to accommodate E2#.And we see that if I define a date using the year 2019 and the month 13, it uses 12 months to increment the year to 2020, and uses the leftover month, so I get January 2020. Well, you probably new that, but it s a nice little trick.In row 9 I repeat the previous formula, but with 31 days. Excel does the same thing with excess days, and converts September 31 to October 1, etc.Now let s fill in the column of days. I ll use another SEQUENCE function in cell D3, an easier one this time:Which is a sequence of 31 rows and 1 column, starting with 1 and incrementing by 1.Meaning, give me the data using the year and month from the date E2# and the day from D3#. This new Dynamic Array fills up the whole rectangular range defined by the two Dynamic Arrays it references. Note the extra dates in the September, November, February, and April columns.The dates are fine (except for those extras, which we ll take care of soon). So let s wrap the date in XLOOKUP.I ve put each argument onto its own line for clarity. Basically, I m telling Excel to look for the date we ve calculated, look in the Date column of Table 6, and if you find it, return the value from the Weight column of Table 6. And if you don t find it, give me a cell that looks blank with .And here are the weights, including some duplications: note that the 10/1/2019 weight of 167 appears at the top of the October column but also at the bottom of the September column.So how do I eliminate those duplicates? Even though Excel computes DATE(2019,9,31) using month 9, the result is month 10. So I ll enter a formula that checks whether the month calculated in the lookup cell matches the month in the header cell: if so, perform the XLOOKUP, but if not, return .=IF(MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#))=MONTH(E2#),XLOOKUP(DATE(YEAR(E2#),MONTH(E2#),D3#),Table7[Date],Table7[Weight],""),"")Clever trick to skip those duplicates, Jon, but that s an unwieldy formula. That big chunk MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#) appears twice, and we ve all written monstrosities which has more than one chunk like this repeated more than twice.So I m going to take the new function LET out for a spin. I ll create an argument called TheDate, and it will stand for MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#). Then in the calculation, I ll use TheDate where the big chunk was before.IF(MONTH(TheDate)=MONTH(E2#),XLOOKUP(TheDate,Table8[Date],Table8[Weight],""),""))This formula is only a few characters shorter than the original, but it s certainly a lot easier to read. And if I had to change something in the definition of TheDate, I would only have to change it in one place, and not worry that I forgot to change it somewhere else.I was looking for data to proof out the tool I was building, and I thought I could use my weight as a decent data set. My wife bought a new digital scale in 2006, and I ve been weighing myself almost every day since then. And being an Excel jock, I put my measurements into a spreadsheet.In the chart below, you can see how I fluctuated around 200 lb for over a decade. Then 20 months ago my wife and I joined Weight Watchers, and over the course of 6 or 8 months I lost 40 lb.I thought looking at the past few months would be a good way to illustrate the use of SPC to track a process. This exercise will construct a series of control charts of this data.There are many other information sources about SPC and control charts. The National Institute of Standards and Technology (NIST) has an online Engineering Statistics Handbook, which has a chapter on Univariate and Multivariate Control Charts. Wikipedia has brief articles with many references covering SPC and Control Charts. And Google shows about 1.2 billion results for SPC and 0.5 billion results for Control Charts.The first step is to identify the data and get it into a form where it can be analyzed. I decided to track from 1-Sept-2019 to 1-Feb-2020. Below is the top of my data worksheet, with a few calculations. The data is in three columns of an Excel Table named Table_1. The first two columns are date and weight, manually entered. The third column is Moving Range (MR), which we will use as a measure of variability in the data. The formula in cell C2 and filled down the Table column isEssentially it determines the absolute value of my change in weight from one day to the next. Any error in the calculation (such as trying to subtract the column header) returns NA(), or the #N/A error. I ve calculated some values in a range beside the table, and I ll explain them as I go along. The little table below the calculations show the formulas I ve used. I ve also named these cells as indicated, to make it easier to use the cells in formulas.The next step is to plot the data. I ve made two charts, one of my weight, the other of the calculated moving range. We look first for any obvious issues in the data, such as the spike late in September. If you look at the data above, apparently I gained 18 lb one day, and lost it the next. A more likely explanation is that I transposed digits in 168 and instead entered 186 in the worksheet. I ll deal with this data issue soon, but for now I ll continue with the SPC construction.I added the calculated items as columns in my Table to make it easier to chart them. Having named the cells, I could use simple formulas in the Table: =Mean in cell D2, =LCL in cell E2, etc.Among my calculations are averages of the weight data (Mean) and of the moving range data (MR Bar). Let s add these as green horizontal lines to the weight and MR charts for reference.So far, so good. Now let s add a measure of allowable or acceptable variation. If the process is following statistical rules and its variability follows a normal distribution, we would use multiples of sigma, the standard deviation, to identify limits. According to the definition of a normal distribution, 68.3% of values fall within ±1 standard deviation of the mean, 95.5% fall within ±2 sigma, and 99.7% fall within ±3 sigma of the mean. By convention, 3 sigma is commonly used to identify acceptable variations.We could measure the sample s standard deviation (SD) directly, multiply it by 3, and use this to determine our limits. But using moving range is more robust, since outliers and non-normal distributions have a greater effect on sigma than on moving range.The average moving range, or MR Bar, is used to calculate control limits. Less commonly, the median of the moving range is used to compute these limits.First we determine MR UCL, which is the Upper Control Limit on the moving range, by multiplying the average moving range by 3.268. This is plotted to the moving range chart as a horizontal orange line (bottom chart below). We would expect 99.7% of our MR values to fall below this limit.In the same way, we calculate the UCL and LCL (Upper and Lower Control Limits) of our individual data. We multiply MR Bar by 2.67, and add it to or subtract it from the mean to get our limits. These are plotted on our chart of individual values as horizontal orange lines (top chart below). Again, we expect 99.7% of our individuals to fall between these two lines.These charts of measurements along with means and limits are called Control Charts. The chart of individual values is called an I Chart (no, not eye chart ), and the moving range chart is the MR Chart. Together they are referred to as an IMR (sometimes ImR) Chart.Our ±3 SD limits are shown in the dashed red lines below (they are calculated as LCL 2 and UCL 2). They fall pretty far outside the MR-based control limits. All points fall well within the SD-based limits, except for the one obvious outlier.In fact, because the outlier causes two excessive moving range values, the MR-based limits are also too wide, and would lead us to accept points that would otherwise be out of control.The spike in my weight in September is a special cause variation, because it is a one-off problem. Since it is obviously not a valid measurement, we can attribute it to a recording error, and ignore it. We want to remove this value from our moving range calculations, since it resulted in limits which were too wide.The other variation we see in the timeline is common cause variation. It comes from variations in inputs, like exercise, meals, and other factors, which are themselves subject to normal variation.In my adjusted table below (Table_2), I ve added two columns. Wt 2 simply repeats the data in Weight, using the Table formula =[@Weight]. I can replace any special cause deviation with =NA() or #N/A in this column. MR 2 uses the same formula as MR, based on the Wt 2 column:Where there was one bad weight and two bad moving ranges, we now have #N/A values in the table, which we can ignore in the chart and in our other calculations.When we plot our individual and moving range values, the chart scales now show much narrower ranges, and there are no longer any obvious outliers: there is one high individual value and corresponding moving range in January, a few low weights in November, and a few high weights in December.Let s add our means and control limits, and see what we have. The MR chart shows the outlying value in late January, and four more moving range values that are just at the limit. In the individuals chart, the low values are within the limits ( in control ) while the high values we eyeballed before are above the UCL ( out of control ).When values are out of control, we have to examine the process, to ensure that nothing is wrong with our process, and that nothing has changed. I can actually explain some of the variations. On Thanksgiving, I ran a Turkey Trot with my daughter, so for a couple weeks I was running more than my usual 3 miles a day: thus the few low values in November. And of course, the few values of 172 coincide with the Christmas and New Year s holidays.Below I ve plotted the SD-based limits along with the MR-based limits. The limits are much closer to each other and closer to the mean than when the outlier was included in the calculations.Here I ve plotted these control limits as calculated with and without the outlier. The outlier had a substantial effect on the limits, especially on the SD limits.When the variation fits a normal distribution, the two sets of limits are close together, with the MR-based limits wider sometimes and the SD-based limits wider other times. The larger the data set, the closer they will be.For the rest of this analysis, I ll ignore sigma and stick to MR-based calculations.We can enhance our IMR Chart by highlighting points which are out of control. I ve added two columns to my table to support this. Wt X has this formulawhich shows the value from Wt 2 if it falls outside the control limits, and #N/A otherwise. MR X has this formulawhich again shows the value from MR 2 if it falls above the control limit, otherwise #N/A.There are other features of control charts that indicate a process which is out of control. These are conditions which are not expected to be found in about 99.7% of cases. Here are a handful of common out-of-control rules; the first one is the one I highlighted above.One point beyond 3-sigma control limits2 of 3 points outside 2-sigma on same side of mean4 of 5 points outside 1-sigma on same side of mean8 consecutive points outside 1-sigma on both sides of mean15 consecutive points inside 1-sigma on both sides of mean9 consecutive points on same side of mean6 consecutive points moving in same direction14 consecutive points alternating up and downAdvanced SPC software highlights any of these situations, in addition to the 3-sigma violations.To show how to manage a growing data set, I added ten more weeks of my weight tracking.Typically, when a process is determined to be steady, the limits are calculated and frozen, then these are extended forward. This is illustrated below: the frozen limits were calculated from September through February, indicated with solid lines, and extended into April, shown with dashed lines.Where I had a few values above the UCL in December and January, I now had several below the LCL and only a few above the mean in February and beyond.This is evidence of a process shift. Several of the additional rules mentioned at the end of the last section would have been triggered. Checking my exercise records gives us an explanation. For much of the period from September through January, I was running 3 miles a day, four or five days a week. The weather in February was rather mild, so I increased my mileage to about 3.5 miles a day, six days a week.The control charts below show control limits calculated over the entire range. The process change is still noticeable, but it s not as clear as with the frozen and extended limits above.Another problem with continually recalculating limits is that the limits move over time. Points which were in control at one time may be pushed out of control by later measurements. A December point at 170 which was in control when the limits were frozen is now out of control under the newly computed limits.We can overcome this concern by staging our analysis, that is, computing different limits for different subsets of our data. In my latest Table below, I ve added a column named Stage, which contains 1 for the first stage and 2 for the second; these can be entered manually or with a formula, which for example increments the stage number on a given date. The control limits are computed separately for different stages.The IMR Chart below shows a staged analysis. Stage 1 looks familiar; the UCL for both MR and Individuals are slightly lower because the large MR late in January coincided with the process change. The violations in stage 1 are the same as before; the few outliers in stage 2 would have been well within the stage 1 limits, but are actually above the stage 2 UCL.It s common practice not to compute a separate average moving range for all stages, especially if the stages have small numbers of points, but instead use an overall MR Bar. The chart below uses this combined measure of variation. Stage 1 s control limits are now a bit tighter, so the low weights measured during the Turkey Trot training in November are now outliers. Conversely, Stage 2 s control limits are slightly wider, so there are no outliers in Stage 2.VBA provides a number of ways to interact with users. MsgBox lets you send a message to a user, and get a simple response (yes/no or okay/cancel) in return. InputBox lets you ask a user to input some information. And you can design a whole UserForm as a custom dialog.We will use an InputBox to get a range from our user. There are two kinds of InputBox: regular InputBox that accepts text input from the user, and Application.InputBox, which lets you require a certain type of information from the user. Since we need a range, we ll use Application.InputBox. The required syntax is:Function InputBox(Prompt As String, [Title], [Default], _ [Left], [Top], [HelpFile], [HelpContextID], [Type])You supply a Prompt to tell the user what you need, and optionally a Title for the InputBox and a Default value. Left and Top position the InputBox but haven t worked in recent memory. HelpFile and HelpContextID are used if you have help content for the InputBox. Finally, Type describes the type of data you want; for our purposes, type 8 is used for a range.Here is a simple VBA procedure that uses Application.InputBox. It uses simple prompt, title, and default arguments, plus the 8 for range, and the in between arguments are left blank. When a range is returned, those cells are filled red. Dim UserRange As Range Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8) UserRange.Interior.Color = vbRedEnd SubWhen the code runs, here is how it looks. The default range is highlighted with a dark dashed outline.When another range is selected, its address appears in the InputBox, and the new range is highlighted a dark dashed outline.If we give up and hit cancel, we get a run time error. The statement calling InputBox is highlighted.The warning is fine, but we don t want to saddle our poor user with the run time error, so we will make a minor modification to the code. Actually, two modifications. First, we wrap the call to InputBox in On Error Resume Next and On Error GoTo 0. Then, if the user has canceled, UserRange will not have been assigned, so it is Nothing, and if this is the case, we ll bail out before trying to format a nonexistent range, which would lead to another error. On Error Resume Next Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8) On Error GoTo 0 If UserRange Is Nothing Then Exit Sub UserRange.Interior.Color = vbRedEnd SubExecution exits the sub above in two places, either Exit Sub if UserRange Is Nothing or End Sub at the end. If you have a bit of OCD like I do, you d prefer the code to always exit at one place, so I usually use this structure instead: On Error Resume Next Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8) On Error GoTo 0 If Not UserRange Is Nothing Then UserRange.Interior.Color = vbRed End IfEnd SubLet s punch up the code with a few text variables, and allow for a smart default range (or you can omit the default to leave out blank if that makes more sense). On Error Resume Next Set UserRange = Application.InputBox(Prompt, Title, Default, , , , , 8) On Error GoTo 0 If Not UserRange Is Nothing Then UserRange.Interior.Color = vbRed End IfEnd SubSo now that we know how to get a range from the user, let s put it to better use than just formatting a range to have a red background.In this example, we ll ask the user for a range containing series names, and we ll apply the range to the active chart s series one by one, until we run out of series in the chart or cells in the selected range.Since we re working on the active chart, the user must select a chart before running the code. We ll use this structure to make sure there is an active chart: If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected" Else ' do our thing here End If End SubOften I just bail out if there s no active chart, but it s nice to let the user know why nothing happened when they clicked a button.In the procedure below, we check first for an active chart, then we ask for a range containing series names. Then we make sure the range is a single row or column (not strictly necessary, but otherwise it gets complicated), and step through the series of the chart, using each cell in the range as the title of the series. If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected" Else Dim Prompt As String Prompt = "Select a range that contains series names for the active chart." Dim Title As String Title = "Select Series Names" Dim SeriesNameRange As Range On Error Resume Next Set SeriesNameRange = Application.InputBox(Prompt, Title, , , , , , 8) On Error GoTo 0 If Not SeriesNameRange Is Nothing Then If SeriesNameRange.Rows.Count = 1 Or SeriesNameRange.Columns.Count = 1 Then With ActiveChart Dim iSrs As Long For iSrs = 1 To .SeriesCollection.Count If iSrs = SeriesNameRange.Cells.Count Then .SeriesCollection(iSrs).Name = _ "=" SeriesNameRange.Cells(iSrs).Address(, , , True) End If Next End With Else MsgBox "Select a range with one row or one column", vbExclamation, _ "Must be One Row or Column" End If End If End If End SubHere is a chart. The highlighted data range shows that the Y values are in C5:E10, and the category labels (X values) in B5:B10, but no series names are highlighted. The series names Series1 etc. in the legend also indicate that no series names have been specified.Let s use the labels in C2:E2 for series names. Run the code: the InputBox is waiting for a range to be selected.Click OK and the selected range is now highlighted as the series names, and the chart legend shows these labels as series names.The above procedure can be used to assign series names (legend entries) for a chart that has none, or to replace the existing names (entries) in a chart that already has them.If there are not enough cells in the selected range, some series do not get new names assigned. If there are more cells than series, the code ignores the excess cells.In much the same way, we can ask a user for a range containing category labels (X axis values) to assign to a chart. If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected" Else Dim Prompt As String Prompt = "Select a range that contains category labels for the active chart." Dim Title As String Title = "Select Category Labels" Dim CategoryLabelRange As Range On Error Resume Next Set CategoryLabelRange = Application.InputBox(Prompt, Title, , , , , , 8) On Error GoTo 0 If Not CategoryLabelRange Is Nothing Then If CategoryLabelRange.Rows.Count = 1 Or CategoryLabelRange.Columns.Count = 1 Then With ActiveChart If CategoryLabelRange.Cells.Count = .SeriesCollection(1).Points.Count Then .SeriesCollection(1).XValues = CategoryLabelRange Else MsgBox "Select a range with the correct number of points.", vbExclamation, _ "Wrong Number of Points" End If End With Else MsgBox "Select a range with one row or one column", vbExclamation, _ "Must be One Row or Column" End If End If End If End SubThe above procedure can be used to assign category labels (X axis values) for a chart that has none, or to replace the existing labels in a chart that already has them. The selected range must have one row and multiple columns or one column and multiple rows. (In some cases, a chart can have category labels that use multiple rows/columns, but that is a lot more complicated than needed for this example.) If this condition is not met, the code tells the user to try again.Here is a chart. The highlighted data range shows that the Y values are in D3:F8, and the series names in D2:F2, but no categories are highlighted. The category labels 1, 2, 3, etc. along the X axis also indicate that no categories have been specified.Let’s use the labels in B3:B8 for categories. Run the code: theInputBoxis waiting for a range to be selected.Click OK and the selected range is now highlighted as the category range, and the horizontal axis displays these labels as categories.The above procedure can be used to assign categories (X values) for a chart that has none, or to replace the existing categories in a chart that already has them.If the selected range has the wrong number of cells for the points in a series, the procedure tells the user to select a range with the right number of labels.If we construct a chart from an ideally-shaped range, there is a predictable alignment of X values, Y values, and series names in the worksheet. In this chart, the series data is in columns. The Y values for a series are in a vertical range, and the series name is in the cell directly above these Y values. The category labels (X values) are in the column to the left of the Y values of the first series.In the next chart, the series data is in rows. The Y values for a series are in a horizontal range, and the series name is in the cell directly to the left these Y values. The X values are in the row above the Y values of the first series. If for some reason the chart was constructed without series names or without category labels, but the missing elements are in the right alignment with respect to the Y values, we can find the cells with the series names or category labels and assign them to the chart.The procedure below processes each series in the active chart. First it looks up the SERIES formula, and extracts its arguments into an array. The code finds the address of the Y values, which is in the third argument, and locates the associated range. Then it determines whether the data is by column or by row, identifies the cell with the series name, and uses this cell s address as the name of the series. ' just the arguments sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1) ' split into an array Dim vFmla As Variant vFmla = Split(sFmla, ",") ' Y values are the 3rd argument Dim sYVals As String sYVals = vFmla(LBound(vFmla) + 2) ' the Y value range Dim rYVals As Range Set rYVals = Range(sYVals) ' by row or column? If rYVals.Rows.Count 1 Then ' by column, so use cell above column of Y values Dim rName As Range Set rName = rYVals.Offset(-1).Resize(1) ElseIf rYVals.Columns.Count 1 Then ' by row, so use cell to left of Y values Set rName = rYVals.Offset(, -1).Resize(, 1) Else ' one cell only: who knows? Set rName = Nothing End If If Not rName Is Nothing Then srs.Name = "=" rName.Address(, , , True) End If Next End With End If End SubThe data range for the chart below is intact, but somehow, the series names were not associated with it.If the series in the chart already had names, this procedure overwrites those names with the names it finds in the worksheet.The procedure below processes the first series in the active chart. First it looks up the SERIES formula, and extracts its arguments into an array. The code finds the address of the Y values, which is in the third argument, and locates the associated range. Then it determines whether the data is by column or by row, identifies the parallel range with the categories (X values), and uses this range as the categories for the first series, and therefore, for the chart. ' just the arguments sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1) ' split into an array Dim vFmla As Variant vFmla = Split(sFmla, ",") ' Y values are the 3rd argument Dim sYVals As String sYVals = vFmla(LBound(vFmla) + 2) ' the Y value range Dim rYVals As Range Set rYVals = Range(sYVals) ' by row or column? If rYVals.Rows.Count 1 Then ' by column, so use column to left of Y values Dim rXVals As Range Set rXVals = rYVals.Offset(, -1) ElseIf rYVals.Columns.Count 1 Then ' by row, so use row above Y values Set rXVals = rYVals.Offset(-1) Else ' one cell only: who knows? Set rXVals = Nothing End If If Not rXVals Is Nothing Then srs.XValues = rXVals End If End With End If End SubThe data range for the chart below is intact, but somehow, the categories were not attached to it.Select the chart and run the procedure, and it applies the categories to the chart.If the series in the chart already had categories, this procedure overwrites those categories with the new labels it finds in the worksheet.For completeness, here is the routine that lets the user select the chart source data for the active chart. If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected" Else Dim Prompt As String Prompt = "Select a range that contains source data for the active chart." Dim Title As String Title = "Select Chart Source Data Range" Dim ChartSourceData As Range On Error Resume Next Set ChartSourceData = Application.InputBox(Prompt, Title, , , , , , 8) On Error GoTo 0 If Not ChartSourceData Is Nothing Then ' if a range was selected If ChartSourceData.Rows.Count = ChartSourceData.Columns.Count Then Dim DataOrientation As XlRowCol DataOrientation = xlColumns Else DataOrientation = xlRows End If ActiveChart.SetSourceData ChartSourceData, DataOrientation End If End If End SubIf you follow this blog closely, you may have heard of Jon s Toolbox, a new Excel add-in that I released recently. I ve already made a few changes to this utility, thanks to suggestions from readers like you.Jon s Toolbox already had a feature that made it easy to select a new source data range for a chart. While finishing up this article, I realized that these features should be included in the software.My colleague Frédéric LeGuen, an Excel MVP from France who runs the ExcelExercise web site and YouTube channel, asked me a tricky question last week. He wanted a way to visually track visitors to a museum, knowing how many arrived and left during a given hour. He tried a candlestick chart first, which is great for monitoring stock data, and a waterfall chart, which is great for tracking a quantity subject to up and down changes. The problem is that both of these charts show only up or down values in a given time period, while we want to show up and down for each time period.We have the following data, which lists the number of visitors entering (Entrée) and leaving (Sortie) during the hour, and the net visitors . The formula in cell D2, filled down the list, isWhat we want will look like this, but have blue arrows before each column showing visitors who have arrived and orange arrows after each column showing visitors who have left.There is an easy way to do this, using all columns and the sometimes tricky custom error bars. And there is a hard way, using XY series and tricky custom error bars. There s another hard way, which uses easier error bars but more complicated stacked columns that require a complicated data layout, so I think I ll avoid that one. Starting with the same initial data as above, I made a clustered column chart using time (Heure) as X values and Visitors as Y values. I ve changed the series overlap from the default of -27% to 0% and the gap width from the default of 219% to 100%. I don t know who thought up those defaults. I copied the Visitors data, selected the chart, and pasted. Two series with the same values and name, that s what I wanted. Really.Then I repeated the copy and paste, so I have the same data in the chart three times. This will give me the total visitors as the middle column, with a column before and after this where I can draw my arrows. I changed the colors of the bars, because I want the totals to be gray, and I want blue and orange to represent increases and decreases. And I ve renamed the outer bars Before and After.I added error bars to the Before and After series, using the little plus-sign icon floating beside the chart.I don t need to display the blue and orange columns anymore, so I ve formatted them with no fill, to be transparent.Time to format the error bars. Be sure to choose the No End Caps option, and then under Error Amount, select Custom, and then click the Specify Value button.I used the data in the Entrée and Sortie columns as custom values. When you click the Specify Value button, you get this tiny little dialog, with even tinier little edit boxes for selecting the range. The values are initially ={1} for both: change the positive value to ={0} and select the Entrée values for the negative.I ve been complaining about the tiny Custom Error Bars dialog for a long time. Here s what a more reasonably sized dialog would look like. See that, Microsoft? I did it in 5 minutes in MS Paint.Note, for the Sortie error bar values, they are off by one row, so you need to select the range starting in the second cell, so use $C$3:$C$11. Here s how the custom error bars look.Format the error bars with the blue and orange colors. Make the lines a bit thicker; I ve used 1.5 pt which is 2 pixels. Give the blue error bars a starting arrowhead, and the orange error bars an ending arrowhead.A little clean up. I deleted the legend, and adjusted the chart data range to leave out the meaningless blank at 18:00.You have some leeway in the spacing of the columns, by adjusting the gap width. In the chart above, the gap width is 100, meaning the space between the clusters is 100% as wide as a single column. The hidden columns that the arrows occupy are 100% of this width as well, so there is a reasonably wide space between arrows.If you want to decrease the spacing, you can decrease the gap. The chart below has the minimum gap width of 0%.If you had kept the default gap width of 219%, the columns and arrows would be rather far apart.You can set the gap width as high as 500%, which looks so ridiculous that I m not showing it..The easy approach used two column chart series to hang the error bars on. The hard approach uses two XY scatter chart series. I ve inserted a column of X values before the Y values (Visitors) in my data.I selected the time (Heure) and Visitors, and created a column chart. I ve recolored it gray, and applied overlap of 0% and gap width of 150%.I copied the X and Visitors data in columns D and E, selected the chart, and used Paste Special (Ribbon Home tab Paste dropdown Paste Special) to add the data to the chart as a new series in columns, series name in first row, categories in first column.The result is a second set of columns with the same values as the first. I ve recolored the columns blue and renamed them Entrée.I right-clicked on the new series and chose Change Series Chart Type from the pop-up menu. I changed the chart type for Entrée to Scatter (arrow 1), and I unchecked the Secondary Axis box beside it (arrow 2).The result is a combination chart with a set of gray columns and a set of blue markers, which I have reformatted into a large blue circle with no fill color.The X values I ve used, 1, 2, 3, etc., align with the bars at the first, second, third, etc. categories. What I need is to subtract a little bit from these X values to move the blue circles to the left of the columns, and add the same amount to these X values so another set of circles will sit to the right of the columns.So I ve added two more columns to my data, X-Before and X-After. I put a small value into cell D13. The formulas in the added columns are:Keeping my original X values in their own column and using a cell to hold my small amount to add and subtract make it easier to make adjustments later, to cells I can see, rather than to formulas where the amounts are hidden.Looking good. So I copied my X-After and Visitors data, and used Paste Special as before to add a third series to the chart. Since I ve already changed the previous series to a scatter type on the primary axis, Excel made my new series also a scatter type on the primary axis. I ve renamed the new series Sortie, and made it a large orange circle with no fill.So far so good, as long as Disney doesn t come after my for trademark infringement.So let s bring this puppy home. Add error bars to the Entrée and Sortie series, using the plus icon floating beside the chart.Let s format the error bars. Choose the No End Caps option, and under Error Amount, select Custom, and then click the Specify Value button.Use the data in the Entrée and Sortie columns as custom values for the error bars. You get this tiny little dialog I showed before, with microscopic edit boxes for selecting the range. The error bar values are initially ={1} for both: change the positive value to ={0} and for negative select the Entrée values.Isn t this Custom Error Bars dialog difficult to use? Here s a better sized dialog. See that, Microsoft? I fixed it in 5 minutes using MS Paint.Note that the Sortie error bar values are off by one row, so you need to select the range starting in the second cell, so use $C$3:$C$11. Here s how the custom error bars look.Format the error bars with the blue and orange colors and the lines thicker; I ve used 1.5 pt (2 pixels). Give the blue error bars a starting arrowhead, and the orange error bars an ending arrowhead. Finally a little clean-up. Delete the legend. Make all of the chart series one point shorter to remove the meaningless 18:00 category (stop at row 10 instead of row 11). Set the Y axis minimum to zero (the arrows between 12:00 and 13:00 actually drop slightly into negative territory, but that s not crucial to the story).The chart above used a gap width of 125% and a value of 0.333 to add or subtract from X to position the Entrée and Sortie arrows. You can adjust these together to change the chart s appearance. Below, for example, I ve applied a gap width of 75% and used an X increment of 0.41.There is a somewhat dated but still relevant tutorial on this blog that tells you all about Custom Error Bars in Excel Charts. Here is the cramped Custom Error Bars dialog in Excel 2007 and Windows 7, even narrower than Excel 365 s.

TAGS:Blog Tech Peltier 

<<< Thank you for your visit >>>

Peltier Tech Excel Charts and Programming Blog

Hot Websites