Hi! i have a problem with solving a 19x19 matrix.
I've got an error !NUM when start MINVERSE a matrix.
Is it a problem if some rows are 0 all of them?
P.S. I've checked it right now after I've read some posts on here with MDETERM and it returns 0.
P.P.S. And any idea how to solve system of linear equotations in excel only by formulaes and functions and not in VBA?
Last edited by angel44e; 05-31-2024 at 08:59 AM.
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
The matrix is (A1:S19)
I wish to solve these system of equatations:
-x1+x3+x5+x6+x8+x9+x10=20
x1-2x3+x5+x6+x8+x9+x10=20
x1+x3-4x5+x6+x8+x9+x10=20
x1+x3+x5-4x6+x8+x9+x10=20
x1+x3+x5+x6-4x8+x9+x10=20
x1+x3+x5+x6+x8-4x9+x10=20
x1+x3+x5+x6+x8+x9-4x10=20
I'm not sure I understand exactly what you are doing. In the OP, you say that you are trying to invert a 19x19 matrix (implying a system of 19 equations with 19 unknowns). However, in post #3, you describe a problem involving 7 equations with 7 unknowns. I observe that Excel has no trouble inverting the matrix if I set up the 7x7 matrix given in post #3.
If I'm guessing correctly, yes, I think the problem comes from having full columns/full rows of 0s (indicating that those variables/equations are not actually part of the problem and, therefore, not part of the solution). I don't know how you like to interact with your spreadsheet, but I think you will need some strategy for extracting the variables and equations that are part of each problem and build your input matrix from that. Not knowing how you like to interact with your spreadsheet, I have no suggestion to offer at this point, but it looks like the main question is how to reduce the given 19x19 matrix (that represents the largest possible problem?) down to the nxn matrix that represents a specific problem.
Originally Posted by shg
My math is VERY rusty, but have a look at the attached, please.
You are really GODDSS!!!.That's all I needed RaulSerg.That's all done by excel math formulae and no need VBA and Solver add-in.
THANK YOU VERY MUCH!!!
And I've not seen any other forum for any topic someone answer so fast.THANK YOU ONCE AGAIN!!!
Last edited by angel44e; 05-31-2024 at 12:21 PM.
But one question more,RaulSerg! What if values in 1st 19x19 matrix are dynamic from other sheet and could be variable - so matrix could be only 1 to 19x19,how could I manage that solution?
Please give an example in an Excel sheet.
I am not really clear what the problem is.
I see currently no reason why Excel would not be able to invert a 19x19 matrix, unless the determin is 0 which I guess means the matrix cannot be inverted.
MrShorty's answer is pointing into the same direction.
Attached is an example of any 19x19 matrix that I think is sort of automatic.
edit: attachment fixed
Last edited by RaulSerg; 05-31-2024 at 03:22 PM.
That's all I need,but when I run =MINVERSE(DROP(A71#;;-1)) I've got !VALUE error and I couldn't manage it.I've checked formatting values,but somehow !VALUE error is still on sheet.
I've seen that =LET(x;A1:V19;y;FILTER(x;BYROW(x;LAMBDA(a;SUM(a)>0)));FILTER(y;BYCOL(y;LAMBDA(b;SUM(b)>0)))) give me only 16 columns and 14 rows when I input all 19 values.
Last edited by angel44e; 05-31-2024 at 04:39 PM.
Provide a file that shows your error.
At least based on my file this formula
doesn't make sense to me.
Please Login or Register to view this content.
That's file 19x19
That's file 19x19!
First, you need to include also include column T in row 71.
Secondly, the generic formula I provided filters out columns that should not be filtered out because your actual data is much different than your previous data provided.
For example, X16 gets filtered out because the column sums to zero, so the formual is doing something here that it shouldn't be doing.
As I don't know how your actual data might morph, it's hard to come up with a correct filtering for all situations.
Best if you figure out or explain in which situations rows and columns need to be included and when not.
In your dataset
gives a correct result.Please Login or Register to view this content.
So you just need to select the correct data for the function.
Hi again.RaulSerg! The matrix values comes from F4:F22 cells(substracted by 1) and from P3 cell (value for T50:T68) - could be from 1 to 1000 natural numbers.
Then that leads to equations system that could be solved by matrix 19x19 (max) or 1x1(min) - it depends from how many values are input in F4:F22.
I have noticed that if I input value <19 in F4:F22 cells then matrix is full and if it's >=19 then matrix is cut.But if I run MMULT function
only one value is returned and not all of 19 values.What could be wrong?
P.S. I'm sorry for my poor English but I could try to explain what's you need to correct matrix formulaes!
P.P.S.By the way I've corrected some errors in sending matrix values and that's no problem >=19.I've corrected values T50:T68,it should be negative.I've formated F4:F22 cells and P3 cell to custom too.But I receive !CALC error right now.
P.P.S.I've solved these all problems,RaulSerg and only one stayed.How to point values A117:A135 to be filled in H4:H22 in that order if F4:F22 are filled.
Last edited by angel44e; 06-01-2024 at 03:33 AM.
An alternative approach to construct the matrix could be this:
or whatever your relevant input range is.Please Login or Register to view this content.
That should filter out only if all values are zero, but not if the sum is accidentally zero.
I don't understand what that means.How to point values A117:A135 to be filled in H4:H22 in that order if F4:F22 are filled.
What exactly is the required output under what scenario and what is the logic employed?
Thank you very much for your help RaulSerg!!! I've managed with last my question
Last edited by AliGW; 06-01-2024 at 07:50 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
Hi all,again! And especially to RaulSerg who helped me at https://www.excelforum.com/excel-for...-a-matrix.html
Everything is OK,but when I wish to change dynamically 1st range ( I define it with Name manager and I save the workbook).But when I reopen that 1st range is dynamically changed by output source and that formula
=LET(x,NewRange,FILTER(FILTER(x,BYROW(x,LAMBDA(a,COUNTIF(a,)<>COLUMNS(x)))),BYCOL(x,LAMBDA(a,COUNTIF(a,)<>ROWS(x)))))
is saved ex-data(and gives a VALUE! error) when 1st saving workbook and not changing anything as 1st range is changing dynamically at the same time.
How could I solve that?
Provide a workbook that illustrates the issue, please - instructions at the top of the page.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
That's a workbook! Values from F4 to F22 cells are values for that 1st range (A50:T68).These values(F4:F22) are from 2nd sheet of that workbook which are updated in every 2-3 secs from output source.
Formula
=LET(x;A50:T68;FILTER(FILTER(x;BYROW(x;LAMBDA(a;COUNTIF(a; )<>COLUMNS(x))));BYCOL(x;LAMBDA(a;COUNTIF(a; )<>ROWS(x)))))
in A71 cell create new 2nd range from that 1st range (A50:T68) which have no blank rows and columns.
And if I link that sheet to output source for data update it's working till I've not closed the sheet and save it.Then after reopen it that formula in A71 cell shows VALUE! error.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks