Forum Discussion
Excel problem
I am trying to use an IF formula to tell me if a number is greater than and less than two numbers.
For example: minimum number is 3, maximum number is 10. Question is, is 7 between these two numbers.
I am trying to use IF(7>3,"A",IF(7<10,"A","B")) but the formula returns "A" even if the number is larger than 10
With the sample data you provided, you could keep it simple with MEDIAN:
=IF(B3=MEDIAN(B1:B3),"A","B")In part the difference between our formulas is that yours applies to the single input value specified by the OP whereas I went for an array of inputs. Comparing like with like we might have
= IF( MAP(input, LAMBDA(value, MEDIAN(value, bounds) = value)), "A", "B" ) = IF( BYROW(input<TOROW(bounds), XOR), "A", "B" )which are somewhat similar.
An interesting difference is that your MEDIAN formula assumes a closed range whereas I have one closed bound and one open.
- Indeed. I've been accused of offering 'complex solutions' to simple problems so I must not indulge at each opportunity!
A touch more on the wild side, using the insider beta version of Excel
= IF( BYROW(value<{3,10}, XOR), "A", "B")where value may be a column array.
- Bit beyond me Peter. I am still working on wriggly brackets but I will certainly give it a try.
The 'wriggly brackets' are simply Excel's way of defining an array constant. If you type
={1,2;3,4}
into a cell you will see
Conversely, were you to type
= SEQUENCE(2, 2)
into a cell, it would evaluate to give the same spilt range.
Then, were you to select the expression in the formula bar and press F9, the formula is evaluated and the array constant is displayed as its result.
Your formula determines that 7>3 and returns "A". It never looks as the 10.
A variant that I like is
= IF(XOR(A1<{3,10}), "A", "B")which evaluates an array of two Booleans and then determines whether only one is true.
- Thanks for your response Peter. The formula worked good. I must learn more about the different brackets.
- =IF(AND(A1>3,A1<10),"A","B")
This works in my sheet if the value is entered in cell A1.- Thanks Oliver. Appreciate your response. That works on my sheet too.
