Rounding Formula not working, please help.

[dtiemeyer]

I have a highly customized panel schedule. We use the ((round(xxxx) *100) / 100) trick to get 2 decimal places of rounding in many locations.

In this example it produces the expected results:

((round((K Estimated Demand / 1 W) * 100)) / 100)

However in this example, it rounds to zero decimal places, and I need it to be two:

(((round((((M Estimated Demand - M Connected) * 4) / 1 W) * 1.25) * 100)) / 100)

Here's the original of that formula with no rounding:

(((M Estimated Demand - M Connected) * 4) / 1 W) * 1.25

Any thoughts?

[Gary_J_Orr · Accepted solution]

You have a little typo in your example of "the trick", but, as is usually the case when I run into these things, you have a misplaced grouping. When I have similar issues I simply go back to my algebra days: "show your work", although in this case it's about developing the function one step at a time:

the template (multiply the number by 100, round the result, then divide all of that by 100):

#### * 100
round(#### * 100)
(round(#### * 100)) / 100

Your working formula follows that pattern:

K Estimated Demand / 1 W
(K Estimated Demand / 1 W) * 100
round((K Estimated Demand / 1 W) * 100)
(round((K Estimated Demand / 1 W) * 100)) / 100

Following that same pattern with the erroneous formula gets you to a working version:

M Estimated Demand - M Connected
(M Estimated Demand - M Connected) * 4
((M Estimated Demand - M Connected) * 4) / 1 W
(((M Estimated Demand - M Connected) * 4) / 1 W) * 1.25
((((M Estimated Demand - M Connected) * 4) / 1 W) * 1.25) * 100
round(((((M Estimated Demand - M Connected) * 4) / 1 W) * 1.25) * 100)
(round(((((M Estimated Demand - M Connected) * 4) / 1 W) * 1.25) * 100)) / 100

-G

[ToanDN]

Wrong number of parentheses between left and right

[dtiemeyer]

Thank you Gary, and Thanks for "Showing your work", as I'm sure it will help others who come across this topic in the future. (and me for a few more like this I need to fix up!) Cheers!