Round to Thousands and x-foot the sum of the total

Jeanne · 07-08-2005, 12:05 AM

**Bryan Hessey** · 07-08-2005, 04:41 AM

Round(a1,-3) will round to the thousands,

Round((sum(a1:a39)),-3) will round the total to thousands

but I do not know what you mean by 'x-foot'

Dave O · 07-08-2005, 06:05 AM

X-foot is shorthand for "cross foot", meaning the sum should be the
same whether you add the elements horizontally or vertically. It's a
checksum process to ensure accuracy.

**Bryan Hessey** · 07-08-2005, 07:33 AM

Unfortunately I have never had much success getting Excel to admit that two numbers, albeit look-alike-numbers, were equal.
To attempt the same with 3 variations for Cross-Checking purposes could be difficult.
(the three variations being the total of the vertical sub-totals, the total of the horizontal sub-totals, and the total altogether sum(A1:D10) etc)

Assuming A1 to D10 contained data, totalled vertically at A11 to D11, and horizontally at E1 to E10

My current balance sheet effort resorts to testing as equal the Integer of the numbers, as
=IF(INT(G71)=INT(G72),"ok","An error was detected")

which for cross-checking could replace G71 with sum(A11:D11) and replace G72 with sum(E1:E10) to check data in A1 to D10, or you could try
=IF(INT(sum(A11:D11))=INT(sum(A1:D10)),"ok","An error was detected")

Hope this helps

Jeanne · 07-08-2005, 09:05 AM

Sorry, hit the enter key before completion. Dave, you are correct on your
explanation for x-foot. However, Brian's solution does not work. Here is an
example in column two of what I want to see as a result of column one:

123,456.78 123
123,456.78 123
123,456.78 123
123,456.78 123
123,456.78 123
123,456.78 123

740,740.68 738 Total of above column

Any advises are greatly appreciated. Have a great day.

Jeanne

"Dave O" wrote:

> X-foot is shorthand for "cross foot", meaning the sum should be the
> same whether you add the elements horizontally or vertically. It's a
> checksum process to ensure accuracy.
>
>

**Bryan Hessey** · 07-08-2005, 09:23 AM

try =ROUND(A1/1000,0)

if A1 has 123,456.78 it will display 123

Also, the 'total of the above column' should show as 740, and a cell display the rounding error if you require, to add the '123's up is incorrect and will probably cause problems in further calculations etc.

It is incorrect to say that 740,000 can be represented as 738 when rounded to thousands.

Still. it is your data . . . . . .

Dave O · 07-08-2005, 04:05 PM

If you use this custom format for the 123,456.78 cells
_(* #,##0,_);_(* (#,##0,);_(* "-"??_);_(@_)

.... the 123,456.78 will display as 123. Altho the number 123 is
displayed, the math will be performed on the *content* of the cell
rather than the displayed value. 6 x 123,456.78 = $740,740.68, which
would display as 741 using this format. Why represent 740,740.68 as
738?

You can derive the value 123 from 123456.78 like this:
=INT(123456.78/1000), and it will have no remainder. By rounding like
that it sure seems like you'd be missing the boat when it comes to the
456.78: in your 6 element example you'd have $2740.68 that's not
accounted for. I accept PayPal, if you need a place to send it!