0

I have a simple system of equations that needs solved repeatedly, based on slightly different input each time. The user is comfortable with Excel, and solving a system of equations is easy within Excel. Say if it were a quadratic formula with 3 equations in the system, I might set up my sheet and formulae as seen in the answer to this question.

If continuing in the example of the quadratic, I might have the user only input 2 values per equation. Then the coefficient a would be equal to the first value squared, b equal to the first value, and c always equal to 1. Displaying the entire matrix to this user (or hiding columns/rows) is something I would like to avoid.

Can I 'hard code' a value (like 1 in my example) into my formulae? Or, can I enter non-contiguous cells or perform arithmetic (like squaring in my example) inside my matrix formulae?

To be more explicit, If I had something like A2=2, B2=17, A3=3, B3=34, A4=4, B4=57, I am looking for something along the lines of =MMULT(MINVERSE({A2*A2,A2,1;A3*A3,A3,1;A4*A4,A4,1}),B2:B4) input in cells C2:C4, and get the result of 3, 2, and 1.

Do I have to perform all the intermediate steps in other (contigious) cells, in order to work with matrices in Excel?

  • I would be worried that my constant value would change at some point. In your case, establish a (possibly hidden) cell with your fixed value (1). Create a named range for that single cell, e.g. FORMULA_CONSTANT. Then in your matrix formula you can use =MMULT(MINVERSE({A2*A2,A2,FORMULA_CONSTANT;A3*A3,A3,FORMULA_CONSTANT;A4*A4,A4,FORMULA_CONSTANT}),B2:B4) – PeterT Jul 24 '18 at 19:37
  • @PeterT Thanks... are you saying that you got the formula I typed to work? Or even the formula you typed? – CWilson Jul 24 '18 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.