Brady
New member
- Joined
- Oct 24, 2018
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Location
- Vancouver, BC
- Excel Version(s)
- Office 365 Sub
First time posting a question here. I read through the article on the round function https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/
So, I began adding RoundingMode.AwayFromZero to all my formulas.
I have a formula which is calculating the volume of some lumber on sales orders.
=Number.Round([Thickness]*[Width]*[Length]/12*Number.Abs([Pieces]),0,RoundingMode.AwayFromZero)
Thickness, Width and Length are always positive numbers, however the # of Pieces can sometimes be negative if it's a credit order. I want the volume to always be positive, so I put the absolute function around the number of pieces.
This works as intended, except when the number of pieces is zero the result is -0
Even if I put the Absolute function around the outside instead of inside, the results also -0
=Number.Abs(Number.Round([Thickness]*[Width]*[Length]/12*[Pieces],0,RoundingMode.AwayFromZero))
This then causes issues with an "IF" formula in Excel afterwards.... =IF(ThisResult=0,"Flag","NoFlag") -- because the result is -0 and not 0, it gives me "NoFlag"
What am I doing incorrectly?
So, I began adding RoundingMode.AwayFromZero to all my formulas.
I have a formula which is calculating the volume of some lumber on sales orders.
=Number.Round([Thickness]*[Width]*[Length]/12*Number.Abs([Pieces]),0,RoundingMode.AwayFromZero)
Thickness, Width and Length are always positive numbers, however the # of Pieces can sometimes be negative if it's a credit order. I want the volume to always be positive, so I put the absolute function around the number of pieces.
This works as intended, except when the number of pieces is zero the result is -0
Even if I put the Absolute function around the outside instead of inside, the results also -0
=Number.Abs(Number.Round([Thickness]*[Width]*[Length]/12*[Pieces],0,RoundingMode.AwayFromZero))
This then causes issues with an "IF" formula in Excel afterwards.... =IF(ThisResult=0,"Flag","NoFlag") -- because the result is -0 and not 0, it gives me "NoFlag"
What am I doing incorrectly?
Last edited: