+ Reply to Thread
Results 1 to 8 of 8

Columns don't add up in cross-checking with Rows

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    49

    Columns don't add up in cross-checking with Rows

    Hi, hope someone can help me with this one.
    I have added an attachment here with my spreadsheet tracker for a large multi site project, which I've butchered a bit for simplicity and confidentiality reasons, only keeping in the important bits.
    The main sheet tracks all the relevant details of each site, dates, costs, quotes etc.
    The second sheet is a summary page which should automatically update as new entries are made on the main sheet. All is working ok, but when I checked some of the calculations on the summary sheet there was a difference in the Total figures. The calcuations are made in columns, but in checking the rows they dont hac=ve that same result
    I am certain that rows 4 - 8 are all correct, with correct results in F4 down to F8.

    The figures in range C14 to F16 are the concern, particularly the Totals cells -F14, F15 and F16. Column-wise they all add up correctly, but when doing a check and adding up the rows (ie C14, C15 and C16) they should be the same result but they are not.
    I have highlighted the cells in yellow and added in the alternate results in column G as a visual aid.
    F16 and G16 should both have the same figure (as should F15/G15 and F14/G15).

    I've looked at these several times and i cant see where the problem is, so I hope someone can point me in the right direction.

    Thanks
    Steve
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Columns don't add up in cross-checking with Rows

    Why do you think the numbers should be the same?

    Consider a simpler example if your values in C6:E6 were all 15, and your values in C5:E5 were 2,3 and 5 then the result of dividing the former by the latter would be 7.5, 5 and 3 which totals 15.5

    On the other hand the sum of row 6 would be 45, which divided by the sum of row 5 (10) returns 4.5

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Columns don't add up in cross-checking with Rows

    how does =(F6/F5)*F4 relate to =SUM(C14:E14)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Columns don't add up in cross-checking with Rows

    They wont be the same, its a mathematical principle

    A/B*X + C/D*Y +E/F*Z = (ADFX+CBFY+EBDZ)/BDF and not (A+C+E)/(B+D+F)*(x+y+z)

  5. #5
    Registered User
    Join Date
    07-04-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Columns don't add up in cross-checking with Rows

    ...and there I was thinking I'd missed something complex...
    I must have been asleep during that maths lesson at school

    so does that mean my forecast total calculations in F14, F15 and F16 were correct?

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Columns don't add up in cross-checking with Rows

    Depends what you mean by "correct".

    They display the correct answer in terms of what you've asked them to do, but they probably don't display what you want them to. Your SUM calculation is probably what you want in those cells.

  7. #7
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Columns don't add up in cross-checking with Rows

    I agree with post #5 since it gives a consolidated answer

  8. #8
    Registered User
    Join Date
    07-04-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Columns don't add up in cross-checking with Rows

    I think you may be be correct with the SUM answer for what I am after.

    I'll leave it at that, and book myself into school for some lessons in mathematical principles.
    Or just a dark room for half an hour.

    thanks

    Steve

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1