MS Excel calculations and rounding - seeking concensus from GZers

Forums › Microsoft Windows › MS Excel calculations and rounding - seeking concensus from GZers

OldGeek

989 posts

Ultimate Geek
+1 received by user: 409

ID Verified

Lifetime subscriber

#304501 9-May-2023 13:46

I have a spreadsheet where rounding is involved, and rounded numbers reported are not carried forward in subsequent cell calculations. I am retired and taking on voluntary roles that have exposed me to the need to use spreadsheets - so still a relative novice but a long-time user of other Office apps.

Example:

C1 value is 94.326 and defined as a number with 3 decimal places

C2 value is 0.123000 and defined as a number with 6 decimal places

C3 value is C1*C2 - 11.60 reported but actually is 11.602098 rounded down - defined as currency with 2 decimal places

When C3 is used in subsequent formulae, should the value be based on the rounded number that Excel reports in the cell (11.60) or the actual number (11.602098)?

A follow-up question is how to ensure the rounded number is used?

--

OldGeek.

Quic referal code: https://account.quic.nz/refer/581402 and use this code for free setup: R581402E48MJA

This is a filtered page: currently showing replies marked as answers. Click here to see full discussion.

mjb

996 posts

Ultimate Geek
+1 received by user: 67

Trusted

#3074217 9-May-2023 13:52

It'll use the calculated value, not the "display" value. If you want to use the rounded number, use the ROUND() formula in the cell calculation.

contentsofsignaturemaysettleduringshipping

mjb

996 posts

Ultimate Geek
+1 received by user: 67

Trusted

#3074227 9-May-2023 14:22

OldGeek:

mjb:

It'll use the calculated value, not the "display" value. If you want to use the rounded number, use the ROUND() formula in the cell calculation.

Thanks but the affected cells already have a formula: so my question is how to add the ROUND function to an existing formula. For example, Cell C3 has this formula: =IF(B9<>0,B9*I9,"") - can I add in a ROUND statement? or do I have to use another cell with a ROUND statement referencing C3

Absolutely. You'd want:

=IF(B9<>0,ROUND(B9*I9,2),"")

You can nest expressions within expressions - as long as the ultimate result of an inner expression is in the form that the outer expression can use. In this case, IF() is expecting a value of any type in the second argument, so the result of ROUND() will work fine. The first argument (B9<>0) is expected to be an expression that evaluates to a True or False . However, this can actually be returned from many different types of expression results (integers, booleans, strings, etc), but that's a bit out of scope for your current question.

contentsofsignaturemaysettleduringshipping