Rounding Alerts

[rencavalier]

Looking for a way to solve for an issue using an Excel formula.

In a table, the values used are often presented as a rounded value. When applied to a sum of a group of numbers, I am looking for a way to set up a flag/alert indicating whether the final value is rounded vs. exact. I figure this can be set up in Conditional Formatting, but cannot come up with the Syntax.

 

[thisoldman]

For Excel 2013 and later, try this formula;

=ISNUMBER(SEARCH("=round(", FORMULATEXT(A2)))

 

[V_Malkoti]

I don't think those cells are actually rounded. In it the format of the cell is set to display limited number of digits after decimal places. So if you click on the cell in the table, you'll actually see the full value in formula bar.

That said, if the table is showing value upto 2 decimal places, then comparing the cell's value with that same value with round function should do the trick.

For example if your table data in A1:B9 (including headers), then select the data portion of the table (A2:B9) and create conditional formatting rule with formula:

=ROUND(A2, 2)<>A2

and some formatting to mark those rounded cell values.