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?
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 RelatedI 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'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.
a huge spreadsheet that is taking up way too much time. It starts out with 6,000-7,000 rows, but then, she does Subtotals & it grows to nearly 10,000 rows! Then she has to deal with each & every one of the Subtotal rows, by hand. Here is a small sample of the spreadsheet, after the Subtotals have been applied:
Manifest Dttm
Manifest No
Carrier Scac
[Code].....
This sheet has columns A-Q. The key columns for our purposes are K (Weight In Lbs), L (Pickup Charge) and M (Consolidation Charge). We need to add information to column R on each SUBTOTAL row only! On each row there will be an amount in EITHER column L or column M, but not both.
1) If the amount is in L and K is LESS than 488 then R needs to show 44.39.
2) If the amount is in M and K is LESS than 124 then R needs to show 3.82.
3) If either
a. The amount is in L and K is 488 or HIGHER or -
b. The amount is in M and K is 124 or HIGHER
THEN R needs to show a formula to do this: (L+M)/(K/100)
Is there a way to either put a formula in just the Subtotal rows to accomplish this or (preferably) have a macro enter either the 44.39, 3.82 or the formula? I was able to come up with a formula that gets the correct amount in col R. Here's my formula:
Code:
=IF(AND(M3=0,K3123))),SUM(L3:M3)/(K3/100))))))
I built a very basic refresh button that enters formulas into cells.
.Range("C12").Value = "=SUMIFS(Inventory!$R:$R,Inventory!$M:$M,'Frozen Dashboard'!$D$3,Inventory!$A:$A,
'Frozen Dashboard'!C$11,Inventory!$Q:$Q,'Frozen Dashboard'!$B12)"
Basically, I have around 50 of these individual cells that I am pasting the formula into.
Currently is taking 28-32 seconds to refresh these 50 cells.
Anyways to expedite these refreshes ?
I am also using the :
application.enableevents = false, and application.screenupdating = false
but still way to slow..
I'm trying to add an enter space to text with the combination of a loop and an array, but I cannot find the right combination so that the final result appears as so:
ArrayItem
Desired Text
or via excel
=NamedRange&"
Desired Text"
Here is the code for the loop, trying to add the appendage to the first item after the if.
[Code] .....
I have following Array formula in Cells N1:N3.
HTML =SUM(IF(O$4:O$1000="Total I/O Points",N$4:N$1000,0))
Each formula has different text in "".
There are a few macros in this workbook that delete and/or insert rows.
When that happens, the formulas listed above get modified with different references. How do I restore the formulas back to the same range reference [O$4:O$1000]?
Problem-1) i want to round off the digit in the same cell i,e. Formula in F12 should be included in D12.
Problem-2) i have entered a sample account.in this i want to post the data entered in day book into their respective sheets of jaya and supervision automatically.
This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.
Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?
I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.
View 8 Replies View RelatedAttached 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
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.
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 Replies View RelatedThis 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 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 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 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