Check if decimal/float value is between two values

[vanclute]

Hi all, apologies if this has been answered as the forum software won't allow searches for the word "between"!

I need to check if a monetary value (2 decimal places) is between two numbers. I've been using the usual if(and()) approach but it's sooooo clunky. I found the MEDIAN approach and like it, but it only seems to work for integers.

Is there a tidy way to evaluate if a decimal value is between two other (potentially also decimal) values?

Thanks!

EDIT: I may have been mistaken about the MEDIAN approach only supporting integers, I think I just did it wrong. Still I'd be interested to know if anyone has any other clever solutions to this.

 

[Rick Rothstein]

Hi all, apologies if this has been answered as the forum software won't allow searches for the word "between"!

I need to check if a monetary value (2 decimal places) is between two numbers. I've been using the usual if(and()) approach but it's sooooo clunky. I found the MEDIAN approach and like it, but it only seems to work for integers.

Is there a tidy way to evaluate if a decimal value is between two other (potentially also decimal) values?

EDIT: I may have been mistaken about the MEDIAN approach only supporting integers, I think I just did it wrong. Still I'd be interested to know if anyone has any other clever solutions to this.

Assuming two decimal point accuracy, Round all values to two point accuracy in the formula. For example...

=MEDIAN(ROUND(A1,2),ROUND(B1,2),ROUND(C1,2))=ROUND(B1,2)

 

[vanclute]

Thanks for that, though I don't think it's any shorter or more efficient than using the "and" method or my current MEDIAN approach, which is:

=IF([@plRPM]=MEDIAN([@plRPM],$O$2,$O$3),TRUE,FALSE)

I'm noticing however that my use of this calculation on a fairly large spreadsheet, is quite slow. Wondering if there's a faster solution.

 

[RCBricker]

wrong post