How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.
I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.
Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered
NO
Employee Name
Question
1
2
3
[Code]...
How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.
I want to convert Hex to Binary. I want the Binary in 8 different cells.The hex input would be lets say A1 I would want to convert that to eight different cells Bit7 G10, Bit6 G12, Bit5 G14, Bit4 G16, Bit3 E10, Bit2 E12, Bit1 E14 and Bit0 E16.
Then I want another Hex input on A2 and convert that to eight diferent cells.
I'm having some problems with graphing 'blank' cells for my thesis work.
I am filling in data still as I go, and I'm trying to set up my graphs as well so I can easily go in later and modify, fill in more data, or whatever. But all the cells I've formatted as coming back blank are labelled as zeros on the Excel graph. I tried putting in to return #N/A but then Excel is still graphing the cells as zeros. Frustrating. I really do not want to have to recopy over 1000 data points, find the zeros, and cut them out later, I'll have enough to do writing the dang thing. How to modify the formula to have it correctly skip the points when graphing (it's a line graph)?
This is the current formula I'm using. =IFERROR(AVERAGE(C41:Y41), "")
And I put in =IFERROR(AVERAGE(C41:Y41), "#N/A"), which shows the cell as #N/A (which, I'd much rather it be blank, as then I can read the data easier), but it is still graphing as 0.
I am trying to do a query against my database using MsQuery, but I am having a problem with it...
One of the tables in my database stores information as Binary (Bits) instead of actual text.... When I create the Query in MsQuery, the text is displayed as desired...but when I send it to Excel that column is not displayed..
Does anyone have any ideas on how I can get it to send the column to Excel?
See attached for what I mean. The Bits column shows in MSQuery, but not in Excel.
"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid the limitation, save the workbook in the Excel 2007 Binary workbook format."
The longest formula is 29 characters long. Even if I add the number of characters in every formula in the workbook, it only adds up to 1664.
I have a spread sheet that we input data on and produce a simple graph. When I started we plotted these graphs by hand on chart paper. When we started using spread sheets I learned how to graph the problem is these guys we have working in the field don't know how or care to learn how to make a simple xy graph.
Six months ago I started automating a workbook so our field guys could just plug in numbers and have the needed graph, I put check boxes to select the points to calculate the slope and intercept. I hard coded to plot to say line 21 then at the end of the test we always have a zero point that we don't plot so I put that on line 22 then have a macro to hide everything before I print it.
I think I would like to dynamically select the data for graphing but I'm not sure about the best way to do this. My first thought is to key on the x axis column with a loop and graph data from zero until the x looses value.
How well does Excel / VBA deal with data in a binary format? I am working on a Base64 decoder, which in basic terms means I will have 4 numbers, which I need to convert into their binary representations (6 bits each). I then need to combine these binary groups together, and then divide them up differently to give me 3 numbers (made of 8 bits each).I'm just wondering if anyone has any examples on how best to work with binary in Excel? Also if there are an gems of builtin library functions?
I have researched and read multiple threads related to graphing rolling averages per month. I've setup my own but I cannot get it to graph correctly. The x axis will be the days of the month ie. 1-31. The y axis is obviously the sales volume. The legend should be a line graph for each individual month sales. I want the graph to reflect the current month and previous 12 full months.
I used the file user rockycj started for another user in a different thread labeled, "Excel Line Chart - 12 month chart moving with dates ". It was very helpful and I'm trying to get this completed before my deadline. If anybody could take a look and let me know, I would greatly appreciate the help. I have attached the file for reference.
I have got a table of results with wards along the columns and in the rows reading left to right weekly dates e.g. 5/10/08 12/10/08 19/10/08 etc.
How can I get a graph to change the data it displays from a combo box eg. A1 is selected in the combo box and it shows a line graph of the Data for A1 over the weeks? There are 50 wards
I have a lot of outputs being spat out in csv files.
I need to get some of the data from set columns and copy this over into a master tracker. Columns are not sequencial and may need to copy change at a later date. Example attached.
Some data fields will have letters in them as well. Some are varying in length in terms of the amount of data within a cell. eg. "AB389238923589Y234HI" or just "A-GT6"
I don't know where to start VBA wise but it must be possible rather than open copy paste.
The tracker has a set name but will change quaterly.
The Output CSV files are new files with a number (no date) for titles.
I want to create the chart below. I have data that is coded with a binary flag (0 = red, 1 = blue) that is also ordered. For example, the data I would use to hypothetically create the left hand column below looks like this
I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....
Before Array Works! (Uses Cases)
Code: Sub GraphTest4() Dim LastRowGraph1 As Long Dim ws As Worksheet
I've set up a binary matrix, such that I have 421 unique headings repeated twice: once in each column and once in each row (i.e., in the matrix the headings for row 5 and column 5 will trace back to the same description in the column headings and in the row headings).
I need to fill in 1s (case satisfied) or 0s (not satisfied) in this 421X421 matrix. Where my problem lies is in finding the exact row-column cell combination in which I need to enter the 1s. Descriptions that belong to the same group or category will be marked by these 1s in the matrix. For example, if I have descriptions 5, 9, 13, and 17 belonging to the same group I need to enter a 1 in each possible combination of these numbers: {5,9} {5,13} {5,17} {9,13} {9,17} {13,17}. I need a quick formula that will compute all possible combinations for inputted descriptions AND will enter a 1 into the cells, which correspond to these combinations.
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form: | Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
I'm using Excel 2010 to link a table to a XML file on my server. I'd like to distribute the Excel file to a group of people and have it updated every time the XML file is updated on the server.
In Excel, I'm pulling the data using "Data", "From Web" and then I type in the path of the XML file.
Excel then builds a table with all the data but if the XML file is updated on the server, the data remains static, even if I click "Refresh" or "Refresh XML data" on the table.
It should be able to download new data including new columns to the table, if any.
I have an input spreadsheet to capture site addess details using Excel 2010
The Site Name is in Column A starting at row 8, each row is formatted with validation rules etc. but want only expose rows with data and one blank row to add next site.
So I needed code to unhide the next row without data - found what I needed in this thread
Have adjusted to my needs
[Code].....
It works by adding the next row but if I then delete the last rows Site Name I get a run time error with "Unable to set the Hidden property of the Range class" at follow code line.
I have problems copying data (from notepad, values are in scientific notation) into Excel 2010 worksheet. This problem only occurs with one of my laptops. I also tried this on my partner's laptop and no problem at all.
My new laptop (which I would like to use in the lab) has Windows 7 professional installed on it. I bought my laptop in Austria/Germany, so I changed the language from German to English. I then installed Microsoft Office 2010 on my laptop (which I am also using on my main laptop- without any issues). I have changed my default language to English UK.
The issue is as follows: Here is a small sample of the data from notepad
I select & copy the data from the notepad (also tried notepad++) and paste it into the Excel 2010 worksheet. This is what I get in Excel: 3.33E+02 1.51E+02 0.00E+00 0.00E+00 4.97E+04 3.85E+02 6.24E+05 1.81E+08
When I use another spreadsheet package (MagicPlot Student version), there are no problems. I have also installed Notepad++ and experience the same issue. So somehow Excel is increasing the value by 5 orders of magnitude. When I copy the values from another Excel sheet into a new Excel sheet, there are no problems. I have re-installed the Microsoft Office suite several times and the problem is still there. I can't re-install Windows 7 as I don't have the installation discs.
When I had Excel 2010 and now with Excel 2013, whenever I open a workbook, it opens and displays to the row AFTER the last row of data. (So, if my last row of data is 38205, it will open starting at row 38206...hence I see no data until I hit Ctrl+Home). I have not found anything about this on the internet and I cannot find a setting. I need Excel to open to A1 so I don't freak out every time I open a workbook and see no data.
My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.
The file was a PDF at first but I have since been able to convert to excel format.
The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.
The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.
The file looks like:
|A---------|B-------|C----------| 1.John-----|Doe-----|123 Main St| 2.New York-|NY------| 3.11100----|SSN----|Balance Owed|
I would like the data from multiple rows to continue on row 1 like this:
|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------| 1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|
I am no programmer in the least and have very little to no experience in pivot tables.
This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.
How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.
I have two spread sheets that I 'converted' into tables. I need to combine them into a single sheet. When I attempt to copy and paste from one to the other the formatting and formulas do not carry 'down' as they do when I manually enter a new line or record.
Is there a way to copy multiple rows of data from one tabled sheet to another tabled sheet?
