Stop the rounding
If I am downloading static information form another software into excel, how do I get the values to stop rounding?
Ex. 3.25 hours 55.50 per hour $180.38 (STATIC Data)
3.25 hours 55.50 per hour $180.37 (into excel)
Excel either wil round up or down the value.
Last edited by KMTrombley; 03-15-2010 at 01:09 PM.
How exactly are you getting this data into Excel? Excel should not simply alter your values like that, so are you sure that the values you are importing are exactly what is displayed in your other software and that it is not in fact rounding the displayed values?
So long, and thanks for all the fish.
The data comes from an invoice report. I then perform a search on that report for specific fields and download into Excel. Once it is in Excel I would expect to be able to have the extension column perform the calculation HOURS*RATE and not round up or down the result. Even though I download the extension field I still need to supply the formula in the cell, I just don't wnat it to change from the data that was downloaded. I need to be able to provide an electronic spreadsheet to a client that matches exactly to the invoice. Any suggestions?
I do not think that excel does the rounding.
Please upload a small part of the Workbook so we can see the actual data.
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
I suspect you will find that Excel is not doing anything wrong based on the values it is actually receiving. If you check the cells that have the hours and rates in, do they actually contain 3.25 and 55.5 as values or is that merely the rounded display value? If you actually had those exact values, then Excel would display 180.38 when rounding to 2DP, but if one or both were actually slightly smaller, then Excel would (correctly) round down.
So long, and thanks for all the fish.
Table A Taken from the Download Search on an Invoice
Date Hours Rate Amount
02/08/2010 0.25 55.50 13.88
02/09/2010 0.25 55.50 13.88
02/10/2010 1.00 55.50 55.50
02/17/2010 0.25 55.50 13.88
Total 97.14
Table B Taken from the Excel spreadsheet with the Amount column having the formula HOURS*RATE and the Total cell having a Auto Sum formula.
Date Hours Rate Amount
02/08/2010 0.25 55.50 13.88
02/09/2010 0.25 55.50 13.88
02/10/2010 1.00 55.50 55.50
02/17/2010 0.25 55.50 13.88
Total 97.13
As you can see the total values do not match, but all of the Amount values do. Where is the problem?
The total on your report is adding the rounded values. The Excel total is totalling all the actual values and then being shown rounded to 2DP.
So long, and thanks for all the fish.
So how do I fix this so that Excel is matching the report?
You need your formulas to actually round the values:
=ROUND(hours*rate, 2)
then add the rounded values.
So long, and thanks for all the fish.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks