How To Create Scattered Plot With Repeating Values In The Same Group
Mar 20, 2014
I have data that is something like this:
Group Assets
1 200
1 300
2 150
3 50
1 500
3 100
I want to create a scattered plot charts based on the data above but I cant get it to work for some reason. I want to have the "Group" on the x-axis and the "Assets" on the y-axis. I don't want to combine the same group number (for example to have group 1 with total assets of 1000, I want to keep it as 200 300 and 500 because I want to show which group is more represented than other group.). I have tried sorting the data by group and also with out sorting but the data doesn't plot at all (everything looks clustered at the 0 level on the x-axis instead of the respective asset level.
View 1 Replies
ADVERTISEMENT
Apr 20, 2006
sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
View 9 Replies
View Related
Apr 17, 2014
creating a Macro to automatically group rows that has no numerical value, for example:
1. 13.4
2. 0
3. 0
4. 33.3
5. 0
and so on...
So for this exercise I need excel to automatically group row 2, 3 5 with one click, and leave other rows with actual numbers visible on the sheet.
View 2 Replies
View Related
Jan 11, 2014
I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days. My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.
Detail: (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me) I open Excel 2010, click File, Open, and select the file that I want to parse. It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width. I select File Origin: MS-DOS (PC-8). On step 2 of the wizard, I create column break lines to place all dates in the far left column. The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps. I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.
In step 3, I format the first column as "date (DMY)". The columns with the dashes I select "do not import". Everything else is "general". I click "finish", and the resultant workbook is attached, called "import".
Now, as to what I want to do: I want to display the "temperatures" as a graph vs a date/time axis. The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines. (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if can be done automatically.)
View 3 Replies
View Related
Jun 3, 2014
I have a pareto for a manufacturing facility that has a list of nonconformists and their occurrences in columns K and L. My issue is since it is a running total for the year at certain times the values are 0 for the number of occurrences. This calculates fine, but the graph is very large and the failures are all located on one side instead of being evenly distributed. I am trying to get it to only plot for what has occurred and not have a bunch of blank values. To make it a little more complex I'm trying to avoid macros.. I can get them to show up blank just can't get the bar graph to resize itself to fill the chart.
View 3 Replies
View Related
Jul 9, 2014
I would like to make a histogram using excel. I have successfully made the bins and frequency functions. Once I want to add the x values, I get an error. There are many examples online, but I can't get it to work.
[Code] .....
View 1 Replies
View Related
Mar 15, 2012
I have data that starts from say, 17:50 hours. However, I would like the chart to start from 00:00 to 00:00 so it is a full 24-hour period. So up to 00:00 to 17:50 hours, it would be blank then the data will be plotted from 17:50. How do I do this? If I select the existing data from 17:50 and ask Excel to create a chart, the x axis starts from 17:50 (not 00:00).
View 4 Replies
View Related
Jan 5, 2013
Okay, so this is what I am using:
[Code] .......
It is finding the name for a value from a table of values, the only problem is that there are more than one names which correspond to the same value. It doesnt matter to me which name it retrieves, but it lists the same name over and over again, where i'd prefer if it used a different name each time. Any way to keep it from using the same one?
Example1 (pcb).xls
View 12 Replies
View Related
Apr 18, 2014
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
View 1 Replies
View Related
Apr 8, 2012
I am importing a text file and plotting some of its contents versus time. The file provides the end time and the step interval. I know that in Matlab you can define a set of values with the start, end, and interval, but I'm having a hard time figuring out how to do this with VBA. I have toyed with fill series, but no luck there.
Specifically, the file I'm reading in (a relay event file with voltages and currents) gives the maximum cycles as 15 and the samples per cycle as 16. Therefore, for each voltage or current sample, I need the time to show as 1/16, 2/16, 3/16, ..., all the way to 15 cycles. I have tried to manually do this and record the macro, but after I put in a few x/16s, when I try to extend the formulas down the rest of the cells, instead of continuing the trend (4/16, 5/16, 6/16, etc.), it repeats what I put in first.
View 1 Replies
View Related
Oct 20, 2006
one of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.
View 2 Replies
View Related
Oct 29, 2009
I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.
I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.
e.g.
444588 Prime Wages/salaries
This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value
Another example would be
444586 Ohd Wages & salaries
This code appears under Production Overhead, Site Overhead, Sales overhead and Admin Overhead this would only ever return the production overhead value only
Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.
Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.
View 14 Replies
View Related
Jun 27, 2009
I'm having trouble delivering a MAX value within a range.
In column A I have a running list of numbers, in column B I have a yes or no answer and in column C I'm trying to write a formula that will deliver a MAX value from the A column inbetween occurancies of the Yes and No values from the B column.
I'm trying the SUMIF function but it seems to be proving problematic.
This is what it would look like.
A B C
1 Y 1
2 Y 2
3 Y 3
2 Y 3
1 Y 3
2 N 2
7 N 7
6 N 7
5 N 7
3 N 7
2 N 7
6 Y 6
9 Y 9
3 Y 9
It is almost as if I'm trying to get the formula in the C column only find the latest MAX value from where it currently is to when the last changeover happened from Y to N.
I can't find the Max dependent on a yes or no answer, but I'm struggling to make it only look at the last batch of Yes's or No's!
View 9 Replies
View Related
Feb 15, 2010
It has several steps and i will now just explain the least complicated one, and hopefully once i get this done i will figure out the rest myself. If not then i will need some more help
I have 500 entries (names) in column A and each entry has a coresponding category in Column B (Large, Medium, Small).
I want to see how many entries are there for each category, but i need to count the names which appear in the list only once. If the name is entered more than once then i dont need to count it.
I hope i made it clear as i am not able to provide excel table as an example do to security settings.
I am usinng Excel`03
View 9 Replies
View Related
Sep 24, 2006
How can I modify the codes below so that the results in the listbox would reflect only the actual data? Some previous information stays in the listbox making the search inaccurate. I have tried to clear the listbox first before querying another but results are the same.
On the first loop, the queried items are accurately listed in Worksheets("Search"). For example, if the data being searched on has only one result, entries in this sheet would only show one. But when it is shown in the listbox, it at most times show previously queried information.
Also, how can I dynamically adjust the array so that no vertical scrollbar would appear if the queried results fit perfectly in the listbox? The array below would automatically assign 10 rows in the listbox even if the information shown consumes only less than 10 rows.
Option Explicit
Dim MyArray(10, 5)
Public MyData As Range, c As Range
Sub FindCaseNo()
Application.ScreenUpdating = False
Dim FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG As String
Dim fndH, fndI, fndJ, fndK, fndL, fndM, fndN, fndO, fndP, fndQ, fndR, fndS As String
Dim head1, head2, head3, head4, head5 As String ' heading s for list
Dim i, intFind, intCount As Integer
i = 1
View 6 Replies
View Related
Dec 4, 2012
I am having a problem when making a XY-plot diagram because I want text values on the x-axis instead of numbers - but that doesn't seem to be possible to get with this type of diagram?
View 2 Replies
View Related
Dec 8, 2006
I wanted to ask if you know any charting software which can give weight to elements on a chart when two or more variables overlap? Ideally the color intensity of, say, circles should increase from pale blue to vivid blue depending on the number of the data items which are operlapping at one point. I include a sample chart which should give you a general idea of the chart I want to make and the data set from which it was made.
View 5 Replies
View Related
Sep 26, 2007
I have over 5000 cells of data. I want to take 4 data points at a time, average them then plot them on a dynamic chart. I know how to do the dynamic chart but how can I take the average of the the first 4 numbers store it in a different cell, then the next 4 and so on...
View 4 Replies
View Related
Nov 25, 2007
working on grouping coordinates because he we wants to simplify data. But he works manually and it eats his time. He has some sheets with data. I suppose there are 7 sheets. Each sheet has coordinates shown on example below. Also there are intervals between pairs of columns, so there will be column A with B, interval of two columns C & D, column E & F and interval of two columns G & H and so on (altogether about 10 columns of coordinates per sheet). Every coordinate begins with argument '1' and increasing gradually by 1 (it is column A). Second column shows values of arguments. But some are repeating, some not. I would like to double these coordinates, where in one argument is only one value (for example these are row 1. and 11.). If there are two the same values I would keep them. But if there will be more values than two, for example row 5. - 8., I would delete row 6. and 7. from the middle. But border coordinates should be kept (row 5. and 8.) and split by moving up lower coordinate to it with rest coordinates. But I want do it separately for every two columns which contains these coordinates, so if I delete coordinates from columns A and B and split boundary coordinates I don't want do it for columns E&F, I&J...
Examle:
Before:
A B
1. 1 1
2. 2 2
3. 3 2
4. 4 2
5. 5 3
6. 6 3
7. 7 3
8. 8 3
9. 9 4
10. 10 4
11. 11 5
12. 12 6
13. 13 6
14. 14 6
15. 15 7
16. 16 7
.. .. ..
After:
A B
1. 1 1
2. 1 1
3. 2 2
4. 4 2
5. 5 3
6. 8 3
7. 9 4
8. 10 4
9. 11 5
10. 11 5
11. 12 6
12. 14 6
13. 15 7
14. 16 7
.. .. ..
View 9 Replies
View Related
Jun 29, 2014
I'm using Office/Excel 2004 on a Mac Mini duo running OS10.6.8. I upgraded to Office/Excel 2011 but I find the program so horrible I had to switch back to 2004.
The problem: On one chart, the Y-axis label values have moved from outside the left-hand axis to _inside_ the right-hand axis and within the plot area. I can't figure out how to move the labels back to the right position.
View 5 Replies
View Related
May 22, 2014
I have 1 column of pasted values that basically look like this
c
1:a
2:b
3:c
1:d
2:e
3:f etc...
And they need to look like this:
c1 c2 c3
a b c
d e f
I know how to separate the first column into two columns by separating the values by the colon. But how do I rearrange the values as above and combine all "1"s in c1 so it becomes the headers and the column is populated only with the values? The table is too big to do manually (thousands of rows).
View 1 Replies
View Related
Oct 23, 2009
Matching Multiple repeating values in one column with another.
I have a three columns of data that I need to map the requird Ids in Col A against multiple repeats in Col C. AS per data below ....
View 6 Replies
View Related
Feb 16, 2009
I have three colums of data.
First represet company , second colum represent data1 ( x- axis) and third represet data2 ( y-axis).
I want to plot x-y graph with the company name
Eg.
McDon | 10 | 20
In the above case i want to show a round circle or dot which will be 10 right on x axis and 20 up on y axis and near the dot i want to show McDon as company name.
See that attachement... Graph is coming well.. but i am not able to add the company name to the dots.. or pot of x and y intersection
View 6 Replies
View Related
Sep 13, 2012
I am using the developer tools and have created a set of radio buttons that work fine.
On the same worksheet and tab I have created a second set of radio buttons but all the radio buttons are grouped as the same set.
How do I define the second group as a second group?
View 2 Replies
View Related
Sep 6, 2006
I have a chart that shows up to a list of 28 people and the number of sales for that day. I'm try to make the chart only show the names and number of salesperson that are not = to null. This is what the chart has for values right now "=' Nest Average'!$C$6:$C$33". How can I make it so that it only shows those cells if not = to null.
View 2 Replies
View Related
Apr 6, 2012
I have a column that has subcategories, and I would like to create a new column that would group those subcategories into categories.
Is there a formula that once it detects a certain word in the subcategory should give a certain category name that I choose?
View 1 Replies
View Related
Jan 17, 2008
I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.
The columns are as follows:
Table
Joined Tables
Column Name
Column Alias
Column Description
Column Data Type
Column Length
From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.
View 9 Replies
View Related
Mar 9, 2014
I need to combine the time scattered in different columns and align them daywise.
View 3 Replies
View Related
Mar 11, 2009
I have just inherited an Excel spreadhseet that displays job titles and what types of documents each one should be trained on. What I am trying to achieve is a formula, or something to that sort, that will look at each job individually and tell me only those documents that they need to be trained on and list it on a seperate sheet.
If you look at the attached document you will see that the first 2 columns contain a document number and description. Across the top you see each of our job descriptions. In the middle you see an X that indicates that person should be trained on that document.
So once more, I would like to take each Job Description (seperately) and list only the documents that have an "x" in their column on another sheet. I already have the sheets created, I just need to know if there is some kind of formula or macro that can do this for me.
View 4 Replies
View Related
Feb 4, 2013
I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
Example of data that needs sorting.xlsx
View 1 Replies
View Related
