SOLVED

# Rounding Error Introduced by ABS?

Copper Contributor

# Rounding Error Introduced by ABS?

Hello,

I wrote a formula that takes the percentage difference between two numbers, if that percentage is 0% it displays a 3, if it is <=0.5% it displays a 2, if it is neither of those it displays a 1. To deal with negative percentages I used ABS().

The formula is as follows:

=IFERROR(SWITCH(ABS('Cost Class QA'!AO13), 0, 3, (ABS('Cost Class QA'!AO13)<= 0.05), 2, 1), "-")

The value of 'Cost Class QA'!AO13 is 0.45% or 0.0045 but it displays as a 1.

The cell is highlighted in yellow.

Is this some sort of floating point/rounding error introduced by the formula?

4 Replies

# Re: Rounding Error Introduced by ABS?

=IFERROR(SWITCH(TRUE,ABS('Cost Class QA'!AO13)=0,3,ABS('Cost Class QA'!AO13) <= 0.05, 2, 1), "-")

This formula returns the intended result in my sheet.

best response confirmed by Louis_Wells (Copper Contributor)
Solution

# Re: Rounding Error Introduced by ABS?

SWITCH is not the correct function to use here.

Use this formula:

=IFERROR(IF(ABS('Cost Class QA'!AO13)=0, 3, IF(ABS('Cost Class QA'!AO13)<= 0.05, 2, 1)), "-")

# Re: Rounding Error Introduced by ABS?

doesn't switch reduce the amount of functions in the forumla?

# Re: Rounding Error Introduced by ABS?

did some googling and answered my own question.

"SWITCH is limited to exact matching. It is not possible to use operators like greater than (>) or less than (<)"

Thank you for the formula and explanation :)
1 best response

Accepted Solutions
best response confirmed by Louis_Wells (Copper Contributor)
Solution

# Re: Rounding Error Introduced by ABS?

SWITCH is not the correct function to use here.

Use this formula:

=IFERROR(IF(ABS('Cost Class QA'!AO13)=0, 3, IF(ABS('Cost Class QA'!AO13)<= 0.05, 2, 1)), "-")