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

1. ## 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

2. ## 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. ## Re: Columns don't add up in cross-checking with Rows

how does =(F6/F5)*F4 relate to =SUM(C14:E14)

4. ## 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. ## 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. ## 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. ## Re: Columns don't add up in cross-checking with Rows

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

8. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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