Excel problem

[kevinlobbbigpondcom]

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

[#2]

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

[#3]

@kevinlobbbigpondcom 

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.

[#4]

@kevinlobbbigpondcom 

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.

[#5]

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

[#6]

Thanks for your response Peter. The formula worked good. I must learn more about the different brackets.

[#7]

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

[#8]

@kevinlobbbigpondcom 

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.

[#9]

@kevinlobbbigpondcom 

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

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

[#10]

@Patrick2788 

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.

[#11]

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

[#12]

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