Difficulty With IMPOWER() Worksheet Function
Jun 7, 2008
1) The cubic root of complex numbers "x+yi" are calculated using IMPOWER().
The w/s function appears to return incorrect values when the coefficients of the complex number are -ve.
2) For example:
A1:: -8+0i
B1::= IMPOWER(A1,1/3)
the function returns: 1. + 1.7320508i
instead of the correct value of -2.
3) The problem appears to be consistent with calculating theta [=atan(y/x)] as "pi" instead of "0" for the above example "-8+0i"
View 9 Replies
ADVERTISEMENT
Feb 1, 2013
I have a sheet with part numbers, costs and wish to add varying number to the cost in each row, based on a certain aspect of the part number:
A
B
C
[Code].....
In the example, 01/1X/2X/3X are the differentiating aspects of the part number. To rows in column A containing 01 (preceding the dash) I want to add nothing, 1X+.75,2X+1.5,3X+9.
I have researched the nested IF formula and can use it in a test as long as column A contains either numbers or letters. The problem I have is because there is both numbers and letters, the logical argument won't validate "if true". Here is my formula (omitting my attempts with parenthesis etc. around the letter in the logical argument):
=IF(IF(A1=1X,B1+.75,IF(A1=2X,B1+1.5,IF(A1=3X,B1+9,B1)))
View 9 Replies
View Related
Sep 11, 2013
I have researched a problem I am experiencing in Excel 2010 and have had problems in the past with previous versions. It seems that occasionally I run across a link that just refuses to break. During my research I have found some reports that this may be linked to the "Name" function. !!!Bing!!! the light comes on. When I copied my template to my new folder and tried to cut and paste data into it I got a message saying something like "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet." and I opted to replace the existing name with the new one.
Apparently I created an external link at that point and one that cannot now be broken unless I delete my named range and start over the naming process. I mention this here because I see lots of people having this problem and the stock answer is to goto the Edit Links menu and attempt to break the link. Try as they and I might, this doesn't work. I also saw a post about it being a phantom validation problem and that too panned out although I can see how the validation could be linked externally and create this problem.
Does this accurately describes the issue and is there a remedy besides re-creating the "Names"?
View 1 Replies
View Related
Nov 3, 2008
I may not have the terminology right, but here's my problem:
With the Range Select Formula i.e.
View 14 Replies
View Related
May 6, 2008
I am copying a range of rows from one sheet to another.
The rang consists of 11 rows; the first and last will always have data in them but the rest may or may not. Consequently, there are always a number of blank rows which I want to delete...
View 9 Replies
View Related
Sep 15, 2009
I have a formula that currently works on it's own as a array formula, however I need to integrate an IF statement into it. I tried this, and the result shows a blank cell (it should be blank only if false), but the data reflects a true statement.
{=IF(Client!$D$9="Scale 10",INDEX('Base Model'!$K$3:$K$237,MATCH(1,($B3='Base Model'!$A$3:$A$237)*($E3='Base Model'!$E$3:$E$237)*($D3='Base Model'!$D$3:$D$237)*($C3='Base Model'!$C$3:$C$237),0)),"")}
Client!$D$9 is a formula, and currently the result is Scale 10.
My attempt is that if the statement is true, the INDEX MATCH should be calculated and populate the result, and if the statement is false, then the cell should display a blank result.
View 9 Replies
View Related
Mar 12, 2014
I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
View 1 Replies
View Related
Aug 11, 2009
I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?
View 14 Replies
View Related
Sep 24, 2012
I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".
However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"
Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?
View 5 Replies
View Related
Oct 23, 2007
This line is returning the number of the last row with data in it in column 1 for the open worksheet:
View 3 Replies
View Related
Jan 20, 2009
My workbook has a sheet called "MyList", another called "HisList" , a named range called "myNamedRange" which is "MyList!A:A".
How do I get this functionality of this excel formula
View 2 Replies
View Related
Dec 12, 2007
MATCH("b",{"a","b","c","b"},0) returns 2
how about if I would like to have the return result as 4 since I have another "b" within that same array?
View 14 Replies
View Related
Apr 24, 2008
I'v got a school task about making a excel worksheet.
But we have just started and i havent gotten all the functions to work yet.
The task itself is :
Make function that tests if D9 is larger then 100 and I9 is less then 50. If true "Kontroll" if not "OK" should be written.
Also, if "Kontroll" shows up it should have a dark green "OK " should just be black
Its roughly translated from norwegian. If you need some more information I will try to provide it
View 10 Replies
View Related
May 2, 2014
I've got the following array formula :
=MAX(IF(C:C="JAMES",ROW(C:C)))
Which nicely returns me the last occurrence of the name JAMES in column C
However, how would I translate this to VBA code?
I want to find the last occurrence of a value in a column and I prefer not to loop.
View 7 Replies
View Related
Feb 5, 2009
I'm having a hard time figuring out how to use the SumProduct worksheet function in VBA. The below worksheet formula works: =SUMPRODUCT((accProv=D2)*(accFYE=E2)*(accAcc))
- or -
=SUMPRODUCT(--(accProv=D2),--(accFYE=E2),(accAcc))
I tried the below variations in VBA, and I keep getting run-time error 13.
View 3 Replies
View Related
Sep 24, 2012
I've written some code to try to count the number of cells that are not empty and save the result as a variable but sometimes the code seems to think that the cells are empty even though they are not.
The code goes through rows 1 at a time and counts the quantity of cells that are not blank and compares them with a couple other values.
The strange thing is if I execute the actual function =COUNTA(with the range that I am selecting below) the COUNTA function works properly by itself in certain problematic sections of the worksheet.
This code below seems to work correctly most of the time but I've found a few instances where it is not properly counting non-blank cells:
Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)).Select
QtyTests = WorksheetFunction.CountA(Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)))
why my QtyTests variable sometimes is saved as 0 even though there may be some cells that contain values?
Here is the expanded code:
Sub Macro()
'Global Variables
Dim QtyTests As Integer, ReqTests As Integer, InitQty As Integer, Cork As String, Corktype As String
Dim result As Long, x As Integer
[Code]....
View 2 Replies
View Related
Mar 20, 2008
I have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments:
- a range for the input data to smooth
- a parameter detailing how to do the smoothing.
It outputs a matrix of smoothed results, and therefore requires the Ctrl-Shift-Enter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results.
This is how the working syntax is in a cell:
=SMOOTH(B3:B1032, "5RSSH,5RSSH,>,5RSSH")
And there the nightmare starts :
- I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue
- Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process).
I have tried an example I found somewhere using a built-in Excel Worksheet function MMult and it worked with ranges:
Sub SomeSub()
Dim xArray As Variant, yArray As Variant, zArray As Variant
Dim Fn As Object
Set Fn = Application.WorksheetFunction
xArray = Range("A1:B2").Value
yArray = Range("D1:E2").Value
zArray = Fn.MMult(xArray, yArray)
ActiveCell.Resize(2, 2).Value = zArray
End Sub
View 9 Replies
View Related
Mar 21, 2008
I have a variable called LU which I am trying to use to do a match function in a macro.
OwnerOldRow = Application.WorksheetFunction.Match("C" & LU, "Owners1!$A2:$A65")
What I am hoping to do is find the row associated with column C and row value from LU. I have a stop on the line and LU is properly valued but it won't do the match. Is my syntax wrong. I am leaving the match type blank as it defaults to 1 which is the value I want anyway.
View 9 Replies
View Related
Jul 15, 2006
I am trying to use a dynamic range in VBA. But I am getting compile error.
Range("D19:G19").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D19").Offset(0, 0, _
Application.WorksheetFunction. CountA( activesheet.name!Master)) , 3)
In the above code Master is a dyanmic range. I need to autofill the copied data in the 3 coloumns.
View 3 Replies
View Related
Sep 6, 2006
I am trying to use VBA to find a value on a trendline. I have been unable to call the TREND function from Excel, and I can't see what I'm doing wrong. I'm using the following line of
point25 = WorksheetFunction.TREND( Range("d2:d25"), Range("c2:c25"), 25)
I keep getting "Runtime error 1004 - Unable to get the Trend property of the WorksheetFunction class".
View 9 Replies
View Related
Sep 7, 2006
VBA will not allow the worksheet function MMult to be called during the subroutine. The error that flashes is: "Run-time error 1004. Unable to get the MMult property of the WorksheetFunction property"
Worksheets("MatrixOutput"). Range("ak4:di35").Select
Worksheets("MatrixOutput").Range("c4:ah80").Select
XTranspX = Application.WorksheetFunction.MMult("c4:ah80", "ak4:di35")
Worksheets("MatrixOutput").Range("b84:ag115").FormulaArray = XTranspX
View 4 Replies
View Related
Jan 30, 2007
I am having problems getting the code right for the WorksheetFunction. I have put a snip of the procedure below.
I have data that is sequentially entered into columns, always in rows 4 to 18 with the column ref increasing by 1 each time new data is entered.
After each instance of data entry into a new column I need to loop through that data and, if the a cell value matches a value in range A1:A200, to place x in column B in the same row as the match in column A. All in Sheet2.
Set cStartcell = Sheet2.Range("IV4").End(xlToLeft) ' goes to last entered column
For Each cell In Sheet2.Range("B2:B200")
Application.WorksheetFunction.If(Match(Offset.(0, -1), Sheet2.Range.("cStartCell:cStartCell.Offset(15, 0)"),""x"")
Next
I have put the problem in bold. whatever I try seems to come up as 'end of statement expected' or 'identifier or bracketed expression expected' errors. When I have managed to get rid of errors the fromula does nothing.
View 3 Replies
View Related
Jun 20, 2008
Can you have a function create a new worksheet? i know this is possible with a Sub but i haven't been able to do this with a function. Example is below. I'm planning to call the function from an Excel cell (i.e. =CreateNewSheet())
Function CreateNewSheet()
Sheets.Add
End Function
View 8 Replies
View Related
Jun 4, 2009
The sum function won't work in my worksheet. See the green shaded area in the attached spreadsheet. Does anybody know what the problem is?
View 2 Replies
View Related
May 28, 2009
Is there something I can program into my form so that when a record is added and the 'add' button is clicked, it not only adds the record to the work sheet but saves the workbook as well.
View 2 Replies
View Related
Jul 20, 2009
how to update a function when opening worksheet. I made
View 4 Replies
View Related
Jan 9, 2006
I have a spreadsheet with 5 worksheets representing the working days of the week. Each worksheet contains data pertaining to every occurance of that day for as long as it has been recorded. Each day is broken up into hourly data.
So for example, for Monday's worksheet, you would have
Col A..............Col B........Col C
9/jan/06........5am.........data
9/jan/06........6am.........data
9/jan/06........7am.........data
...etc....then...
2/jan/06........5am.........data
2/jan/06........6am.........data
2/jan/06........7am.........data
...etc....
We are trying to create a function, on another worksheet called Query. The only way I can describe what we want it to do is to do it in psuedocode.
Psuedo........................................
View 10 Replies
View Related
Jul 30, 2006
What is the benifit of the worksheet function AREAS?
How can we use it with other functions?
View 14 Replies
View Related
Dec 11, 2008
I'm having a problem with the HYPERLINK function. I am trying to make jumps from an index sheet to the large worksheet with all the actual info in it. The name of the file is SKU and the large info sheet is also called SKU. First I took the SKU sheet and numbered the rows (="A"&CELL("row",A1)), then in the index worksheet I created a VLOOKUP that finds the corresponding chapter title and returns that value.
Worksheet 1
A B C
1
2 100 =VLOOKUP(A2,SKU!$A$1:$B$3,2,FALSE) ="[SKU]SKU!"&VLOOKUP(A2,SKU!$A$1:$B$3,2,FALSE)
3 150 =VLOOKUP(A3,SKU!$A$1:$B$3,2,FALSE) ="[SKU]SKU!"&VLOOKUP(A3,SKU!$A$1:$B$3,2,FALSE)
I used the HYPERLINK formula in column D: "=HYPERLINK(C2,A2)" and it returns something that looks like the right thing but the link won't open.
View 2 Replies
View Related
Oct 21, 2013
I am attempting to write a bit of code so that Vlookup is called when a particular cell in my spreadsheet is populated.
Although it seems to be working, it is only pulling in the data in row 1.
An example of the code is below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row 1 Then
If Target.Column = 2 Then
[Code]....
View 1 Replies
View Related
