Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to compute value in Total row based on Column Heading in seperate sheet

    I have text "total" in Col A on sheet1 and would like a formula to compute the value in the row where total appears based on the heading "AVG Deals". The Column Number and number of rows can change each month


    Sheet2 will have the same heading and the formula to be set up on sheet


    It would be appreciated if someone could kindly assist me in resolving this
    Attached Files Attached Files
    Last edited by HowardC; 2015-10-12 at 13:19.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..here's the file with the formula you want.

    zeddy
    (it seems that adding the formula made the file smaller!)
    (..or maybe it's smaller because I used Excel2016)
    Attached Files Attached Files
    Last edited by zeddy; 2015-10-12 at 13:39.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help. What is the significance of - 1 after false in the formula?

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    The Match parts of the formula return the exact row number and the exact column number for the required cell.
    Suppose this gives us row 21 and column 6 for our required cell.
    Now, since we are using the OFFSET function, starting in the cell location [A1], we need to to 'go down' 20 rows (to get to row 21) and 'go across' 5 columns (from cell [A1] ) to get to the required cell. So that's why we need the - 1 bits.

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the explanation Zeddy. I now fully understand the logic

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •