Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
I'm trying to do the inverse matrix of a matrix captured by an inputbox, but it doesn't work
This is the code:
Code:
option explicit
option base 1
sub matrix()
Dim M() As Double
Dim B() As Double
Dim Y() As Double
Dim columnas As Range
[code]......
the error is: Error 13 the types do not match
how to inverse a 3x4 matrix in excel.
I can easly inverse NxN Matrix ( 3x3 ,4x4,2x2)
The matrix I am trying to inverse looks like this
1 1 1 1
3,5 2,5 1,8 2
17,1 11,1 5,1 6
I am trying to calculate the inverse of a matrix in vba here is my code, I get an error 2015 when I run my code
Function InterpolationCubique(TableauMaturites, TableauDonnees, DateCalculees, _
Optional EstFactActua As Boolean = False, _
Optional DateDeCalcul As Date)
On Error Goto zob:
Dim TabMat
'talbeau des maturites des donnes source
Dim TabData
'donnes source
Dim TabDates
'dates a calculer
Dim i As Integer, j As Integer
'variables de boucle
Dim TabRetour
'donnees renvoyees
Dim MatriceDate(4, 4)
'Matrice des coefficients des parametres
Dim MatDateInv As Variant
'Matrice inverse de MatriceDate
Dim VecTaux(4, 1)
'vecteur des donnees solutions du systeme
Dim VecParam
'vecteur des parametres calcules
' conversion des arguments en tableaux
TabMat = CTableau(TableauMaturites)
TabData = CTableau(TableauDonnees)
TabDates = CTableau(DateCalculees)
'dimensionnement du tableau de retour
Redim TabRetour(LBound(TabDates) To UBound(TabDates)).................
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 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.
This is my first post on this forum and I am an Excel novice. I have a worksheet with lots of blank cells and I need to shift all the cells with data all the way to the left (to column A). I cannot get the sort or filter functions to do this for me, and I've never used macros but it appears they might be useful for my problem. I'd like to find an automatic process to move the data left rather than drag and drop hundreds of times. I'm attaching the complete xls file for your viewing.
View 5 Replies View RelatedFor a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I have made a function (udf), called linjenr. It's input is an n by n matrix, and it returns a n by 2 matrix. This works fine in the worksheet. However, when I'm trying to use this function (udf) in a sub it fails, i.e. I get the error "object does'nt support this property or method". How do I use an own made function that returns an array in a sub macro? (ctrl shift + enter..)
Sub linjegenerator()
Dim nodematrise As Variant
Dim antlinjer As Integer
nodematrise = Range("F6:T20")
antlinjer = WorksheetFunction.Sum(nodematrise) / 2
If antlinjer < 3 Then
MsgBox "This is not a network. Minimum 3 lines."
ElseIf antlinjer > 2 Then
If antlinjer = 3 Then
'Range("F25:G27") = linjenr(nodematrise)
ElseIf antlinjer = 4 Then
'Range("F25:G28") = linjenr(nodematrise)
ElseIf antlinjer = 9 Then
Range("F25:G33") = WorksheetFunction.linjenr(nodematrise)
End If
End If
'antlinjer = WorksheetFunction.Sum(nodearray) / 2
MsgBox "Antall linjer = " & antlinjer
End Sub
I have a 10 x 15 matrix named "Range1"
I have named the range of Column headings in that matrix as "Weight", and the range of Row headings as "Height". And then all the matrix cells contain some values.
Now I need to find the value from intersection of height and weight (please see the attached excel file)
Attention: I need to use excel VBA code to do the trick. (Not Worksheet formula using for example INDEX and MATCH)
A have a table like this:
X 1 2
A 9 5
B 8 4
I would like to have a condition that when is equal to B and 2, return the corresponding paragraph in the matrix (in this case 4)
In the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.
Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.
I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.
The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.
Finish
Zone 1
Zone 1
Zone 2
[Code]...
Below there are all the information needed to understand my problem.
M(1 to R,1 to C): matrix with R rows and C columns [element known]
M(i,j): elements of the matrix M in position i(row)-j(column) [element known]n
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
I need to get the fee value, if I write "Name" and "Time".
Please read the simple example file named "Fee from matrix".
Given one array variable (matrix n x n) M, I want to use the function sum, to sum along the rows.
I can use Application.WorksheeFunction.Sum(M)
but this will sum all the elements
and I would like smtg like this
Application.WorksheeFunction.Sum(M(1,)
Just sum the first line, or the i-th line.
I have code to create a correlation matrix (NxN, where N is the number of columns). This is done by selecting an area that is NxN, entering the function and range, then hitting ctrl +shift + enter (array formula).
However, I want to convert this to accept VBA arrays, rather than a data range, and give the output in form of an array as well.
VB:
Function CorrmatK(dataRange As Object) As Variant
On Error Goto 20
Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object
r = dataRange.Rows.Count
n = dataRange.Columns.Count
[Code] ......
I NEED some code to convert a matrix to a singel column and it should be reversed to!
see attachment!
example:
FROM:
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
TO:
D1
C1
B1
A1
D2
C2
B2
A2
I have to create a matrix from columns in this format:
A1 A2 1
A1 A3 4
A1 A4 6
A1 A8 4
A2 A5 2
A2 A8 3
with a lot of empty cells (here, for exemple A1,A6 & A1,A7 ) and (A1,A2) = (A2,A1)
I'd like to have :
_ A1 A2 A3 A4 A5 A6 A7 A8 .....
A1 0 1 4 6 0 0 0 4
A2 1 0 0 0 2 0 0 3
etc
The source data is in the attachment. Would it be possible to do it automatically?
I have this code to take an area of data and perform the SUMXMY2 formula and output the results to another sheet. My problem is that the results are being outputted in a semi-matrix and i needed them in a column so i can them perform a sort. Is this possible and can anyone shed a light on the best way to do it?
View 2 Replies View RelatedI have a matrix A with 12 rows and 10 columns. My problem is if in the cell(i,j) there is data then the same data should appear in a similar matrix B, but in a cell which is 15 cells behind the cell(i,j).
That is it should start counting upwards from cell (i,j) in B and once it reaches the top of the matrix it should continue counting from the bottom of the immediate left column and go up. When it reaches the 15 cell from cell(i,j) in b, it should print there the value that was in cell(i,j) of A.
I am trying to figure out a better way to do my mileage for when I drive for work.
Currently I need to look at a sheet and see where I started and where I stopped and then I’ll see the distance.
Kinda look something like this.
Home
Work
School
Home
031Work
304School
140
What I would like to do is type in the “to” and “from” cell and have it automatically know the miles based on the chart above.
DateToFromDirectionTimeMiles3/4/10Home
Work
I wasn't able to attach the file because it was too big, but you can download it from here www.easygcc.com/correl.rar. On the sheet called " Correlation" there is matrix, I am baiscally trying to fill in the correlation formula into every cell so the matrix is filled out. The data for the correlation calculation comes from the sheet called "Tech Data". I have filled in the correlation formula into a few of the cells as an example, but I don't want to continue doing this manually but rather have a macro do it for me. Otherwise this will take for ever. My macro is also a part of the file, if you would liek to look at it and maybe fix what I already have.
View 4 Replies View RelatedI have a 5 x 5 matrix. When values are entered in 2 cell there needs to be a matrix lookup and the corresponding value needs to be entered into the 3 cell. Example :
- A B C D E
V 1 2 2 3 3
W 2 2 3 3 3
X 2 3 3 4 3
Y 3 3 4 4 5
Z 3 3 4 5 5
So if X and D are entered into the 2 cells the 3rd cell should show 4.
I would like to do the following transformation in excel:
imagine the following matrix:
1 2 3
4 5 6
I would like to transform this into:
1
4
2
5
3
6
This is a simple version of my matrix, in reality it is much bigger, and I do not want to copy paste each column by hand. Any ideas?
I'm trying to create a risk matrix like the attached example. I want to be able to enter a value and to have it to appear in the right quadrant.
View 2 Replies View RelatedI'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4
Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.
I've got a list of zones in my From column and also in my To column.
I need to write a formula that will give my customers the Zones Charged in a seperate cell.
I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.
FREE Deliveries to anyone in Long Beach, CA to the winner!!!
On my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.
View 2 Replies View RelatedI need a function that can get, from a matrix, the first number that is higher than the number in a given cell.
View 9 Replies View Related
