Need to create formula wich will reduce random rounding. I need to explain further
My spreadsheet has several lines each of which has a mixture of data and formulas. At the end of the line going across, is the final formula. However, most times, the result is rounded up or down when compared to the same information entered in an accounting programme. I am using SUM formulas at that moment.
Last edited by Karen@TSCL; 07-03-2012 at 03:22 PM. Reason: This has been solved thanks to ChemistB.
Sorry, what is your question?
Do the =ROUNDUP( or =ROUNDDOWN( or = MROUND( formulae help?
<--- Please click the star to say thank you
From what I have read about ROUNDUP, etc, these are for whole numbers. My spreadsheet figures show 2 decimal places. I tried EVEN but that also does whole numbers.
Hi, are you need to reduce the decimal places or trying to round off.
Could you explain your problem clearly.
try
Please Login or Register to view this content.
MMctague, that format is new to me. How do I add it or amend this formula to include your suggestion? Here is a sample of the formula =SUM(G1:M1)*F1+N1+O1+SUM((G2:M2)*F2+N2+O2. Each result comes from a multiple of 2 lines. Hence it is either 2 lines or 4 or 6, etc. The sample above picks up 2 lines
No, ROUNDUP works just as ROUND does except ROUND uses 0.5 as a breakpoint. For ROUNDUP, for example, =ROUNDUP(A1,2) if A1 contains 2.25001.... to 2.259999... it will round to 2.26From what I have read about ROUNDUP, etc, these are for whole numbers.
MROUND can be used to set specific endpoints =MROUND(A1,0.05) rounds the value in A1 to the nearest 0.05.
Hope that helps.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Most likely, the accounting program is rounding or the inputs are being rounded. Increase the number of digits the results are displayed as to see extra digits (up to 15 places). Excel uses all digits whether you have it formatted to display or not. That would be why you want to ROUND.However, most times, the result is rounded up or down when compared to the same information entered in an accounting programme.
Chemist, based on the sample formula I provided, can you give me an example of how I would incorporate ROUNDUP to get the desired result?
Banuchander, please read the reply I sent to MMctague where I provided a sample formula. What keeps happening is that result I see in Excel sometimes differs by a cent or two (sometimes more) from what the accounting programme produces. It may seem like a minor difference but when you have to do about 200 of those transactions, it does make a difference.
Depends on where the discrepancy is coming from. If for example, you're showing digits to 1 decimal place and G1:M1 actually equal 2.14, 2.12, 2.23, 2.32, 2.44, 2.43, 2.34, then SUM(G1:M1) will equal 16.0 whereas your accounting program where values are being entered to 1 decimal only (2.1, 2.1, 2.2, 2.3, 2.4, 2.4, 2.3) will give 15.8. ROUNDUP will actually take the value further from your Accounting program. :-/
Can you upload your spreadsheet (Go Advanced>Manage Attachments) so we can determine that?
Chemist, I see what you are saying. However, on the spreadsheet, the decimals are formatted to 2 decimal places. It will take me a while to upload the spreadsheet because I will need to make it generic for the sake of privacy. I may not be able to do this until later today or tomorrow.
Last edited by Karen@TSCL; 06-27-2012 at 03:04 PM. Reason: typo errors
Another option, Change your spreadsheet to "Precison as displayed"
Options>Advanced>"When Calculating this workbook" > check "Set Precision as Displayed"
Did that fix your problem?
Chemist, I checked the box which is "set precision as displayed" and now see a warning which says "Data will permanently lose accuracy". Can you tell what does that mean overall? How will that affect my figures? In what way will the accuracy be compromised?
Last edited by Karen@TSCL; 06-27-2012 at 03:38 PM. Reason: added two more questions to the reply
If the values are not part of a formula, (i.e. you copied from someplace else a value of 3.12005), you will forever lose that last 005 if you are showing 2 digits. To be safe, save it under a different name first to see if you're happy with the result.
Chemist, I have saved the document as a new file and done the precision thingie. However, I cannot see any difference-no better, no worse. The rounding may be needed in one of the formulas in one of the cells in the ranges. What do you think? I am not familiar with creating formulas using ROUND. May I have some assistance with amending this formula to include ROUND? Here is the formula =SUM(P2*0.05). By the way, I should give you the final formula which is =P2-(Q2+R2+S2+T2+U2+V2).
Last edited by Karen@TSCL; 06-27-2012 at 04:29 PM. Reason: added another sample formula
You would want to round last so it would be
=ROUND(SUM(P1*0.05),2) assuming you're rounding to 2 decimal places.
Hope that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks