A More Efficient Formula?

[dangerousmouse]

Hi Everyone,
How do I reduce this syntax?
I want the formula to:
If either F5 - M5 display values of 1 - 4 then show the word "Opening" in another chosen cell.
The formula works... But it seems to me that there must be a more efficient way to achieve the same goal.
Do you know how the formula might be reduced?

=IF(OR(F5=1,F5=2,F5=3,F5=4,G5=1,G5=2,G5=3,G5=4,H5=1,H5=2,H5=3,H5=4,I5=1,I5=2,I5=3,I5=4),"Opening",IF(OR(J5=1,J5=2,J5=3,J5=4,K5=1,K5=2,K5=3,K5=4,L5=1,L5=2,L5=3,L5=4,M5=1,M5=2,M5=3,M5=4),"Opening",""))

Thanks in advance, Dangerousmouse

 

[GlennUK]

How about:

=IF(MIN(F5:M5)<=4,"Opening","")

?

( assuming that you don't have zeroes or negative figures in the range )

By the way, welcome to the board

 

[dangerousmouse]

Thanks Glenn,

That's halfway there - I really wanted to express a range of cells like you have to keep the formula short ie F5:M5

However, how do I stop the word "Opening" appearing when the value of each cell is 0? ie Blank

I tried to uncheck zero values in tools > options
I've also tried to write in an ISBLANK into the IF statement but no luck

Thats why I found it necessary to individually express values of 1, 2, 3 and 4 in the formula...

Mike

 

[marka87uk]

Try this...

=IF(AND(MIN(F5:M5)<=4,MIN(F5:M5)>0),"Opening","")

 

[dangerousmouse]

Awesome!

I'm going to research the syntax you've expressed here and analyse how I can put this into action in future...

Thanks, Mike

 

[marka87uk]

Basically,

IF the MINimum number in cells F5:M5 is LESS THAN or EQUAL to 4 AND the MINimum number in cells F5:M5 is GREATER THAN 0, then show 'Opening', otherwise show nothing.