# Excel problem

Copper Contributor

# 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

11 Replies

# Re: Excel problem

=IF(AND(A1>3,A1<10),"A","B")
This works in my sheet if the value is entered in cell A1.

# Re: Excel problem

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.

# Re: Excel problem

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.

# Re: Excel problem

Thanks Oliver. Appreciate your response. That works on my sheet too.

# Re: Excel problem

Bit beyond me Peter. I am still working on wriggly brackets but I will certainly give it a try.

# Re: Excel problem

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.

# Re: Excel problem

With the sample data you provided, you could keep it simple with MEDIAN:

``=IF(B3=MEDIAN(B1:B3),"A","B")``

# Re: Excel problem

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.

# Re: Excel problem

Indeed. I've been accused of offering 'complex solutions' to simple problems so I must not indulge at each opportunity!

# Re: Excel problem

Big grin! Surely not, how could anyone accuse us of that!