Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Round to Thousands and x-foot the sum of the total
|
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert every cell in a spreadsheet to round to millions | Excel Worksheet Functions | |||
How do i round up a price to the nearest 5,10,15 etc pence | Excel Worksheet Functions | |||
How do I format cells to round to the nearest thousands without .. | Excel Discussion (Misc queries) |