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]?
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?
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.
View 3 Replies View RelatedI'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 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.
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 RelatedSay I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab.
Question #1 how this formula works or what it does.
Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I need to take a figure that appears in a cell, take off 20% vat from it and then calculate 16% off what is left, that is the figure I need in the new cell ? Show me exactly how to write that formular into the new cell?
View 3 Replies View RelatedI have an excel sheet wherein I want to enter the following formula into cell G11 when aparticular Macro is run by the user. I am using the following command but it is showing some error.
View 4 Replies View RelatedI am trying to write a macro which will get values from column B and C and print the result on column D using a simple function like the one before:
D2 = "text" & B2 & "text2" & C2 & "text3"
I need this to be done in the macro, so that when I click the button, it will automatically create column D. Column B is formula and column C is constants.
I tried something like the one below:
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
In Q3 I have a formula which determines the "next" date from today. In P3 I need to enter a formula which will return the value of the range (P6:P37) which is in the same row but different column as the value calculated in Q3.
View 1 Replies View RelatedIf the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 5.
View 3 Replies View RelatedIs there a way to add data into a cell that already had a formula? Lets say in cell A1 we have a formula like "=A1*A2". Would I be able to some how enter a value into cell A1 without changing the formula?
Example: enter value "5" into A2 and value "10" into A1 which would result in A1 value being "50" after value is entered.
I know that this creates a circular error but is there any other way to accomplish this?
I know I can put an IF formular into B1 =if(A1="sat",D/O,0) but obviously if people enter data to this cell i.e time they worked the formula goes is there anyway I can put a formular into C1 but have the result entered into B1?
A1 sat
B1 D/O
C1
I need to write a formula that calculates after a value is entered. As in I want the cell to show 12% of whatever value is entered. The form needs to look as it does so I considered inserting a column and shrinking it down so it isn't visible, however this would be a last resort. I am building a spreadsheet for others to use and excel is not something they readily understand.
I am thinking something like:
if(0,0),ifx,(x*12%)
x being the entered value. Is this possible? I am hoping for some out of the box thoughts.
How can a formula perform the equivalent of the keyboard alt enter in a cell?
ie Make =A1&A2 where A1 and A2 are cells containing text become
a1text
a2text
in the new cell.
Is it possible to have a formula in a cell which can be overwritten by manually entering in a number, but if the manually entered number is deleted, the formula remains in the cell.
View 9 Replies View RelatedI have the formula:
=HYPERLINK("[Book1.xlsm]'Sheet1!A1","To Reachback")
I may however change the name of Book1, therefore rendering the hyperlink formula useless. Is there a way to enter something in place of Book1 that will return the current name of the workbook?
If there isn't, I will have to create a VBA program to update the hyperlinks, and will therefore have additional questions about that shortly.
I am trying to make a very simple macro, that when it is run, it inserts a formula into the selected cell. But I am having problems with quotation marks etc... As it doesn't understand the 3rd and beyond quotation marks.
Sub Macro()
ActiveCell.FormulaR1C1 = "=IF(A1<3000,"Small", "Large")"
End Sub
I'm sure there may is a way of coding it in VBA properly, but I plan to use this method for a range of basic formulas. The idea is this is saved in the Personal Macro Book so I can start a column of formulas by running a Macro rather than typing it in (the example above is a simplified formula, the actual one is fairly long) - then it can just be dragged down on every Workbook I need it in.
I have a huge (for a newbie ) spreadsheet where every item is associated with several key words. There about 500 key words, all in the same column, and I have to build a table identifying the total frequency of each key word. Basically, the first column of the table I've created lists all the possible key words, and the second one is all the COUNTIF formulae, each one being associated with its corresponding key word. The formula I need to use is this one :
=COUNTIF($D$2:$D$8486;"corresponding key word from column 1")
The formula works well and my table looks fine, but the task of copying and pasting 500 key words into each occurrence of the formula is pretty daunting! is there an automatic way to enter each key word into the corresponding formula without having to do it manually? Otherwise, is there a more direct way or another formula that would give me the information I need? Surely there's a more efficient way to do this, but I just don't know how!
VBA automation -
Excel added the formula - but they all return "Not Found". After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar. In VBA I tried to use the:
objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The Excel Formula bar for the cell is just empty (blank)
Using only the Formula property:
objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The correct formula is created in each cell, but. Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.
In Excel - my vba code successfully constructs these formulas: In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.
I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.
I want the Macro to do the following THREE THINGS:
1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”
2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”
3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:
= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.
Here is an example of how the formula will work:
I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.
Here the DATA SET:
Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ
This is what is happening in Column Q Explanation of Formula
Row 2
Times
Cont
2
28 28 n/a
n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".
Row 3
Times
Cont
1
404 1194 413.4740369
O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L
[Code] ...........
I have a simple formula in cell A2
=A1
But it will not calculate. I have gone to Tools - Options - Calculation to see if calculation is set to Manual but it is Automatic. Is there another setting or have I exceeded some limit?