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

[Karen@TSCL]

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.

[Peter.M]

Sorry, what is your question?

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

[Karen@TSCL]

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.

[Banuchander]

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

[mmctague]

try

Please Login or Register  to view this content.

[Karen@TSCL]

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

[ChemistB]

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.

[ChemistB]

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.

[Karen@TSCL]

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?

[Karen@TSCL]

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.

[ChemistB]

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?

[Karen@TSCL]

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.

[ChemistB]

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?

[Karen@TSCL]

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?

[ChemistB]

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.

[Karen@TSCL]

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

[ChemistB]

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.