# Need to create formula wich will reduce random rounding. I need to explain further

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

2. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

Do the =ROUNDUP( or =ROUNDDOWN( or = MROUND( formulae help?

3. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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.

4. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

Hi, are you need to reduce the decimal places or trying to round off.
Could you explain your problem clearly.

5. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

try
``Please Login or Register  to view this content.``

6. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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

7. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

From what I have read about ROUNDUP, etc, these are for whole numbers.
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.26
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.

8. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

However, most times, the result is rounded up or down when compared to the same information entered in an accounting programme.
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.

9. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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?

10. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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.

11. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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

12. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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.

13. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

Options>Advanced>"When Calculating this workbook" > check "Set Precision as Displayed"

14. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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?

15. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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.

16. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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

17. ## Re: Need to create formula wich will reduce random rounding. I need to explain further

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

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1