Results 1 to 7 of 7

Math Help - matrix in Excel

  1. #1
    Newbie angusmdmclean's Avatar
    Joined
    Jan 2010
    Posts
    12

    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
    Last edited by angusmdmclean; December 29th 2011 at 02:28 PM. Reason: adjust columns
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Super Member ILikeSerena's Avatar
    Joined
    Dec 2011
    Posts
    733
    Thanks
    121

    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.
    Attached Files Attached Files
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie angusmdmclean's Avatar
    Joined
    Jan 2010
    Posts
    12

    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
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Newbie angusmdmclean's Avatar
    Joined
    Jan 2010
    Posts
    12

    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
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Super Member ILikeSerena's Avatar
    Joined
    Dec 2011
    Posts
    733
    Thanks
    121

    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
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Newbie angusmdmclean's Avatar
    Joined
    Jan 2010
    Posts
    12

    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
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Super Member ILikeSerena's Avatar
    Joined
    Dec 2011
    Posts
    733
    Thanks
    121

    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!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. help in excel
    Posted in the Math Software Forum
    Replies: 1
    Last Post: May 26th 2011, 05:29 AM
  2. Excel to Matlab!
    Posted in the Math Software Forum
    Replies: 5
    Last Post: May 25th 2008, 02:00 PM
  3. excel
    Posted in the Math Software Forum
    Replies: 8
    Last Post: April 23rd 2008, 07:48 AM
  4. excel
    Posted in the Math Software Forum
    Replies: 1
    Last Post: January 21st 2008, 05:24 AM
  5. Excel
    Posted in the Math Software Forum
    Replies: 1
    Last Post: August 13th 2007, 05:26 PM

Search Tags


/mathhelpforum @mathhelpforum