I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.
View 5 RepliesAttached is the file where I want to derive Min Date of seq A and asset Type Char.
SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008
I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop
I typed in the word array into Excel Help and found this item
"About array formulas and how to enter them"
I am trying to duplicate the first example and cannot
Ex: =Average(if(C5:C14="Europe",D5:D14))
I tried something similar to this (diff cell ref) and I get #VALUE
Do I need to check off an addin or something?
Do array formulas work if the array is across sheets instead of across columns or rows? I'm getting a #Ref! error when I try to use an "across sheets" array.
I'm doing this:
=sum(if('Sheet 1:Sheet 2'!A1=1,'Sheet 1:Sheet 2'!A2,0))
With a CTRL + SHIFT + ENTER return.
I have been lurking around for past month learning lots from MrExcel's wonderful web site. One of the many things I learned was how to improve my spreadsheets with Array Formulas, but today I ran into a problem on a new spreadsheet I'm building for work.
Here's the problem: I have 39 coworkers. For each coworker, I have 14 Array Formulas using SUMPRODUCT command with up to 5 "conditions", similar to this example:
{=SUMPRODUCT((user=$A$5)*(task=AO$3)*(DateChecked>0)*((Error="Error Removed")+(Error="Error Converted to an FYI")))}
Each condition such as "user" and "task" is a static named range of 5000 cells. This spreadsheet will hold one week's worth of my coworkers' work. This past week they have processed about 2500 items. To be safe, I doubled this number to determine the static named range size.
For each worker I have 56 columns (one for each possible task which a coworker can process).
So for each coworker, there will be 14 * 56 = 784 Array Formulas.
Currently my spreadsheet only has a single coworker defined, so I only have 784 Array Formulas, but it takes 35 seconds at 100% CPU Utilization when I press F9 (Calculate all formulas). Right now, I am running this on my Home PC (a 400 MHz PII PC with 256 Megs of RAM, OS is Win2000 at SP4 maint level and Excel 2002), but it is equally slow at work (1.7 GHz Celeron with 256 MB of RAM running Win2K SP4 and Excel 2K).
I haven't tested yet, but even if I assume a linear progression, with 39 coworkers I am thinking it is possible the amount of time for Excel to recalculate all the formulas will be 39 times longer than it is currently. This will be close to 22 minutes. That is a long time to wait! It will be even worse if my testing shows the amount of time Excel takes to evaluate the array formulas is exponential instead of linear...
...784*39 = 30,576 formulas...
I am having little luck creating an array to work with 3 critieria. Here is what I am trying to do: In the attached spreadsheet I am trying to show a total for each "Global Process" based on if "Reason Cancelled" and a date greater than or equal to March 1st, 2006. Here is what I currently have for the array formula: note: this is an Excel formula not VBA. I couldnt find the right code
=SUM(( 'Raw Data(Added_Cancelled)'!$D$2:$D114="Fulfillment")*('Raw Data(Added_Cancelled)'!$I$2:$I$150="Other")*('Raw Data(Added_Cancelled)'!$H$2:$H114>="2006-03-01 00:00:00"))
Does anyone know how to activate a block of different array formulas at once??
Example:
N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.
In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.
btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.
This formula is returning 0.2578 when it should return 2.473
{=AVERAGE(('Data-Design'!E2:E6000)*('Data-Design'!Q2:Q6000=1))}
What am I not understanding?
I want the average of column E when column Q=1
I would like to set up VBA codes to generate a variable number of transition matrices. I would like to know how I can define dynamic ranges instead of coding each and every range. In the codes below, I have to define each range one by one instead of using a loop.
Sub TransitionMatrix()
Dim P1 As Range
Set P1 = Cells(2, 2).Resize(3, 3)
Dim P2 As Range
Set P2 = P1.Offset(5, 0).Resize(3, 3)
P2.Cells(0, 1) = "P2"
For i = 1 To 3
For j = 1 To 3
P2.FormulaArray = "=MMULT(" & P1.Address & "," & P1.Address & ")"
P2.BorderAround Weight:=xlMedium
Next j
Next i
Dim P3 As Range
Set P3 = P2.Offset(5, 0).Resize(3, 3)........................
I've created a very large spreadsheet (4096 calculations) and I'm using array formulas for a large number of cells, which leads me to my current predicament. All the formulas are written in, but I haven't done the necessary ctrl+shift+enter after finishing all of them (there is only slight variation in each calculation so I produced them in an iterative manner) and I was wondering if there was a way besides selecting each cell individually - pressing F2 - Then pressing CTRL + SHIFT + ENTER to make all my formulas array formulas.
View 5 Replies View Related I am trying to calculate a matrix of array formulas that is roughly 365 x 137, or about 50,000 cells being calculated from a range of roughly 12,000 x 137. There are multiple if statements within the array formula, and then those 50,000 cells are referenced to another, but smaller matrix of array formulas (25 x 137). Each of these matrix sets is for one year, and there are three years that need calculated.
Problem: When calculating the 50,000 cells it takes roughly 2.5 hours to complete all calculations. So, for 3 years it will take roughly one work day of tying up my computer to just compile data that will then require several days of calculation/manipulation.
Question(s): Is there a way to speed this up to a reasonable amount of time (I'd even take 30 minutes at this point)? Or, is Excel simply the wrong tool to be using for this amount of data? Do I need a better machine to run these calculations? I am currently using a Dell XT3 with 2.5 Ghz i5 quad core processor.
I often see array formulas written with "extra zeroes", like this: {=SUM(IF(let=1,IF(cost>5,cost,0),0)). Yet I was taught to write the same formula without the zeros, like this: {=SUM(IF(let=1,IF(cost>5,cost))).
My question are:
1) what do the zeroes mean or do?
2) what happens if I replace one or both of the zeros with a different value, like the number one?
I have a worksheet "Expenses" with columns Month, Category, Expense, Amount. There are four categories and, obviously, 12 months.
In the workbook is another worksheet, "Totals" which lists the months across the top, and the categories down the left. Then I have array formulas in each cell, that look like this:
=SUM(IF(Expenses!$B$2:$B$990="July",IF(Expenses!$C$2:$C$990="Acquisition",Expenses!$E$2:$E$990,0),0) )
This works fine, until people start moving cells around in the Expenses sheet. Say they sort everything they've put in by month, and then they want to move July up to the top because it's the start of our fiscal year. So say they have six expenses for July and they cut and insert them in at B2. Suddenly the formula now says:
=SUM(IF(Expenses!$B$8:$B$990="July",IF(Expenses!$C$8:$C$990="Acquisition",Expenses!$E$8:$E$990,0),0) )
I thought by using absolute cell references instead of relative ones, I could avoid this problem, but that's apparently not the case. I tried changing the cell references in the formulas to relative ones but it still happens then too.
I have some imported data that lists the date in separate columns.
i.e.
ColumnA ColumnB ColumnC
January.......10.........2013
How do I convert this to a date by using a formula? Later I want to use the date in an array formula to sort the list.
I have a couple of array formulas that were expertly suggested on this very forum. They function spectacularly, but now I need to incorporate them into my code.
The catch is that I am not sure yet if I will a WorksheetFunction solution, or more the likes of the actual "live" formula, e.g., "=SUM(A:Z)"
In either case, I am not sure what the equivalent of CSE is in VBA, or whether the braces/curly brackets can be manually inserted in the live formula.
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1".
Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.
View 6 Replies View RelatedI am looking for ideas on how to speed-up one workbook. I have a spreadsheet with hundreds of lookup formulas and array formulas which are very slow to recalculate (takes around 5 min). Good thing about it is that these formulas are located in a specific range and that I need to run them only once a day (in the morning). Since I need to refresh the rest of the spreadsheet frequently, I am looking for an idea on how to isolate this resource heavy areas.
One solution I've been thinking about is moving these complex and slow formulas to VBA and run them only when needed. Can anybody assist me with the best and easiest way of doing this. Ideally I would want to leave option to the user to later edit this formula. Does anybody have any experience with weather it is best to migrate complete formulas to VBA or maybe leave formulas in the spreadsheet and run them based on predefined flag (that I can switch on or off from the code)?
Since I got such fantastic help with the last quesiton let me try one that
I've been beating my head against the wall on for two weeks now.
I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
the following formula:
=((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)
A Column: Dates
D Column: Monthly returns in multiplier format
I want to do the same thing as this with GSD using this basic formula --
that is, only do the returns based on the IF statement:
=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100
And Sharpe with this formula:
=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)
Any help you can offer will certainly save me a ton of pain -- head's
beginning to hurt!
I am really struggling with the following formula:
MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)
I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.
I'm trying to enter a series of formulas referencing the first cell of each row.
With Range("A40")
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).FormulaR1C1 = "= COUNTIF(Details!R2C2:R65536C2,RC1)"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 2).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC[-2])*('Details'!R2C11:R65536C11=RC1))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 4).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 5).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 7).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 8).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"
End With
While this code works for the first formula, the following 4 are arrays, and for some reason, will only reference the first A40 cell.
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
My excel (2003) is stuck in array mode - is there any way to get it out of Array Mode and put it back in the letter:number display for formulas/functions? It's doing this in VBA too which is totally killing me.
View 2 Replies View RelatedI have 7 different files that each contain one sheet. Each sheet has the same headers/data types in columns A:H, but may be any different number of rows in length. I need a macro that goes through each file and copies cells A2 through H of the last row of data and pastes them all into my master sheet ("Timesheets") where the macro will be stored. My problem is that I want all the data from File1 to be pasted, then all the data from File2 to be pasted at the next empty row, and so on. I also need to include a way to tell the macro to move on if a file is not found.
View 2 Replies View RelatedI still have a problem with consolidation of workbooks.
Scenario:
I am working in HQ and at the end of each month, every branch under my care will submit an excel workbook to me. Inside this are two worksheets (worksheet 1 and worksheet 2 for convenience). Both have standard headers and rows and rows of data. Upon receipt, I will keep all these files in a folder called "Oct06" (and Nov06 etc for future mths)
Problem:
I'm not able to find an easy way to consolidate all the various worksheet 1 into one master worksheet. I need this so as to generate a pivot table. At the same time, I need to consolidate all the worksheet 2 also for the same purpose.
I have A spreadsheet.
The spreadsheet contains 8 worksheets, of which I am only concerned with 7 as the 8th merely presents a graph.
The first 6 worksheets all contain raw data exported from BMC Remedy indicating the names of users who logged incidents to have their passwords reset or their AD Accounts unlocked over a 6 month period, hence 6 worksheets (per month basis).
The 7th worksheet contains raw data from another application known as FIM (A MS based interface which allows users to reset their own passwords) indicating all users who are registered to utilize this functionality.
The goal of my exercise is to consolidate the data and sort it to such an extent where I can ascertain which users phoned the service desk to log an incident for a password reset, even though they are registered to do it themselves via FIM ... I have been going through the list manually and it took me 10 solid hours to go through 265 ... ... ... ... I have 3'900 to go through!
I have many sheets in an excel workbook. Each sheet contains some data. I want data of all sheets into one sheet in the same workbook. The data of sheet 3 should appear below the data of sheet 2 & so on. I leave the first sheet of the workbook blank so that data of remaining sheets can be consolidated in the first sheet. Therefore I wrote the following code:
Public Sub consolsheets()
Dim a As Integer
Dim rng, rng2 As Range
a = a + 1
For a = 2 To Worksheets.Count
Worksheets(a).Activate
Set rng = Worksheets(a).UsedRange
rng.Copy
[code].....
Data of row 3 of sheet 2 (ccccc) is overwritten by data of row 1 sheet 3 (11111). Similarly, data of row 3 of sheet 3 (33333) is overwritten by data of row 1 of sheet 4 (@@@@@).
This problem is not faced if the sheet 1(blank sheet) has some data.
I have let's say 3 workbooks with 1 sheet in every book.
Sheet in first wb named "SD_Romania". It looks like this:
Sheets in other workbooks named "SD_Bulgaria" and "SD_Poland" (there are more than 3 workbooks, just an example).
What I need to do is to write a macro that can extract only "Outlook" row data from all books and worksheets into new workbook. So that data will be structured like this:
BM and KF_TYPE are constants. REG should be taken from worksheet name. VERSION should be taken from A1 column (3 chars, starting from 8 char).
Period should somehow be transformed from "March 2008" to "32008".
The macro should look search for information in all opened workbooks, regardless of their names.
This is a hard macro, I guess. But unfortunately I don't have a clue how to write vba macros
consolidating all data from different worksheets. I have use the consolidation function in excel but the result is not what to appear.
Example:
Sheet 1
nameAddTeljclag123jpasd321jmds234
Sheet 2
nameAddTeljolag135jfasd357eedds234werpoi567
Result using consolidate
AddTelBook1123jc123Book1321jp321Book1234jm234Book1135jo135Book1357jf357Book1234eed234Book1poi567wer0567
Expected result
nameAddTeleedds234jclag123jfasd357jmds234jolag135jpasd321werpoi567