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 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.
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.
View 3 Replies View Relatedi am trying to modify and existing array formula
=VLOOKUP(R2,TRIM(Codes!$C$3:$D$283),2,FALSE)
but ctrl shift enter does not seem to work. has anybody else encountered this problem?
FYI auto calculate is on, lookup value and table array are all formatted the same. as i said, the formula works but i need it updated for one extra row.
I recently upgraded my computer. I noticed I can no longer enter array formulas. When I tried to enter an array formula using CTRL+Shift+Enter, nothing happens. I don't get any error, just nothing happens.
If I do the same exact thing on my old computer, it works - formula is converted and I see the braces {} added as part of my formula. Am I missing a macro or add-in? I'm using Excel 2002 - same version on my old computer. Is there another way to generate an array formula besides using CTRL+Shift+Enter?
I have a ton of cells that contain formulas with arrays. How can I update these without:
clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER
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 RelatedVBA 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.
know the keybaord shortcut to select a range? Indeed, how can i trick excel to use CTRL SHIFT Down in a range with blank data and to select the last data in the range?
View 1 Replies View RelatedI was thinking of how the Worksheet_Change(ByVal Target As Range) macro can be used to paste values instead of formula when the user uses Ctrl-C and Ctrl-V. Instead of assigning a macro to a command button for user to activate.
Suppose I have a worksheet where the range ("D7:D56") is where I would want the user to paste his values in it.
I have to factor in the possibility that these values have formulas attached to them, and that the user is not tech-savvy enough to know about the "paste special --> values" functionality of excel, and chooses to use the Ctrl-C, Ctrl-V method instead.
What then appears are ####### which might alarm the user.
Neither would we want the user to press a button (which we can assign the xlpastevalues macro as an alternative) as that adds an extra step for the user. There is also a chance the user might not use the button, or overlooked it.
Is it possible to use the Worksheet_Change(ByVal Target As Range) macro, or sth similar, to paste values instead of formula when user uses Ctrl-C and Ctrl-V?
complex formulas in Excel but managed to work out and create the following formula;
{=IFERROR(INDEX('owssvr(1)'!$AE:$AE,MATCH($A9&AK$1,'owssvr(1)'!$C:$C&'owssvr(1)'!$E:$E,0)),"")}
Basically I want to return a call quality score if the name and week match my criteria.
The above formula works perfectly, however, it is very slow and the sheet takes ages to update.
Is there a quicker and more dynamic way to achieve the same result?
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[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]?
For Excel 2007, does this key function only work after typing in a formula into a cell? I tried pasting the formula and am unable to get the {}.
Is there some setting in preferences to allow this?
If ur using a formula that uses Control Shift Enter to activate it in Excel,
How do u use that formula in VBA? ....
The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:
K28 = Last month balance
Column E = Type of expenditure
Column D = Values
Column B = Payments
K23 = Currency conversion rate
Sheet2ABCDE1Start Date 01/01/201005/01/2010Holiday211/01/2010Holiday3Days1445the date to be back to work 19/01/2010Excel 2010
Worksheet FormulasCellFormulaB5=WORKDAY.INTL(B1,B3,16,D1:D2)
but how i can get the same result with Xl 2003 or 2007
I have got several columns of data with subtotals, months, total unit per month, total sales per month, Example
jan feb
total unit total value total unit total value
60 £600 35 £180
SOLUTION
dateunitsale01-Jan6060001-Feb3518001-Mar335801-Apr911220
I am having to use = sign (=A10 --) transfer the information to solution, is there a formula that I use instead. I have try $A10,A$10 to ancher the column?. ideal I need formula that read alternative columns for date, unit and sales- that I can copy down?
I have the followings in my spreadsheet.
a) Column A: 900 serial nos of Work Orders. Each is unique.
b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.
Note: Column A & B are the main reference.
c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.
I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised.
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
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 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?
An OzGrid member kindly provided this formula from another post I made here:
http://www.ozgrid.com/forum/showthre...837#post326837
= SUMPRODUCT((--(WEEKDAY(Database!$E$7:$E$3973,2)=(ROW()-3))*1)*(Database!$C$7:$C$3973= 'Pattern Analysis'!$A$4)*(Database!$F$7:$F$3973='Pattern Analysis'!C$3))
My problem is that the =SUMPRODUCT is slowing my sheet down considerably and I was wondering if any of you formula whizzes can convert it to a less processor hungry alternative such as DCOUNTA (if that's possible) or suggest a better method.
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'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 need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.
i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.
I've attached a sample workbook to show.
MDT Report December 201322(FILEminimizer).xlsx
I have the formula but it will always change when i shift my cell down
=MAX($G$21:G35)
When I shift my cell G21 down, it will change to below
=MAX($G$22:G35)
The problem is that I do not want the formula to change. How can I do it ?
How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??
View 4 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.
