1. ## matrix in Excel

I need to find a way of solving the following in Excel. I think it is a called a matrix, but I do not know the type.

.................G1 ....... G2............. G3........

.................X1........ X2 ..............X3......

................X1G1 .....X1G2..... .....X1G3
.............................X2G1 ...... ...X2G2 ......X2G3
...............................................X3G 1 ......X3G2....... X3G3

SUM= ....... Y1..........Y2 ...............Y3................................. .......

I want to get the values of the series of sums (Y values) I have up to 15 values for X1 and G2 so I need to have a automated approach in Excel for solving 15 value for the sums of the Y values in the vertical columns. Can this be done in Excel? It is very labor intensive and error prone to do it manually in Excel.

Angus

2. ## Re: matrix in Excel

Welcome to MHF, angusmdmclean!

Here's an excel sheet that does what you want for 3 values.
It uses the INDEX(range, row, column) function to address the relevant cells.

3. ## Re: matrix in Excel

Hello Netherlands: I am here in Washington DC. I will try you suggestion later in the day. I have never studied matrix algebra so it will be a new challenge for me. I do not know the language of matrix albegra.

Angus

4. ## Re: matrix in Excel

Thank you IlikeSerena : It works brilliantly. I have already done 3-4 by individual calculations. So I used your spreadsheet to calculate and I got the same values! Of course it is less error prone and makes the work easier to do!

Is this called a matrix? if "yes" then what type would it be called or described or how would it be referred to? is a 2,2 matrix and if so why?

Presumably one can add many more values to the arrays just as you need? Yes?

Angus from DC

5. ## Re: matrix in Excel

Thanks!

A matrix is nothing more than a rectangular grid of cells with values in them.
Excel is perfectly suited to handle those.

The 5x5 grid where the intermediate values are (X1G2, etcetera) is a 5x5 matrix.

Yes, you can add many more values to the arrays as needed.
However, you need to take care that when you add extra values, you do that in such a way that the formulas that reference specific areas, remain intact and cover those extra values.

--I like ILSe from NL

6. ## Re: matrix in Excel

Yes; I was able to change the range. There is evidently no EDIT RANGE command in Excel so I had trouble fining my way to doing this. I used the Excel help and sometimes it is hard to believe this is an English speaking country.

I just used the basic name range commands and extended the array out to 6.

Oddly enough I have never named a range in Excel; never had to. Pharmaceuticals is my area of interest not mathematics for the sake of it. I actually have applications that I want to do.

Best Wishes from Washington DC and many thanks again;

Angus

7. ## Re: matrix in Excel

I usually insert cells within a range making it expand automatically.
Most important is to be aware of where a range starts and ends, so it does not accidentally becomes wrong.

Cheers and best wishes!