MS Excel calculations and rounding - seeking concensus from GZers

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

OldGeek

893 posts

Ultimate Geek

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.

Voyager referral code: https://refer.voyager.nz/6XQR2QG9Q

mjb

996 posts

Ultimate Geek

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

OldGeek

893 posts

Ultimate Geek

ID Verified

Lifetime subscriber

#3074220 9-May-2023 14:04

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

--

OldGeek.

Voyager referral code: https://refer.voyager.nz/6XQR2QG9Q

duckDecoy

896 posts

Ultimate Geek

Subscriber

#3074226 9-May-2023 14:20

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

You can nest as many formulas as you want For example

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

Or you could try it the other way around:

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

THIS WILL FAIL because you cannot round "" as it is not a number

It seems like the formula is wanting to hide zeros rather than necessarily turn them into zero length strings? If so this formula doesn't work anyway, what if B9*I9 is less than 0.5? It would round to 0 rather than become ""

What would be better is to do something like:

Round(B9*I9,2)

This will display 0 if the value was zero. Now you can additionally apply formatting to those cells that do the job of hiding 0, the 0 is still there but you can show it as an empty cell.

Highlight the cells you want to do this to
Format > Format Cells.
Click Number > Custom.
In the Type box, type 0;-0;;@, and then click OK.

It is tempting as a beginner to get rid of zeros by converting them to "" but this is normally a very bad idea. It can screw up other calcs, e.g. averaging across a bunch of numbers but someone has replaced the 0 with "", now the average might not work.

mjb

996 posts

Ultimate Geek

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

OldGeek

893 posts

Ultimate Geek

ID Verified

Lifetime subscriber

#3074228 9-May-2023 14:23

I have answered this question - the ROUND function can be used in place of a direct reference to the cell. From the example used earlier, the new formula is =IF(B9<>0,ROUND(B9,2)*ROUND(I9,2),"").

--

OldGeek.

Voyager referral code: https://refer.voyager.nz/6XQR2QG9Q

OldGeek

893 posts

Ultimate Geek

ID Verified

Lifetime subscriber

#3074229 9-May-2023 14:25

mjb:

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.

Thanks - even better.

--

OldGeek.

Voyager referral code: https://refer.voyager.nz/6XQR2QG9Q

mjb

996 posts

Ultimate Geek

Trusted

#3074230 9-May-2023 14:31

OldGeek:

From the example used earlier, the new formula is =IF(B9<>0,ROUND(B9,2)*ROUND(I9,2),"").

While this will work, it falls apart when the cells you're rounding end up as two values that when mulitplied give a result with more than 2 decimal places...

As mentioned in my previous reply, "expressions within expressions" applies here.. at a more basic level, ROUND() is expecting two arguments which can be expressions - in your case you'll use "B9*I9" as one expression, and the constant value "2" as the second.

contentsofsignaturemaysettleduringshipping

Samsung

Shop now on Samsung phones, tablets, TVs and more (affiliate link).

shk292

2853 posts

Uber Geek

Lifetime subscriber

#3074285 9-May-2023 16:27

It's best practice not to round intermediate answers, but only to round the final answer to the required level of precision. Otherwise, repeated rounding in interim stages can introduce unnecessary and unexpected errors.

Behodar

10503 posts

Uber Geek

Trusted

Lifetime subscriber

#3074294 9-May-2023 16:55

shk292:

It's best practice not to round intermediate answers, but only to round the final answer to the required level of precision. Otherwise, repeated rounding in interim stages can introduce unnecessary and unexpected errors.

Even then you likely don't want to actually round the final answer, but rather just format it to display with e.g. 2 decimal places.

shk292

2853 posts

Uber Geek

Lifetime subscriber

#3074296 9-May-2023 17:10

Behodar:

Even then you likely don't want to actually round the final answer, but rather just format it to display with e.g. 2 decimal places.

Agreed, providing the display format is rounded - eg 4.866 displayed as 4.87 if 3 SF

johno1234

2797 posts

Uber Geek

#3074333 9-May-2023 19:34

shk292:
It's best practice not to round intermediate answers, but only to round the final answer to the required level of precision. Otherwise, repeated rounding in interim stages can introduce unnecessary and unexpected errors.

However in business transactions, detail lines typically are rounded (to one cent) before being summed into a total. Otherwise your rounded total is not equal to the sum of the detail lines.

OldGeek

893 posts

Ultimate Geek

ID Verified

Lifetime subscriber

#3074337 9-May-2023 19:49

johno1234:
However in business transactions, detail lines typically are rounded (to one cent) before being summed into a total. Otherwise your rounded total is not equal to the sum of the detail lines.

Correct. My original spreadsheet was to compare electricity billing by entering meter readings (to 3 decimals of a kwh) off the bill along with appropriate rates per kwh (to 3 decimals of a cent) and daily line billing, to compare the sum of calculated billing to actual. The calculated billing result was often 1c under or over the actual bill, even though each element (ie off-peak, peak, daily rate and GST amounts) reconciled. Since I have introduced ROUND in the calculations of off-peak, peak (and GST) amounts this problem has been eliminated.

--

OldGeek.

Voyager referral code: https://refer.voyager.nz/6XQR2QG9Q

pdh

339 posts

Ultimate Geek

#3075815 13-May-2023 15:33

If it were me...

I'd use

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

That way there's no issue with the small number of zero results which would by-pass the test to replace a zero answer with "" (blank).