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

B

Bryan Hessey

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'
 
D

Dave O

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.
 
B

Bryan Hessey

Unfortunately I have never had much success getting Excel to admit tha
two numbers, albeit look-alike-numbers, were equal.
To attempt the same with 3 variations for Cross-Checking purposes coul
be difficult.
(the three variations being the total of the vertical sub-totals, th
total of the horizontal sub-totals, and the total altogethe
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 Intege
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) an
replace G72 with sum(E1:E10) to check data in A1 to D10, or you coul
try
=IF(INT(sum(A11:D11))=INT(sum(A1:D10)),"ok","An error was detected")

Hope this help
 
J

Jeanne

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
 
B

Bryan Hessey

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 cel
display the rounding error if you require, to add the '123's up i
incorrect and will probably cause problems in further calculation
etc.

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

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

Dave O

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!
 
Top