
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
My chart has gaps and I want to remove them. The gaps are from the zeros in my data table. I cannot eliminate those. I tried to type =NA() to show as an error so that the chart won't read it, but it still does.
Similar Excel Video Tutorials
Comparative Charts
 See Mr Excel and excelisfun create charts to compare data values: 1) Bar Chart 2) Stacked Column Chart 3) Trendline to predict future d ...
Helpful Excel Macros
Get Values from a Chart
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Similar Topics
HI, I am trying to create a chart that is using data with gaps. The x axis is wk numbers, so I cant hide rows. The gaps in the data are not empty cells as there is a formula in there. How do I prevent the chart from plotting these gaps as zero??
Can anyone help??
Hello,
I have a four charts that are using dynamic named ranges to plot the values. The data table that my chart uses may have data in it with gaps. When there are gaps in data, the chat plots funny.
I have attached an example illustrating my issue.
When there are gaps, the named range will plot the second block of data but not the first. Is there anyway I can adjust the formula for the named range to plot both without gaps on the chart? Or just overall make it look better, or plot better.
I have data series that may have gaps in them. For instance, this series is
typical:
1 yr 70
2 yr 87.5
3 yr 110
4 yr 120
5 yr 129
6 yr
7 yr
8 yr
9 yr
10 yr 162.5
For other series the gaps may appear in other places. I would like to chart
all of these (up to five series) on a single chart.
I can do this, but the gaps make the line "disappear" where there are gaps.
For instance, in the case above there is a nice like from 1 to 6, then a
single dot at 10. Is there some way to tell it to ignore the gap and draw
straight lines over them?
Maury
Plotting a stock bar chart and using weekdays only (no weekend date values)
as the xaxis (category labels) leavs med with gaps in the bar chart.
How do i plot the chart without the gaps?
Regards
And second problem I have is, that chart leaves gaps if there is empty cells.
I need these cell to be ignored instead. That chart don't show information from them. For example if there is no information about this year, then just ignore it, and show only years where is information.
Data for example:
.....200805 ......... 200804...........200803
971 349 188..........................953 285 324
611 323 736..........................612 510 025
747 189 300..........................703 691 200
How to ignore "200804" instead of gap?
As I know, it work's if there is only line chart, but i have chart with 2 Y axes, 1st presents column chart, 2nd  line chart.
Thx in advance.
And second problem I have is, that chart leaves gaps if there is empty cells.
I need these cell to be ignored instead. That chart don't show information from them. For example if there is no information about this year, then just ignore it, and show only years where is information.
Data for example:
.....200805...........200804...........200803
971 349 188..........................953 285 324
611 323 736..........................612 510 025
747 189 300..........................703 691 200
How to ignore "200804" instead of gap?
As I know, it work's if there is only line chart, but i have chart with 2 Y axes, 1st presents column chart, 2nd  line chart.
Thx in advance.
I'm trying to create a single 'line type' chart from two sets of data. The 'x' axis for both is the distance along carriageway, the 'y' data are values of skidding resistance. The problem is that the two sets of data are recorded at different locations on the carriageway and when I try to join the data sets there are gaps in the lines. e.g.
Distance (x) Data Set 1 'y1' Data Set 2 'y2'
100 2 2
125 1
150 2
175 3 2
200 2
225 3
250 1 1
And so on, is there a way to create a 'line type' chart without gaps in the lines where there are gaps in the data?
Hi,
I am trying to plot a time data with discontinuous time line for a dynamic chart.
Example:
Date Value
1/1 10
1/3 15
1/4 16
1/7 20 etc.
Upon creating the chart I see gaps for the dates for which values are not given. Remember there is no row for these dates at all. I would like to know how to remove gaps from my chart for these dates.
SI
Hi,
I rewrote this question to be a bit more clear.
On line charts, I know you can choose to prevent interpolation by using the Tools>Options>Chart tab optionwhich works when cells are empty. But I have cells with formulas that return #N/A. These cells don't plotwhich is greatbut I don't want the plotted line to interpolate the plots of data that come before and after the #N/A cells. I want gaps. Is there a way I can do this?
Thanks in advance,
Christiaan V
I have a chart that uses dates on the x axis ands stacked bar chart data in the data series.
I want the displayed chart to eliminate the weekends from the chart as the values in these cells are zero.
At present I have each week as a discreet data range, misisng out the weekend dates altogether from the selection but the chart still shows a gap between each range as if to account for the weekend.
How do I show only work days in one continuous display, i.e., without showing gaps on the chart??
Cheers
I have a chart that uses dates on the x axis ands stacked bar chart data in the data series.
I want the displayed chart to eliminate the weekends from the chart as the values in these cells are zero.
At present I have each week as a discreet data range, misisng out the weekend dates altogether from the selection but the chart still shows a gap between each range as if to account for the weekend.
How do I show only work days in one continuous display, i.e., without showing gaps on the chart??
Cheers
Hello Everyone!
I am having an issue with a chart (Line & Column)... The issue is that on the Column Chart with a Date Axis, there are gaps on days without data (Weekends for my data).... So if I change the axis from date to text, I can eliminate the gaps without a problem....
The problem then becomes the Axis Format... I would like to add gridlines corresponding to the 1st day of each month. While this is easy to while the axis is a date, however I have not figured out how to do it when the axis is text....
Is it possible to have a column/bar chart using a date axis without the weekend gaps where data does not exists, or properly format the axis with gridlines on the 1st of each month corresponding to the same spacing as the representative Line Chart?
I have attached an example of my issue.....
Thank you all in advance for your assistance...
JB
I have charts that I do monthly and a few months ago for some reason there is something that seems to be causing a 'problem' that isn't allowing the next month's chart to appear the same.
For some reason the first two come out how I want them, the dates with no information (when that employee wasn't working) are empty and are displayed with a gap. However, March for some reason doesn't show those empty days as gaps. Basically, for the 30 day period that's selected in the source data for the xaxis, I need all 30 days to be there which includes days the employee is off, they should just have no bar which creates two day gaps in the chart from week to week. I needto be able to get them to show as gaps again. I've checked everywhere in the axis settings and options and other areas and can't seem to find anything I can change to get the chart back to how the others are. I'm not sure why this started happenign because each month I do everything the same. I just copy the chart from the previous month, copy/paste, then change the source data.
Any thoughts? Thank you so much for any help you can offer. I've been trying to figure this out since March and have had ZERO luck
P.S. One thing I did notice is that when double clicking on the axis to go to 'Format Axis' the 'Scale' tab for the chart i'm having the problem with only has 3 selections, Number of Cats between tickmark labels, tickmarks, and a checkbox for reversing the order. On the charts that are fine, I get the 'min/max/baseunit/majorunit and minorunit' options. I think the problem can be fixed in this section but those options aren't on the other chart for some reason.
Hi everyone,
I am trying to create a "Gaps" program that cycles through ALL 13,983,816 combinations in a 6 from 49 Lotto and produces the following data.
I would like it to work out the Gaps between each of the balls for EACH combination in a 6 from 49 Lotto with no replacement.
For example, the combination 02 08 18 28 36 45 will have the Gaps of ...
Ball 1 numbered 02 & Ball 2 numbered 08 = 05 Gaps ( 03 04 05 06 07 )
Ball 2 numbered 08 & Ball 3 numbered 18 = 09 Gaps ( 19 10 11 12 13 14 15 16 17 )
Ball 3 numbered 18 & Ball 4 numbered 28 = 08 Gaps ( 20 21 22 23 24 25 26 27 )
Ball 4 numbered 28 & Ball 5 numbered 36 = 07 Gaps ( 29 30 31 32 33 34 35 )
Ball 5 numbered 36 & Ball 6 numbered 45 = 08 Gaps ( 37 38 39 40 41 42 43 44 )
... so the Gaps description for this particular combination will be 05 09 08 07 08.
The Gaps difference between ball 1 and ball 2 is really ball 2 minus ball 1  1 and so on.
I would like to have a Gaps description for each category and the Total combinations for each category associated with that list.
So extracting three combinations from the Loop as an example ...
Combination  02 09 16 25 38 45
Combination  04 15 19 36 37 49
Combination  09 15 28 39 46 47
... will give the answer ...
Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 12, 23 ,34 ,45 & 56 )
Gaps 10 03 16 00 11 = x combinations ( x could be 236,258 combinations with the exact same Gaps between the balls 12, 23, 34, 45 & 56 )
Gaps 05 12 10 06 00 = x combinations ( x could be 756,258 combinations with the exact same Gaps between the balls 12, 23, 34, 45 & 56 )
... etc.
It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations.
I have written the following which has nothing to do with what I am trying to achieve above, but for some reason it does not work.
It is supposed to give me a list of Gaps from 00 to 43 from ALL 13,983,816 combinations.
The list of Gaps should be in cells B3:B45 and the total combinatios for each Gap should be in cells C3:C45.
Could somebody please have a glance at it.
Code:
Option Explicit
Option Base 1
Private Sub Gaps()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim GapsTotal(43) As Long
Application.ScreenUpdating = False
For i = 1 To 43
GapsTotal(i) = 0
Next i
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
If B  A  1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If C  B  1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If D  C  1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If E  D  1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If F  E  1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If B  A  1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If C  B  1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If D  C  1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If E  D  1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If F  E  1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
... continued all the way down to ...
If B  A  1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If C  B  1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If D  C  1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If E  D  1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If F  E  1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If B  A  1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If C  B  1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If D  C  1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If E  D  1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If F  E  1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
Next F
Next E
Next D
Next C
Next B
Next A
Sheets("Gaps Data").Select
Range("B2").Value = "Gaps"
Range("B3").Value = "Gaps of 00"
Range("B4").Value = "Gaps of 01"
... continued all the way down to ...
Range("B44").Value = "Gaps of 42"
Range("B45").Value = "Gaps of 43"
Range("C2").Value = "Total"
Range("C3").Select
For i = 1 To 43
ActiveCell.Offset(i, 0).Value = GapsTotal(i)
Next i
Application.ScreenUpdating = True
End Sub
Any help will be greatly appreciated.
Thanks in Advance.
Dear All,
I have several thousand rows of data, with many gaps (see extract below). I need to compress the data set so all the gaps are removed (see example titled after).
Any ideas, how I might do this with a simple formula?
Thanks
Paul
Before
After
55.58
55.58
55.30
55.30
55.00
55.00
55.00
55.00
55.00
55.00
55.00
55.00
55.00
55.25
55.00
55.44
55.36
55.34
55.25
55.26
55.44
55.26
55.32
55.36
55.42
55.34
55.49
55.50
55.26
55.51
55.26
58.76
55.32
58.76
55.42
55.49
55.50
55.51
58.76
58.76
I have a list of data (Text) unfortunately there is gaps in the data e.g. Row 1 has data, row 2 doesn't, row 3 does, row 4 doesn't......I want to remove all the gaps. Any suggestions please?
Thanks
Hi,
I've got a set of monthly data. The data is filled in for JanApril and I have the year end figure. I used to have a formula that would fill in the gaps for me but can't remember what it was. I think it was something along the lines of Last numberfirst number/number of gaps but that doesn't work as I obviously just get the same number for each missing month. I need the months to show a linear progression.
Any ideas?
Thanks
Hi everyone,
I am trying to create a "Gaps" program that cycles through ALL 13,983,816 combinations in a 6 from 49 Lotto without replacement.
I would like it to work out the Gaps between each of the 6 balls and give it a category. EACH 6 number combination will have a category.
For example, the combination 02 08 18 28 36 45 will have the Gaps of ...
Ball 1 numbered 02 & Ball 2 numbered 08 = 05 Gaps ( 03 04 05 06 07 )
Ball 2 numbered 08 & Ball 3 numbered 18 = 09 Gaps ( 19 10 11 12 13 14 15 16 17 )
Ball 3 numbered 18 & Ball 4 numbered 28 = 08 Gaps ( 20 21 22 23 24 25 26 27 )
Ball 4 numbered 28 & Ball 5 numbered 36 = 07 Gaps ( 29 30 31 32 33 34 35 )
Ball 5 numbered 36 & Ball 6 numbered 45 = 08 Gaps ( 37 38 39 40 41 42 43 44 )
... so the Gaps category description for this particular combination will be 05 09 08 07 08.
The Gaps difference between ball 1 and ball 2 is really ball 2 minus ball 1  1 and so on.
Ideally, I would like to have a list containing each of the Gaps categories and the Total combinations associated with each of the Gaps category.
Obviously there will be many combinations associated with the same category, but I just want the total combinations for each unique category.
For example, taking a few combinations from the 13,983,816 combinations at random ...
Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00
Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01
Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02
Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03
Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04
Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05
Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06
Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02
Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02
Combination 19 22 23 24 28 31 = Gap category 02 00 00 03 02
Combination 29 32 33 34 38 41 = Gap category 02 00 00 03 02
Combination 31 34 35 36 40 43 = Gap category 02 00 00 03 02
... the category list and total combinations for the above will be ...
Category 00 00 00 00 00 = total combinations of 1 < One
Category 00 00 00 00 01 = total combinations of 1 < One
Category 00 00 00 00 02 = total combinations of 1 < One
Category 00 00 00 00 03 = total combinations of 1 < One
Category 00 00 00 00 04 = total combinations of 1 < One
Category 00 00 00 00 05 = total combinations of 1 < One
Category 00 00 00 00 06 = total combinations of 1 < One
Category 09 00 01 03 02 = total combinations of 2 < TWO
Category 02 00 00 03 02 = total combinations of 3 < THREE
There could be any number of combinations with the exact same Gaps between Balls 1 & 2, Balls 2 & 3, Balls 3 & 4, Balls 4 & 5 and Balls 5 & 6.
I would also like the total combinations at the end please, which of course should add upto 13,983,816 combinations.
I hope this is clear enough.
I though a start for the code could be something like this :
Code:
Option Explicit
Option Base 1
Private Sub Gaps()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim GapsCategory As Long
Dim GapsTotal As Long
Dim GapsTotalCombinations As Long
Application.ScreenUpdating = False
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
' CODE maybe goes here.
Next F
Next E
Next D
Next C
Next B
Next A
Sheets("Gaps Data").Select
Range("B2").Value = "Gaps Category"
Range("C2").Value = "Total Combinations"
Application.ScreenUpdating = True
End Sub
Thanks in Advance.
In Excel 2003, I created a table of blood pressure readings beginning in November, 2008 through today (Col A = date; B= Sys; C= Dyas; D= HR). I missed some days, so there are gaps. When I try to chart the trends over time, the Wizard creates a chart that begins in November, 2008 but continues through the entire 2009... which compresses the relevant data and leaves a lot of blank space. So far, the "Help" feature hasn't been much help.
All,
I have a series of columns with values inside. Out of 20 rows per column, anywhere from 1 to all 20 may contain values. There are gaps between some of these values. Is there a simple way to create an adjacent set of columns with the same values, but remove the gaps?
For example:
1
1
2
1
3
1
2
Becomes
1
1
2
1
3
1
2
Any ideas?
Cheers
John
(I previously posted this as a reply to an earlier post but I'm hoping that
someone new might see this.)
The array formula was extremely useful and I am trying to edit it for the
following:
Gaps sheet is where account and meter info is kept, along with periods
(months) that have already been reported.
Main sheet is where new data is kept. I want to delete the rows where those
periods (months) have already been reported.
Gaps!A3:A10 = account #
Gaps!C3:C10 = meter #
Gaps!E3:E10 = reported periods
main sheet, cell A2 = account #
main sheet, cell F2 = meter #
main sheet, cell P2 = period that this billing data is for
=INDEX(Gaps!$E$3:$P$10,MATCH(1,(Gaps!$A$3:$A$10=A2)*(Gaps!$D$3:$D$10=F2),0))
I would like to mark rows that should be deleted with "Yes" (must be
deleted) or "No" (stays).
Should I be using a different function or are my operators incorrect? What
am I missing? Please let me know if I need to give further clarification.
HELP!!!
Thank you all for any suggestions.
I have a formula which returns "" if there is an error.
The "" shows as zero on my line chart. I have selected "show empty cells as gaps" in the 'select cells" radio button.
If I remove the ISERROR function, the error values do not show as zero but there is a line between the data points before and after.
I want to show them as gaps.
My formula:
=IF(ISERROR(VLOOKUP(+D1297&"  "&$A1298,'monthly raw data'!$D$1:$L$10000,VLOOKUP($B1298,Sheet1!$A$1:$B$10000,2,FALSE),FALSE)),"",VLOOKUP(+D1297&"  "&$A1298,'monthly raw data'!$D$1:$L$10000,VLOOKUP($B1298,Sheet1!$A$1:$B$10000,2,FALSE),FALSE))
Thanks for any help
Jim
I have a column of data with gaps every third row (for plotting a line with gaps). I need to create a dynamic range for the column of data. I can mess with a formula using "count" and then add 50% but I thought I could be more clever and take advantage of the fact that the bottom value is always the maximum value.
Any idea how to do this?
Thanks!
I have an request that comes up once an a while in my work environment. On occasion I will receive an Excel spreadsheet with a list of documents. The spreadsheet will have a beginning and ending document number in corresponding columns. I usually need to do two things with this information.
1. Identify any gaps in the number sequence
2. Consolidate the number series where no gaps exist.
For example:
I will get a spreadsheet that contains this information:
Begdoc# Enddoc#
200 240
241 266
270 288
289 300
What I want to do is to be able to ID the gap between 266 and 270, and combine the numbers that do not have gaps so it looks like this:
Begdoc# Enddoc#
200 266
270 300
I would like to be able to do this in two steps. Somtimes I just need to identify the gaps and somtimes I need to combine the consecutive number series.
Thanks in advance!
I have 3 columns of data. Date, In time, Out time. The data represents the utilization of a facility. I need to know when the facility was not being used. I need to see where the gaps are. Is there a way to graph this with a timeline that shows gaps when the facility was not in use? That would be ideal. Any other suggestions are greatly appreciated.
Thanks,
Mike

