remove gaps for missing values in my column chart. I have tried to adjust series overlap and gap width, but the missing values are still showing as gaps. I have attached the sheet
View 1 RepliesHow can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies View RelatedI get given a csv file on a monthly basis which contains consumption data per day for the specified period. This sounds simple but on occasion (more often than not) the data has missing days. This can cause me problem later on in my analysis.
I can happily total the monthly consumption using the date and month text. What i want to do however is to sort the csv file into daily consumption and highlight the missing days i.e. have a range of the days in the month and allocate the daily data to the correct date. I currently do this manually but know that there must be a better, automated approach... searching for matching dates for example?
In my head i'm thinking the following approach but lack the coding skills to do it.
1. Define the start and end dates. Perhaps count the number of days between the two dates and autofill the start date down the appropriate number of days in column A?
2. Paste the csv file into a different sheet and, starting from the top, cut and paste the csv data to the correct date created in step 1. Do this for each row based on the csv data.
Instead of treating cells with a blank or a text value as zero in a line graph, how can I create a gap in the line?
View 6 Replies View RelatedThe solution can be either in VBA or conditional formatting, if possible.I have product names on column A and weeks as from column B where I have the quantity sold. So, every week I'll have an additional column.
A B C D E ...
Product Week1 Week2 Week3 Week4...
What I need:
If the cell is filled, highlight it in green.
If the gap (empty cells) between weeks is =1, highlight it in yellow
If the gap (empty cells) between weeks is >1 but <2, highlight it in orange
If the gap (empty cells) between weeks is >2, highlight it in red
The attached example better illustrates the needs : Example.xlsx
I have a 5x5 table which is filled by random numbers ranging from 1 to 99. I need to make a function that shows me all the numbers that are missing between 1-99.
View 1 Replies View Relatedi have two chart A and chart B from ,chart A got nine repeting numbers and Chart B will show what is the number not in chart A or is outside from this number 0.1.2.3.4.5.6.7.8.9. i want auto generate to chart B.
View 2 Replies View RelatedI am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
I am facing a problem importing Excel data into a finicky graphics program. This program can't accept strings and numbers in the same column. Hence, strings such as #NA, or Excel's standard "" can't be used where there are missing values, as the entire column is then ignored.
But, I would like to provision for future data below the rows that currently exist.
Let's say I am trying to read data from Sheet1 of a workbook. The rows in Sheet1 contain formulas referencing other sheets (e.g. Sheet2!C1-Sheet2!C2). So, if Sheet2 doesn't have data in a certain row, Excel would show 0 in the formula on Sheet1. That would mess up my graph. If I use any other string or "" using a If statement for missing data, then the graphics program doesn't work.
Is there any way say, using dynamic ranges or similar, to populate blank cells in Sheet1 as data arrive in Sheet2, without a pre-existing formula being present in these Sheet1 cells? I can imagine that VBA macros will easily do this (e.g. the thread on Autofill below). But, is this possible without having to trigger a macro as and when additional data arrive?
I have some value sorted numerically (according to ID column) but there are some missing values. In a new column (Desired value column), I would like to return the values (according to ID column) and add missing values (according to Order column) as zero.
View 2 Replies View RelatedI have a list of records that for which I cannot find values for. Essentially I have a dataset that I need to match to other information but for some records this 'other information' is missing.
1. I have a list of records with missing information
2. I have a folder with a large amount of sub-directories
3. I have many DBF files inside of these sub-directories with X columns and x rows
4. Somewhere within some row of some column is a value that links to my list of records
5. I need to search for my value in, say, A2 and return the file name and directory in which it lies
6. It can be text or number
is this clear?
i'm trying to write a sheet that records weight from a date and user input
trouble is people are likely to miss days out and i want to fill in the missing dates weight based on actual inputs from other days. if i make it easier and say it like this column A is the weight, column b is the number of days gone
as an example i've made it straight forward but left 4 values out
A1616B161
A1515B152
A14B143
A1313B134
A1212B125
A1111B116
A10B107.....................
I am conducting data analysis on a large data set. Missing values are coded as NA. Likewise, when I perform a calculation on a NA value, I receive the # DIV/0! error.
I want to know how I can automatically change NA to the integer 0 in a large data set so when I perform a calculation on the NA value, I will receive 0 instead of #DIV/0!.
The formula works well 50% of the time, but randomly skips over some values. I have attached a sample of what I'm working on.
Extracting example.xlsx
There will be thousands of questions similar, but think there will be a limit of 4 numbers to be taken out.
I have a chart which has two labels at the bottom under the x axis. Is there any way to remove these without removing the data?
View 9 Replies View RelatedI have a pie chart that shows numerical values in dollars and I'm looking for a macro that will delete the $0 data labels. I found this code from Jon Peltier (great website by the way) but I can't seem to get it to work on my pie chart. No errors, just doesn't do anything:
Sub CleanUpActiveChartLabels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
ActiveSheet.ChartObjects("MainChart").Activate
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End Sub
I have two list of names, mostly duplicates. List in column B has about 30 more names than the list in Column A and I need to identify which names are in column B that are not in column A. Names are in exact same format since they were pulled from the same data base.
View 1 Replies View RelatedMonthly I am sent a spreadsheet detailing cost centre approval ranges for our management team. Unfortunately, the data provided shows a low range and high range in two different columns. Is their a formula to automatically generate the values for the missing numbers
For example
columns A
1001
&
columns B 1007
Can excel automatically generate the 1002, 1003, 1004, 1005 & 1006
I have a list of 9 values ranging from 1 to 9 generated by formulas in column A. they can only be "1,2,3,4,5,6,7,8, or 9" and can be repetitive. Is there a formula i can use (not macro please) that will show me what values are missing from the list?
Example
A B
1
4
5
1
2
1
8
7
1
*Note there are 9 values in column A in any order. In this scenario the answer i would like shown is "3,6,9".
I have a list of products and would like to find what are the missing products which aren't ordered yet from the customer.
As the example: let's assume numbers from 1 to 9.
And I have some numbers, like this: 2, 4, 6, 7. What is the formula which can copy automatically the missing one into one cell?
See the attachment : howtodothis.xls
I know, how to copy multiple cell into one cell (like A4&", "&", "A5 etc), but how to find the missing numbers?
I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.
I attach an example : NameCheck.xlsx
The code below returns sytax error. How do I fix that and how do I simplify and compress code? Private Sub CommandButton1_Click()
View 5 Replies View RelatedIf seen a couple of examples but for whatever reason it doesn't work on my sheet. (Excel 2010 32 bit) I don't understand how to highlight the entire column to see this particular value is not in this list. I have two lists, I want to see what data am I missing In list 2 that I have in list one. Basically all I am trying to do is see what values are not in the second list but are in the first list so I can later add them to the second list. (Also the 2nd list is longer than the first if that makes a difference.)
View 3 Replies View RelatedI have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
In column A, I have numbers from 1000 to 8600.
I want to identify (in a separate worksheet is fine) what numbers are missing inside the ranges 2500-2599, 3100-3399 & 3700-3799.
I have created a pie chart showing top 10 best selling categories. However, on this pie chart, I want %age of overall sales to be shown.
At the moment say I put in the following
Catalogue 1 100
Catalogue 2 98
Catalogue 3 92
Catalogue 4 85
Catalogue 5 84
Catalogue 6 75
[Code]....
I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:
Catalogue 11 60
Catalogue 12 58
Catalogue 13 57
Catalogue 14 57
Catalogue 15 56
So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.
I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.
I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****
Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?
BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.
I want to add a control under Chart on the menu bar. This line of code errors with "Invalid Procedure Call or Argument"
With Application. CommandBars("Worksheet menu bar").Controls("Chart")
Change "Chart" to "File" or "Edit" or "Tools" and it's fine.
The Chart item only appears when a chart is selected, but it errors even when a chart on the worksheet is selected.
I have a problem when Im trying to calculate averages in my pivot table. I dont know how to handle my missing values. If I leave them blank I cant get the average at all because the values end up under the dates instead of in a column next to the dates where I want them to be. If I write 0 it uses it as a value, eg 2+2+0 and i get 1,33 instead of 2. Also the zero could be a value for some of the parameters, so it's no good.
I am new to pivot tables.
i had a worksheet in which i had a column Named Cheque No. i want to print the missing cheque no's in a series of cheque no's.The logic here is that there will be different series of cheque nos for different transactions.Just check out my worksheet.
View 9 Replies View Related