I need to create some trend analysis for Vehicle sales. I have attached a file with sample data.
There are few problems i am facing like description has all the vehicle details which are not in any particular order, even if some how i manage to separate these then too many variables are not letting me come to any conclusion for example make, model, mileage, year of registration color of vehicle, auction sold, sale price, cap (bench mark for pricing).
what i need to produce is which auction sells certain vehicles best or which color of certain model sells best.
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
I have an excel question on how to create a table that will be able to compare actual results with our estimated results.
Basically, the column I have in blue in the attached excel file contains our estimates. Once the actual numbers come out, we will replace those numbers with actual numbers.
I would like to create a table that will automatically show the magnitude of the impact of the actual numbers vs our results -- basically how much each item added or subtracted from EPS.
The items I would like to show the impact of are: (1) The tax rate -- how much the actual tax rate impacted EPS vs. our estimate. For example - if it was a lower tax rate there may be a $0.03 benefit. (2) interest expense line - more/less interest..what the impact of that is. (3) non-recurring items (4) Share count - a lower share count or a higher share count than we anticipated - what the effect on EPS is.
For each item though, every other item should be held constant. So if looking at tax rate -- then use the actual interest expense and the actual shares.. and if looking at shares...compare that to our estimate but use the actual tax rate reported and the actual interest expense....
There should then be a sum total of these items +/- impact to EPS.
JAN FEB MARCH APRIL etc Shop 1 10 12 14 20 Shop 2 8 7 6 2 Shop 3 10 10 10 10 Shop 4 20 30 40 50
Although i can manually look at the sales figures and work out if they are buying less or more products i would like a a more automatic way of finding out if customers sales are going down on the up or staying constant. Does anymore have any ideas on how best to acheive this.
Is it easier to use the Solver Add-in than to create your own data analysis code? We are planning to design a scheduler for around 12 technicians in our contact center (doing phone support).
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code: Sub Auto_Open() ' Dim sht As Worksheet If Range("A1") = 1 Then
Below are the weekly closing prices for a stock market index. I'm looking to use conditional formatting to highlight in green "buy" signals and in red "sell" signals.
Buy Signal: If the index rises 4% above any previous low point.
Sell Signal: If the index falls 4% from any previous high point.
DateAdj Close 3/10/201472.16 3/3/201473.55 2/24/201472.83 2/18/201471.92 2/10/201471.43
[code].....
In the above example a Buy Signal is generated on 1/13/2014 because the market closes 4% above the low of 68.24 on 12/9/2013.
Here's my problem: I need to keep two values subject to two upper limits (i.e: value 1 < x, value 2 < y).
These two values are equations that are dependant on 3 variables. The relationship between these variables are not linear (i.e.: can't just plot a graph and mark where the lines cross).
Is there any computational method using Excel I can use that will give me the LOWEST value for the three variables in order for my target values to be under their upper limits?
We are running Office 2010 part of Office Professional Plus 1010 (corporate install). We have added both the Analysis Toolpak and Analysis Toolpak - VBA to the add-Ins and have the button on the toolbar, however when we click on the button, we don't get the popup window allowing the user to pick the analysis tool they want to use. Have tried this on a number of PCs (we all run the same version) and it doesn't work on any of them.
it's possible to create new fields (variables/columns) on the fly (without adding them to the original data table).
For this example, assume I have a column for: job code estimated hours job code actual hours total estimated hours total actual hours
Excel has made it easy to run averages or sums for any of those columns, but what if I want to see what the average proportion for job code estimated hours/ total estimated hours? Is there an easy way to do it with pivot tables without adding a column to my database? I'm working in Excel 2007, my data table size is approximately 100 columns by 200 rows.
I created a macro by using the macro recorder. It creates some columns and formulas and does some formatting, then it is to take the data and create some pivot tables.
for some reason it gets stuck on the first pivot table. It creates it, but it wont add the first field to the table.
I have recorded a Macro to create Pivot Tables, I would like this to run in any workbook. The problem is that every time a Pivot Table is generated Excel alters the number of the table(Pivot Table options). This then does not match that in the Macro as below("PivotTable1").
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R37C6").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("GAME") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("LANGUAGE") .Orientation = xlRowField .Position = 2
ive got 1 sheet,with 2 printable pages in it. 1 being the summery page and 1 being the inventory page.
what i would like to be able to do is. on the summery bit ive got a code in A22 and a number in G22 to save me time i need to create a list in the inventory bit starting at A62
im hoping it will look somthing like this
A62 = what ever the code is ( JR269 ) in A22 A63 = same code and so on until i have 26 in a list, 26 being G22
I have 2 worksheets which have a table on each. I would like to create a list using data from both tables. The list I am looking to create is for an upload fom an excel planning sheet into an ERP system and requires certain data fields and a certain format. Both table have the same amount of fields and the dates across the top. On worksheet one there are material numbers on the left and dates across the top. In the list I require I would need the dates down the left in the first column, next the material number, next the quantity from table one and then the last column I would require the value from the second worksheet which is relevant to the material number and date from the first table. I have tried pivot tables but cannot get this to work and feel it may work with a lookup. I have attached an example of the data.
so let us consider that these are the prices for X company and i have another ten tables for other 10 companies, what i need to do is to create a unified table that includes all the companies prices for all the destination depending on the Country_Code as it could be considered as a primary Key and without any duplications and to include the all the data in all the tables as some destinations and prices are not available in all the tables, so the tables contains different data, the unified table should look something like this
I've written a program to read data into a custom table from ASCII files. The ASCII files are created from a VB script I've added to an Excel spreadsheet. The VB script in Excel is the following:
Private Sub Worksheet_Change(ByVal Target As Range) Dim str_fileName As String Dim int_fileNum As Integer Dim str_fileName2 As String Dim int_fileNum2 As Integer MsgBox "Testing" str_fileName = "\EPCOR02COLLECTSinto.txt" str_fileName2 = "\EPCOR02COLLECTDisa.txt" Application.EnableEvents = False If (Target.Address = "$D$2") Then 'Write to the Sinto file On Error Goto Err_CreateOutputFile int_fileNum = FreeFile...............................
I have a table and say I would like to create a unique list from column2 and store the list in a spearate sheet. Give this unique list a name.
Use this unique list in a FOR EACH loop and apply autofilter to two tables on two different sheets. (one table is from which the unique list was created and other in a different sheet)
My company uses financial analysis software to evaluate data on a monthly basis. The software has a report writing package that is similar (but not exactly like) Excel. There are 2 functions of this report writer that I would like to find out if
Assume A1=4 and B1=5 (but the data may vary in the future). In C1, I want an green up arrow if B1>A1. If B1
I am trying to create an addin that when clicked refreshes all pivot tables and queries insteading of using the right click refresh or refreshall option button. This is part of a larger project which requires the addin -vs- the built in functionality. Anyways...the following script if I put it in a button and run it...it works perfect....if saved and run as an addin it blows past the refresh code and does not refresh anything, but will cycle through each worksheet.
I also tried thisworkbook.refreshall and activeworkbook.refreshall and neither worked.
Code: Dim wks As Worksheet Dim p As PivotTable Dim qt As QueryTable
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros.
I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet.
How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think.
An example of what I want the pivot table to do is: in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5 then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way) then do the same all over again for value in L2 etc etc etc
this should be done for the 6 values in column L, each having values up to 7 in the J column.
Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
I have a lot of data to try and summarize but they are in multiple smaller tables. I wish to only extract 2 variables, 'number' and 'total' from all of my smaller tables (lots of them) so the problem looks like this, i have these tables for example where the 'total' title is in a different column depending on which of the smaller tables you are looking at:
cat 1 cat 2 cat 3 total
[Code]....
I am trying to get:
number Total 123456 30 234567 60
[Code]...
Because the number column is fixed and all of the smaller tables will all have a number in the same column maybe i can insert a column next to the number column and say "find the value where the column title is 'total' - however the location of the column title would change so not sure if this would work? I was also thinking of sorting the columns but that will not work either because of the smaller multiple tables?
I have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?
I have roughly 100 HTML files, each containing a single table of formatted data (created with the SAS statistical software package).
I realize that I can open an HTML file with Excel, do what I need (e.g., copy, format, save as an Excel file), but I would like to try to automate the process, if at all possible, even if it's simply open the HTML file and save it as Excel. That would save me quite a few clicks it seems.
Ideally I could pass a directory name to Excel (via VBA) and have a series of "operations" (i.e., save as an Excel file) performed on each HTML file in the directory, ultimately resulting in a corresponding collection of "new" files (in my case, Excel files).
If this was very fancy, I would love to take each HTML file and copy the one table in each, and paste into a separate worksheet in a new Excel file (so 100 HTML files would result in one Excel file with 100 worksheets).
I did try to generate a macro while using the Import External Data "tool", but didn't have luck with that (although I am able to get the tool to work, just not within a macro environment).
Is there any way to use slicers on a table in 2010? I've seen posts that it is available in 2013, but can't find find a definitive answer on if there is a way to get it to work in 2010.
I am creating many tables in excel using a macro, and I want to copy them into a word document for later use. All the tables come from a long list, so for simplicity, they all get created on the same range starting at F1. Before deleting the existing table and adding the new one, I'm trying to copy/cut the table and paste it into a word document.
In case it makes a difference, I have office 2007
VB: Sub ExcelToWord(LastRow) Dim objWord As Word.Application Range("F1:F" & LastRow).Copy With objWord .Documents.Add .Selection.Paste .Visible = True End With End Sub
