Rounding Function Variation 1

[VincentCrimmins]

Hi,

I have a set of significant values: 8, 8.5, 9, 9.5,etc and I want to flag when a particular value, to four decimal places, is within a 0.1 range of these sigificant values.

I have a table that lists the significant value increments, in this case 0.5, and the within range value, in this case 0.1.

For example, if I have a value of 8.4251, its within 0.1 of 8.5, so I want to flag it.

I don't want to make it static, as I have a number of different series with differing increment values and within range values. Is it possible to take the 8.4251 and round it to 8.5 and 8 and then say if its within 0.1 of either?

Thanks,

 

[mintjulep]

Is it possible to take the 8.4251 and round it to 8.5 and 8 and then say if its within 0.1 of either?

No.

The ROUND function returns a value. A brand new value. There is no information in that new value about what the number was before rounding.

 

[mintjulep]

Array formula solution:

=IF(OR(ABS(A$10-A1:A7)<=0.1),TRUE,FALSE)

Where A1:A7 are you list of "significant values" and A10 is the number you want to check.

 

[DougAJ4]

I suggest:

=ROUND(MIN(MOD(C1,$A$1),$A$1-MOD(C1,$A$1)),10)<=$A$2

where:
A1 = significant value increment
A2 = tolerance
C1 = number to be checked

the ROUND(...,10) is to avoid floating point problems

 

[VincentCrimmins]

Hi Doug,

That's perfect, thanks a lot!