How to round a difference (vs sum) in excel?

June 10, 2021 11:06 AM

How do you round a difference in excel?

I know how to round a sum in excel. For example, to round to two decimal places: =ROUND(SUM(H5;H17),2)
But what is the formula for rounding a difference? (For example, =E7-F7). Would it be something like: =ROUND(DIFFERENCE(E7;F7)2)?? Except, I don't think "DIFFERENCE" is the right term. I know how to round it so that the rounded number will will appear in the box next to the difference, but I'd like it to show in the actual difference box.
(Hope my question makes sense!)

posted by SageTrail to Computers & Internet (12 answers total)

= round(e7-f7) will work just fine. You can also set excel to just display two decimal points, depending on why you need it rounded.
posted by jeather at 11:12 AM on June 10, 2021 [7 favorites]

So the format is =Round(thing to round, digits). So the difference would be =round(e7-f7,2) or for clarity (but not necessary) =round( (e7-f7), 2). You only the = at the very beginning to tell it that is an equation and not text.
posted by metahawk at 11:13 AM on June 10, 2021 [2 favorites]

=ROUND(E7-F7, 2) will do it. In general you can just write +, -, *, / for the basic arithmetic operations; you don't have to use SUM unless you want to add up a lot of things.
posted by madcaptenor at 11:13 AM on June 10, 2021 [1 favorite]

You might want to include ABS for absolute value because, in contrast to + or SUM, subtraction is not "commutative": =(E7 - F7) is not the same as =(F7 - E7). Thus
=round(abs(e7 - f7),2)
otherwise you'll get some negative and some positive numbers for the difference.
Hope helps.
posted by BobTheScientist at 11:55 AM on June 10, 2021 [2 favorites]

If you want to put more than one thing in the same cell, you can concatenate them with '&'
This can be text or numbers or expressions:
="this" & e7 & "that"
posted by Lanark at 12:35 PM on June 10, 2021 [1 favorite]

I'd just like to echo jeather that there's a good chance you don't actually want to round your output, you just want to show it as 2 decimal places, which you can totally have Excel do. But if you definitely want to round the output then yeah you just put ROUND() around whatever other formula you've done. You can do =ROUND(8.631234,2) or =ROUND(6.5*12.7,2) or =ROUND(E7/E12,2) or whatever.
posted by brainmouse at 1:01 PM on June 10, 2021

Response by poster: I need to round it so that all the totals are rounded accurately to 2 decimal places.
For example:
12.50/1.083=11.54201
156.00/1.083=144.04
11.54201 + 144.04 = 155.5863
I need a formula that will round up the sum 155.5863 to 155.59 (and one that will round up each of the division formula sums).
If I simply format all the cells to 2 decimal places, I get an incorrect sum of 155.58
posted by SageTrail at 5:36 PM on June 10, 2021

Formatting to 2 decimal places should work. In your example, 11.54201 + 144.04 = 155.58201, which correctly rounds to 155.58 using formatting. In general you should only use formatting, so you don’t lose precision along the way in your calculations (like you would if you rounded the intermediate steps).
posted by doctord at 5:58 PM on June 10, 2021 [1 favorite]

It's kind of like significant figures. If you keep the base formulas and format the cells to 2 decimal places, it works and you get 155.59. If you round after each division, Excel will drop or lose the trailing figures after the first 2 decimals, so you will get 158.58.

calculations with all decimal places
12.5/1.083 = 11.54201293
+
156/1.083 = 144.0443213
155.5863343
formatted to show 2 dec = 155.59

round after each calculation:
12.5/1.083 = 11.54
+
156/1.083 = 144.04
set exactly to 155.58
posted by mephisjo at 6:29 PM on June 10, 2021 [1 favorite]

Again echoing that you have the errors backwards and you definitely do not want to round. Showing fewer decimal places correctly rounds the results without changing them.
posted by brainmouse at 10:26 PM on June 10, 2021 [1 favorite]

Response by poster: Here is what worked:

12.50 is in cell E8. Formula that worked (which I placed in cell E9) is: =ROUND(E8/1.083,2). Answer of 11.54 appears in cell E9.

156.00 is in cell E10. Formula that worked (which I placed in cell E11) is: =ROUND(E10/1.083,2). Answer of 144.04 appears in cell E11.

Thank you all for your help!
posted by SageTrail at 5:02 PM on June 13, 2021

@SageTrail if you now change the number 12.50 to 12.1 you will find that totals to 155.216989843029
which your early rounding will display as 155.21 instead of 155.22

If you ever round a number before the final sum, you are going to lose accuracy.
posted by Lanark at 7:41 AM on June 18, 2021