Need Formula if all numbers are in the 50th

[serge]

I need a formula that would return 1 if the 5 numbers in a range are all in the 50th.

A1:AC1 : ( spread across according to my other formula ) 51,52,54,55,57.
So I would like in cell : AD1 to return 1.

A2:AC2 : 49,57,58,59,61.
So I would like in cell :AD2 to return blank cell.

Thank you.

 

[T. Valko]

I need a formula that would return 1 if the 5 numbers in a range are all in the 50th.

A1:AC1 : ( spread across according to my other formula ) 51,52,54,55,57.
So I would like in cell : AD1 to return 1.

A2:AC2 : 49,57,58,59,61.
So I would like in cell :AD2 to return blank cell.

Thank you.

Are you saying that within the range A1:AC1 (29 cells) there will be 5 numeric entries and you want to know if all 5 of them are >=50 and <=59?

If that's what you want...are there any numeric values in any of the other cells within the range?

Can you be more specific as to what cells contain these numbers?

 

[jasonb75]

Maybe

=IF(AND(MIN(A1:AC1)>49,MAX(A1:AC1)<60),1,"")

As already pointed out, you're asking for values to be referenced from 5 out of 29 cells, so this could quite easily fail if any of the remaining 24 cells hold numeric values, which would include dates or times.

 

[serge]

Thanks for answering,

Actually you can disregard the range I posted before and just use this simple one, I would change my set up.

A1:E1 51,52,54,55,57. If those 5 numbers are within the 50th so the formula should return 1

A2:E2 49,57,58,59,61. So 49 and 61 are not within the 50th so the formula should return a blank cell.

Thank you.
I hope that is better !!

 

[mikerickson]

This should work

=IF(SUMPRODUCT(--(50<=A1:E1),--(A1:E1<=59))=5, 1, "")

 

[serge]

Thank you Jason,

No, it would be always only 5 numbers in those range.

And your Formula work perfectly, I tried it on 100 range and it's fine.

Thank you Jason for your help.

 

[serge]

Thank you Mike, your formula work fine too, appreciate the help.

Regards... Serge.

 

[T. Valko]

Thanks for answering,

Actually you can disregard the range I posted before and just use this simple one, I would change my set up.

A1:E1 51,52,54,55,57. If those 5 numbers are within the 50th so the formula should return 1

A2:E2 49,57,58,59,61. So 49 and 61 are not within the 50th so the formula should return a blank cell.

Thank you.
I hope that is better !!

OK, try one of these array formulas**.

=IF(AND(A1:E1>=50,A1:E1<60),1,"")

If the numbers will always be integers (whole numbers):

=IF(AND(A1:E1>49,A1:E1<60),1,"")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[serge]

Thank you #NAME?,

Your formulas works fine too.

Regards... Serge.

 

[T. Valko]

Thank you #NAME?,

Your formulas works fine too.

Regards... Serge.

You're welcome. Thanks for the feedback!