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

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'


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=385486

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.

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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=385486

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.

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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=385486

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!