Average Data After Doing Sumproduct To Avoid Having Source Open
Jun 29, 2013
I am pulling data from a database and want to do an average based on data that was input per day, basically I did a SUMPRODUCT in a different file to avoid having the source open, but I can't make it average the data and disregard if there were ZEROS in that day.
e.g.
Column A Column B Column C
6/25/2013 A 1.3
6/25/2013 B 1.45
6/25/2013 C 1.9
6/25/2013 D 0
In the other file I would need to summarize the average of the Data from COLUMN C based on the day but without counting the zeros
View 1 Replies
ADVERTISEMENT
May 12, 2014
My first shot at a function! hows it look?
Code:
Function wbIsopen(wbname As String) As Boolean
On Error Resume Next
WbIsOpen - (workbooks(wbname).Name = wbname)
End Function
[Code]...
This is also my first shot at using a DIM and Set. I am attempting to move the data from 1 workbook sheet to another after it has been sorted. But if i only have the source open i want it to open the destination workbook and sheet then paste and close the source sheet. If the source is open I want it to go to the next command.
View 3 Replies
View Related
Jan 7, 2013
Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).
Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?
All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?
View 14 Replies
View Related
Feb 21, 2014
I need to Average the cells I7, I9, F12:F18, M12:M18 in Cell G2
Cell F12 is an Average of its respective cells bellow. (and so forth)
NRT will = 1
N/O will = 0
I have cells F12:F18, M12:M18 indicate NRT in the cells when selected I would like N/O to also be displayed however it comes across as 0.
How do I avoid the #DVI/0! Errors and get the averages to calculate appropriately.
View 1 Replies
View Related
Sep 7, 2006
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon
View 9 Replies
View Related
May 24, 2012
Can i use SUMPRODUCT and average together? ?
I am using sumproduct to sum the total number of transactions in each country. The argument is =SUMPRODUCT(--(Sheet1!$C$1:$C$9999),--(Sheet1!$AB$1:$AB$9999=Sheet2!A4))
The Firts part C:C is a list of every transaction value, the second is the country to which the transaction took place in. is there an easy way to obtain the average transaction value as te countries are spread over the list?
View 4 Replies
View Related
Jun 23, 2006
I've run across something a little strange. I have a query (in, say, Workbook A) that links to a table in another Excel workbook (call it Workbook B). If somebody happens to have workbook B open (it's on a network) and I try to refresh the query, it actually opens workbook B to refresh the query. If it's not being used, it just refreshes without opening the file.
The issue is that I'm refreshing using macros, and when another workbook pops open, the macros break when they try to use other sheets and ranges and stuff. Obviously, the simple answer is to just use a whole bunch of "ThisWorkbook" statements to make it work. However, I never wanted the workbook to be open in the first place. What's more, if I refresh the macro again, an additional VBA project opens for workbook B (so, now I'd have 1 project for WB A, and 2 projects for WB B). This just seems odd.
Anyway, my question is this:
1) has anybody seen this before, or is it explainable?
2) Is there a way in VBA to determine if a file is in use prior to opening it?
View 3 Replies
View Related
May 25, 2008
I have 3 excel sheets (A,B and C) linked to a master sheet (X). In each sheet (A,B and C), people enter unique idetifier in first column, its type (mr, ab, J) in the second column, start date in the third column and finish date in the fourth column. At the end of every week, i subtract each start date from the finish date and get the number of days spent for each unique idetifier (column N). I preiously posted a thread (Calculate difference between dates excluding weekends) to inquire about how to calculate the difference between finish date and start date and exclude the weekends. I received some nice solutions, but none has worked as the start date cane be weekend as well.
My second and major query is once i have the difference in weekedays for each unique identifier in all the three sheets, I want to calcuate the average of each identifier type (mr, ab and J) in the master sheet. In the master sheet, i have used sumproduct to calculate the average of each idetifier type from column N for each sheet. That means i have a column for A sheet, subdivided into three types, which include the average. I am using sumproduct for this. Once this is done for each sheet type, i need a final colum in the master sheet that calculates average of all mr from A, B and C together, and similarly for ab and J. The problem here is if i combine all the sumproduct formula, i am not gettng the right answer.
My formula looks like SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40)) for all mr in sheet A. I then divide this by SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>"")) to get the average of all mrs in sheet A. I do the same for sheet B and sheet C for all three identifier types. Now i want to combine the formula for all sheets together under mr, Ab and J. I thought the following should work ((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40))+(SUMPRODUCT((SheetB!$E$5:$E$40="mr")*(She etB!$M$5:$M$40))+(SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40)))/((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>""))+((SUMPRODUCT((SheetB!$E$5:$E$40="mr") *(SheetB!$M$5:$M$40<>""))+((SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40<>""))).....
But this is giving me wrong value. It is working fine when done for individual sheets, but when combined for all three sheets together, the value is wrong.
View 9 Replies
View Related
Nov 3, 2011
(SUMPRODUCT(--('Job'!$F$2:$F$1000="CCTV"),--('List'!$S$2:$S$1000="pass")))
There is another column with 'released' date (date the job was raised)
How would i implement a formula to calculate the average age of a certain job type in the above example?
View 8 Replies
View Related
Jan 2, 2009
I’m using the following formula:
SUMPRODUCT(--(Issue="Yes"),--(Month=Aug),--(Building=$A$9))
To find the number of instances where there was an issue in a certain building during a certain month.
What I can’t figure out is how to find the Average number of such instances for the past 3 months, the last 6 months & the trailing twelve months. If I change the formula to:
SUMPRODUCT(--(Issue="Yes"),--(Month>April),--(Building=$A$9))
I get the correct sum for the months of May, June, July & Aug but I need an average number of instances per month for the last three, last 6 and TTM but not to include the current month.
View 9 Replies
View Related
Feb 1, 2010
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.
My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:
I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
View 11 Replies
View Related
Sep 29, 2011
I am currently pulling in all cells from various worksheets, into a specific tabs in a workbook. Each source is pulled into a seperate tab such as Workbook A is pulled into tab Company A using the following formula which works fine.
Code:
"Path[Workbook.xls]Worksheet!ReferenceCell"
Note: This is done due to the use of indirect in the next formula and its inability to work on closed workbooks. Within a summary tab, I am then pulling in specific fields from each of the aforementioned worksheets using the following formula
Code:
=IF(OR(M$220="",M$218=""),"",IF(ISNA(VLOOKUP($B223,INDIRECT
("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT(M$220)),FALSE)),"",VLOOKUP
($B223,INDIRECT("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT
(M$220)),FALSE)+M$222))
M217 - Worksheet Name
M218 - Worksheet Date
M220 - Worksheet Range
M222 - Additional Fee
B223 - Lookup Value
The issue is that one company out of 14 pulls into this workbook (using the first code) just fine with all values visible, but ONLY when the source file is open. Even if I manually update the link, the values do not change.
Is there a setting or something to check, maybe in the source workbook? I have ensured that automatic updates for links is on and there are no macros in the source workbook.
View 2 Replies
View Related
May 7, 2012
I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?
View 3 Replies
View Related
Apr 24, 2014
I have a workbook entitled "Correlation Matrix". I download data from my data provider and then update it in excel each night. I have several worksheets in the workbook, one called "MCC". Today I made a copy of MCC, named MCC2, to experiment, then deleted it. Now, after I update my data in Excel it gives me the msg, "We can't open the source file '(Path)[Correlation Matrix.xlsx]MCC2'".
The data updates fine and all values are correct in the worksheets. I've tried closing and reopening the file, closing and reopening excel, but I'm still getting this msg when updating the data.
View 2 Replies
View Related
Dec 1, 2009
I've been trying to figure out a dollar weighted average formula in excel.
See attached file...
In sheet 1, I need to pull data from sheet 2 using a sumproduct formula to find the dollar weighted average maturity of a bond portfolio. Basically, I need to know how many days the portfolio has left to mature according to the portfolio's weighting by the amount in column H.
View 6 Replies
View Related
Mar 24, 2014
I built a template which pulls data from the source file using formulas. the problem is formulas doesn't give any error it updates the right value when the source workbook is open.
I Thought problem could be with the formula and tried just Source A1=destination A1. it also updates only if the source file is open. when the source file is closed it shows the last captured value.
The source file is on the (Work) Network Drive and is accessible to few people. my template is password protected (tabs) so formulas cannot be deleted.
View 1 Replies
View Related
Dec 31, 2013
I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.
I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).
Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.
Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:
(LEFT($D$2:$D$100,LEN($J9))=$J9)
Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.
Attached file. [URL] ........
View 9 Replies
View Related
Feb 21, 2010
I trying to convert the following formula (I, II, III) and add criteria of Name and Date to the count values of “c”
CURRENT FORMULA
I: “=COUNTIF(C2:C3100,"
View 9 Replies
View Related
Nov 14, 2012
I am trying to adapt a macro to run on data that is consistently in the same format but the data does change, the macro needs to run over and over on new excel workbooks, dynamic range ? instead of a set source ?
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"weekly71012!R1C1:R9379C30", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
I am sure this is the problem as i initially created the macro using weekly71012 excel file however i just want it to run on every file i pull it off?
View 2 Replies
View Related
Feb 21, 2014
I have a workbook that contains two sheets that has 20k of rows of data and in a summry sheet I have a SUMPRODUCT and SUMIF formula together which performs the sum function on the large set of data based on a range of criteria on another sheet. Here's the formula:
=SUMPRODUCT(SUMIFS(BalSht!$F$2:$F$20175,BalSht!$B$2:$B$20175,CedantCode,BalSht!$K$2:$K$20175,'2. Booked Financials'!$A46))
The problem I have, is that by combining the large data set into my workbook it is becoming slow and unwieldy. I was thinking of having a macro button that when the user has input the criteria, they click the button and it then opens up another workbook and performs the sumproduct(sumif formula.
View 5 Replies
View Related
Oct 2, 2008
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous
View 2 Replies
View Related
Jun 19, 2008
I have a range (offset based) which may increase by rows or columns whenever necessary.
In that I have data table like this:
HUNONETWOTHREEFOURFIVESIX
100110021003100410051006100
200120022003200420052006200
300180028003800480058006800
400140024003400440054006400
500150025003500450055006500
In the above table I want to enter in another column or row, a number which is not available in the given table (i.e. no duplication is allowed). (This table may grow both column & row wise). If, duplication is there the message box should say that the value already exist. And, if possible, the existing value can be colored with any color, so that we can easily find out where it is.
1) how to name a offset table and
2) how to avoid duplicate value when entered? Is data validation is one solution?
View 9 Replies
View Related
Jul 7, 2014
I have a dynamic column chart that may contain positive or negative (or both) values. The columns contain Data Labels positioned 'Outside End'.
I want to avoid the Data Labels (for the negative values) overlapping the x axis. Is there a way to do this via VBA?
I know how to update the chart axis via vba linked to cell values, but I can't figure out a formula I can use to calculate the minimum which takes into account the distance needed to avoid the overlapping problem.
View 1 Replies
View Related
Jul 13, 2009
I am trying to AVERAGE an Open-Ended (entire) column, while keeping the first 4 cells, "Out of the loop!"
Example:
=AVERAGE(E:E) less cells E1 through to E4, inclusive.
View 5 Replies
View Related
Oct 2, 2006
I need to average a column, without a fixed number of rows, and within an interval (and excluding zeros).
Example: Average of all numbers in the bracket 28-35, in column B2-B??
By this I mean that the total of rows in column B will vary from time to time, so in order to not having to manually change the number of rows each time, I need an "open-ended" formula, that also incorporates say 4 intervals (for instance 1-27, 28-35, 36-70, 71-100).
View 13 Replies
View Related
Nov 27, 2009
I am inputting 5 variables in columns A through E:
Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth
Gender
I am attempting to generate:
Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets
I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).
However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?
View 6 Replies
View Related
May 21, 2007
I have embeded a PDF file in an Excel Worksheet. When I double-click to open the PDF file the I get an error message: "Cannot start the source application for this object." However, the pdf file opens anyway. how to prevent the error message from displaying (without stopping real error messages)? (...happens in Excel 2000 and Excel 2003) NB: I researched this site first - user "Ed" raised the same issue 27/1/03 - but it doesn't appear to have been resloved.
View 4 Replies
View Related
Dec 27, 2012
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
View 3 Replies
View Related
Apr 14, 2008
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies
View Related
Apr 1, 2009
Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?
View 5 Replies
View Related
