Data Table Suspecting - Stuffing Up Excel Calculations?
Sep 5, 2013
I have built a model which has approx 33,000 different combinations (output) based on user selection. There are 15 different options for user selection which gives rise to the number of outputs (2^15 -1) = 32,767.
I have inbuilt a data table that gives the output for all of the 32,767 scenarios. However, it seems like for each change the calculations takes forever and I am not sure when the calculations are complete. Saving takes forever and stuffs up excel. The numbers keep changing in front of my eyes. The file is 10Mb big/small.
View 3 Replies
ADVERTISEMENT
Mar 27, 2009
I have 2 sheets - one called 'Lookup' that has a table of recipes in the rows and ingredients in the columns. The cross reference of each is the required quantity of that ingredient, if any (obviously..). Both rows and columns are sorted alphabetically.
In the second sheet - Recipe - I have a subset of the recipes (e.g. 'Cakes') along with the applicable subset of ingredients. I've input these by hand, so I assume they can be referenced directly in the function. What I need to do is grab the quantity out of the table for each recipes ingredient. E.g. A6 will have the recipe 'Jamrolypoly' and H1 will have the ingredient 'Jam'. H6 needs to return the value from the table in Lookup.
I'm still (very) new to this but looking around I found the following which seemed to describe the function I am after:
=VLOOKUP(H1, Ingredients, MATCH(A6, Ingredients, 0))
(Ingredients is the name of the table in the Lookup sheet)
I then need to be able to use the returned values in separate equations on sheet 2 to work out costs. This bit I've done already but I've heard that some of the methods of data retrieval return 'N/A' if there is no value, and that would not allow any subsequent calculations using the value.
View 4 Replies
View Related
Nov 2, 2012
I have two queries regarding pivot table.
I would like to include additional columns with formula in to a pivot table
I would like to get ride of the Items in a pivot table with Zero values without editing the source data.
I have attached a work file in to the below link, [URL]....
View 1 Replies
View Related
Apr 25, 2014
I am trying to get my data to be visualized on a monthly basis. A Pivot Table works perfectly in getting the right arrangement, except that it performs a calculation in the values summary field. Is there a solution that allows the Pivot Table to plop the actual text values that it is pulling from? Maybe Excel Pivot Tables aren't the right solution, so maybe another type of software might be useful? Or perhaps a change in the VBA script? The original table looks like this:
ID Description Date Code Building
10223 Package/Split 02/01/2015 ALRM1 A
10223 Package/Split 02/01/2015 PLMB1 A
10992 Pump Test 03/13/2015 ALRM1 B
10992 Pump Test 03/13/2015 PLMB1 B
10992 Pump Test 03/13/2015 ALRM1 B
11432 Inspection 03/13/2015 ALRM1 C
The way I would like for it to look is this:
January February March
Building ID Description Building ID Description Building ID Description
A 10223 Package/Split B 10992 Pump Test
C 11432 Inspection
In the original report, there can be multiple codes for the same ID (ALRM1 and PLMB1 for 10223). In such cases, Pivot Table works by summing up the values, therefore displaying the ID in just one row. I would like for it to do the same thing (spit a multiple row ID as one row) but display the actual text values of Building, ID, and Description as shown. I don't need the code in the table, but it's there in the original data simply to differentiate between the two IDs.!
View 14 Replies
View Related
Jul 9, 2014
I have a worksheet that tabulate from several sheets. I have need to a field populate from a table when the value is not exact and i cannot arrange a formula correctly. The cell issues is highlighted in yellow. When a value is placed in cell r2 i need it to correctly place the value in s2 using the table from the other sheet. I have attached for review.
View 3 Replies
View Related
Jan 12, 2010
I have a list of numbers (each corresponding to a different available gear). I have four columns and am trying to populate those columns with all the possible variations without using the same gear twice in any one row. Is there any way of doing this automatically or with a script or something? There are 16 different gears so it could be rather time consuming to input all variations manually.
The attached worksheet has what I am trying to do begun. The possible number of iterations or variations is apparently enormous and am hoping for an automated way to populate those cells.
View 9 Replies
View Related
Feb 9, 2010
I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.
The table data is very simple, and can be in just two columns:
1, value
2, value
3, value
4, value
5, value
Where 1 thru 5 are time periods, and values are various numbers.
I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be
=Gammadist(5,x,y,TRUE) (x,y values not important here)
and the formula for the second value would be:
=Gammadist(4,x,y,TRUE)
The only thing that changes is the period number.
So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)
I can get this far:
={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}
But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.
View 9 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
May 8, 2013
I have a problem as I need to present data with different data sources. I'd like the calculations to be as "automatic" as possible. The data consists of countries and regions and their sales pipe. The pivot table doesn't give me all the data that I need to present so I need to add extra columns with calculations and data from other data sources.
The problem I meet is that the 'total row' within each group needs to be reflected in my columns as well (F:G) It works as long as the data I've imported has the same amount of States within each group of Contry but if the number of states differs with the newly imported data - my "manual" columns are wrong.
(A;B;C;D;E;F;G;H)
Country;State; Red; Amber; Green; Quota; Total PV; Coverage;
I've created a Pivot table on the first 5 columns [Country; ...; Risk] (A:E)
The following columns are manually entered based on the pivot (F:H)
Quota (data source from another sheet)
Total PV (a sum of Red; Amber; Green in the Pivot)
Coverage (returns % of TotalPV/Quota)
For each group Quota and Total PV needs to summarize the above data within the group. I've 'hard coded it' today (SUM(F3:F5)) - F3:F5 can as well be F3:F8 or F3:F4 depending on the imported data. I have 8 different Countries (groups) with different amounts of States and 3 different sheets for each Region of countries so I need this summary to be automatic based on what group (Country) it belongs to. How can I make the calculation different so that it's dynamic as well as the Pivot table? Today it's not dynamic and it needs my 'hands on'.
Quota =IF(ISNUMBER(SEARCH("total";A6)); SUM(F3:F5); IF(ISNA(VLOOKUP(B6;Quota!B:F;3; FALSE));" ";
IF(VLOOKUP(B6;Quota!B:F;3; FALSE)=0; " ";(VLOOKUP(B6;Quota!B:F;3; FALSE))))).
View 1 Replies
View Related
Nov 28, 2013
i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.
View 11 Replies
View Related
Nov 23, 2012
I am trying to calculate mandays starting from 1st December 2012 to 29th March 2013, and i want to count the days excluding the weekends, formula, i am trying NETWORKDAYS, i use MS2007.
View 2 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Jun 18, 2014
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
View 1 Replies
View Related
Jun 1, 2014
I have a table with 21 numbers two rows with x and y e.g.
nr.xy
205,5
195,4
185,3
so on ........
Now I wish to make a table with 100 numbers in wish y is interpolated based on y in the given table. Using many IF statements (in Dutch "ALS") isn't useful since the number of statements is becoming too large and besides is a number is missing e.g. nr7 x=0,7 the y is 0.
This is not working properly:
=
ALS(P12<$H$28;"Bodem";
ALS(P12<$H$27;$i$28;
ALS(P12<$H$26;$i$27;
ALS(P12<$H$25;$i$26;
ALS(P12<$H$24;$i$25;
ALS(P12<$H$23;$i$24;
[Code] .....
View 11 Replies
View Related
Jul 21, 2014
I need to take data from spreadsheet Control Master.xlsx then apply calculations from another one Calculations.xlsx and record results to the first spreadsheet.Data to be taken from Control Master.xls columns C, D, E. After calculations results (Calculations.xls columns Q, R, S) to be recorded back to Control Master.xls columns F, G, H.
View 4 Replies
View Related
Mar 8, 2012
As a complete novice....I want to:
1. Create a list of 3 different types of projects
2. Write three different types of calculations that should be undertaken dependent on the type of project. Each will be in a separate worksheet.
3. Make sure once I've checked the project type I apply the right calculations that match the project type
4. Summarise the results of the calculations against each of the projects
Can I do this just in excel or do I need to use macros?
View 9 Replies
View Related
Feb 10, 2014
I have some data, in a xlVeryHidden sheet, with multiple headers. An example is given below.
I would like to auto-generate a table on a different sheet with only revelent headers and filter by source and month/year. Basically, I want to write a macro that would make this table for, say, January and source1 only, without headers 1, 2 or 3.
(Very similar to the Pivot table, only without combining the data)
--------------------------------------------------------------------------------------------------------
Source
Type
Ref
[Code]....
View 1 Replies
View Related
Jan 4, 2014
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
DAILY TRAIN RUNNING.xlsx
View 3 Replies
View Related
Mar 31, 2009
I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.
Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.
The text files are here:
[url]
View 9 Replies
View Related
Jun 9, 2013
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?
View 12 Replies
View Related
Jan 10, 2013
I have an Excel table with 7 columns. I have used a formula to randomize the data in each column, and it works fine, accept that it resorts/re-randomizes the contents of the table every time data is entered anywhere in the workbook. I would like the data to be sorted/randomized only once, and then only again upon pressing F9, since I have other spreadsheet which reference the data in the table.
This is the formula I use to sort/randomize the data, and I am using Excel 2010 on a PC.
=VLOOKUP(SMALL($C$2:$C$20,B2),($C$2:D20),2,FALSE)
Column B = numbered 1 - 20
Column C = =RAND()
Column D = last of names
Column E = sorted/randomized list of names.
View 4 Replies
View Related
Sep 27, 2013
I have data below that is misaligned. I would like to know if there is a simple way to automate it's alignment like below
Table:
PC HW
PC
Operating Income
PC MN HW
PC MN
PC
Operating Income
[code].....
View 1 Replies
View Related
Nov 4, 2012
(Excel for Mac 2011). The size of my data means am crashing by usual methods of sorting - I need a formula for this rather than pivot tables.
I have a list of c. 40,000 rows. Col A is client name (largely different but some duplicates); Col B is Policy type (eg Home, Motor, Travel) and Col C is earnings on that policy (eg -60). An example would be:
Name
Policy Type
Earnings
Johnson
Home
[Code]...
So I need to figure out (via a formula) two things:
1) How to count duplicate names (eg appearing 3x = 1 customer; appearing 2x = 2 customers; appearing 1x = 1 customer)
2) How to build a simple table to show the relationship between policies. This would have Motor, Home, Travel, Motorcycle as columns and also as rows - the data would then be how many policies and in each cross point?
I have tried just working the data using Countifs etc, but as soon as I apply it to the whole data set the machine freezes, presumably due to its size
View 6 Replies
View Related
Sep 4, 2006
I have a program that calls the regress function multiple times (sometimes more than 1,000) per run. With each call to the regress function, a new workbook is automatically opened. For each new workbook opened, I am storing the contents in a workbook array called Models so I can later sort the workbooks based on the value of R squared. The program often has to open and store hundreds of workbooks before the sorting can take place so as to consider every possibility. Once the sorting has completed, I close all of the open workbooks except for the two containing the largest value of R squared. Due to the massive amounts of workbooks getting opened and then later closed, it takes about 30-40 minutes to run my program.
I want to know if after a new workbook has been opened and its value stored in the Models array, can I immediately close it and then later open only the 20-30 workbooks I really need to speed up run time? This way I wouldn't have hundreds of workbooks open at one time. Here's some code to help:
'This function will run regression stats for all possible combinations of models
'with choose variables
Private Sub Run_Stats( Combos, ByVal Size, R_Squared, Adj_R_Squared, Std_Dev, Cp_Stat, ByVal Dep_Var, ByVal NumIndepVar, ByVal Observations, ByVal Choose, Reg_Labels, ByVal Residual_Sum, Models)
View 9 Replies
View Related
Sep 22, 2006
I am having problems with calculations to be performed on date format. As in attached excel sheet, Start Date and End Date can be defined by the user. Once the user enters the dates, year 0 onwards are to be populated with values so that: (Also, not sure if I will need a button to initiate the calculations or a direct function will suffice)
year0 start = Start Date,
year0 end = (Start Date +12 months) or (End Date), if End Date is before (StartDate + 12)
year1 start = year0 end,
year1 end = (year0 end + 12 months) or (End Date), if End Date is before (year0 end +12)
year2 start = year1 end,
year2 end = (year1 end + 12 months) or (End Date), if End Date is before (year1 end +12)
year3 start = year2 end,
year3 end = (year2 end + 12 months) or (End Date), if End Date is before (year2 end +12)
year4 start = year3 end,
year4 end = (year3 end + 12 months) or (End Date), if End Date is before (year3 end +12)
year5 start = year4 end,
year5 end = End Date
If the dates are such that all years 0 to year 5 may not be covered, the years not used should be blank.
View 4 Replies
View Related
Jul 23, 2010
Is there a way to increase the size of the data table dynamically?
View 10 Replies
View Related
Jul 30, 2012
Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.
I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.
View 9 Replies
View Related
Apr 8, 2014
I have two tables each containing a column with Account ID. I want to in table A put a column that will return a yes or value of 1 if the ID in that row shows up in the ID column in table B. I have windows 7 with excel 2007
View 1 Replies
View Related
May 26, 2014
I am trying to link data from a pivot table we have in excel to a separate excel sheet.
The pivot table contains an up to date list of our inventory, however, we want to take that data and put it in a cleaner looking excel that we can e-mail to customers and publish on our website. Going individually, through every product takes too much time to have an up to date stock every day. Its even difficult to manage if we want to update just once a week.
When I link the cells of the pivot table to the new excel sheet they are constantly changing values as people are constantly editing the filters of the pivot table to look up various things.
View 6 Replies
View Related
May 27, 2014
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
View 6 Replies
View Related
