Results 1 to 4 of 4

Math Help - Excel formula to calculate values in a matrix with fixed totals for line & colums

  1. #1
    Newbie
    Joined
    Oct 2012
    From
    Belgium
    Posts
    2

    Excel formula to calculate values in a matrix with fixed totals for line & colums

    Dear experts,

    I have a question. I need a formula in excel to calculate following :

    Say I have a matrix in excel 4 rows, 3 columns :
    8 5 6 19
    4 7 2 13
    3 1 4 8
    2 3 1 7
    17 16 14 47

    - values in red are totals (column & row)
    - green value = grand total

    Now we do a reorganisation of the row totals to :
    Total row 1 = 15
    Total row 2 = 11
    Total row 3 = 12
    Total row 4 = 9
    Grand total remains 47
    Column totals (17, 16, 14) are FIXED too

    The formula is to recalculate (pro rata) the matrix values in order that the row totals become as set above (15, 11, 12, 9),
    and the column totals remain equally (17, 16, 14).

    Can somebody help me for this formula ?

    Thank you very much,
    Roskammer
    Last edited by roskammer; October 6th 2012 at 11:29 PM.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Sep 2012
    From
    Australia
    Posts
    3,668
    Thanks
    609

    Re: Excel formula to calculate values in a matrix with fixed totals for line & colums

    Hey roskammer.

    Just to clarify can you say what is fixed and what varies in terms of the cells (and relevant summations) please?
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Oct 2012
    From
    Belgium
    Posts
    2

    Re: Excel formula to calculate values in a matrix with fixed totals for line & colums

    Dear chiro,

    I need to recalculate cell A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4.
    A5=17=SUM(A1:A4) (=fixed)
    B5=16=SUM(B1:B4) (=fixed)
    C5=14=SUM(C1:C4) (=fixed)


    D1=15 (=set) -> changed from 19 to 15 --> must be SUM(A1:C1)
    D2=11 (=set) -> changed from 13 to 11 --> must be SUM(A2:C2)
    D3=12 (=set) -> changed from 8 to 12 --> must be SUM(A3:C3)
    D4=9 (=set) -> changed from 7 to 9 --> must be SUM(A4:C4)

    So i need to recalculate the cell mentionned above prorata to the changed row totals...
    I hope I made mysef clear ?

    Thank you for any help.

    Rgds,
    Roskammer
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Sep 2012
    From
    Australia
    Posts
    3,668
    Thanks
    609

    Re: Excel formula to calculate values in a matrix with fixed totals for line & colums

    The first thing to look at for this kind of thing is if a unique solution exists.

    You have 12 variables which means you need 12 independent constraints. For future reference, if you need to solve n variables you need n independent constraints for a unique solution (if one exists).

    Your sums give three constraints (3) in the first section, you have four constraints in the middle section (4) which gives a total of 7 individual constraints out of a needed 12.

    So you are five constraints short which means you need to either add more constraints that are independent of the above 7 or you will multiple solutions.

    In other words, you will need to either fix five of the cells or introduce five constraints (like sums in the way you have done above) in order to get a unique solution and every single constraint must be linearly independent to the other.

    If you only have linear sums of all the variables as constraints, you can stick this in a matrix find it's inverse and get the solution to all 12 cells in a heartbeat.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. excel statistic formula help
    Posted in the Statistics Forum
    Replies: 2
    Last Post: July 20th 2012, 03:58 AM
  2. Using excel to calculate gradient
    Posted in the Algebra Forum
    Replies: 3
    Last Post: July 27th 2011, 06:49 AM
  3. need help with excel formula
    Posted in the Business Math Forum
    Replies: 5
    Last Post: August 31st 2009, 05:50 AM
  4. Replies: 4
    Last Post: May 18th 2009, 11:16 PM
  5. Replies: 0
    Last Post: February 7th 2007, 05:27 AM

Search Tags


/mathhelpforum @mathhelpforum