Help with Formula

[chefmike]

Hi Everyone,

I have a formula and not sure why it is not working properly, was hopeing
someone could help me with it?

the formula is
=IF(OR(M7>=53,M7<=7),0,IF(OR(M7>=8,M7<=22),15,IF(OR(M7>=23,M7<=37),30,IF(OR(M7>=38,M7<=52),45,FALSE))))

The problem is if M7 is say 57 it works fine, if M7 is 7 it works fine, if
M7 is 9 it works fine, if M7 is 23 it doesn't work it gives an answer of 15
(should be 30), any number above 23 to 53 it gives an answer of 15. What am
I doing wrong in this formula? Am I using the right formula and if so how
come it is not reading the entire formula to produce the right answer?

I have tried the Evaluate Formula Auditing Box and it shows me that when it
reaches M7<=7, it is a True statement.

Thanks in advance for all help

mike

 

[Lars-Åke Aspelin]

Hi Everyone,

I have a formula and not sure why it is not working properly, was hopeing
someone could help me with it?

the formula is:
=IF(OR(M7>=53,M7<=7),0,IF(OR(M7>=8,M7<=22),15,IF(OR(M7>=23,M7<=37),30,IF(OR(M7>=38,M7<=52),45,FALSE))))

The problem is if M7 is say 57 it works fine, if M7 is 7 it works fine, if
M7 is 9 it works fine, if M7 is 23 it doesn't work it gives an answer of 15
(should be 30), any number above 23 to 53 it gives an answer of 15. What am
I doing wrong in this formula? Am I using the right formula and if so how
come it is not reading the entire formula to produce the right answer?

I have tried the Evaluate Formula Auditing Box and it shows me that when it
reaches M7<=7, it is a True statement.

Thanks in advance for all help

mike

Is this what you want?

=IF(OR(M7>=53,M7<=7),0,IF(AND(M7>=8,M7<=22),15,IF(AND(M7>=23,M7<=37),30,IF(AND(M7>=38,M7<=52),45,FALSE))))

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Is this what you want?

=IF(OR(M7>=53,M7<=7),0,IF(AND(M7>=8,M7<=22),15,IF(AND(M7>=23,M7<=37),30,IF(AND(M7>=38,M7<=52),45,FALSE))))

Hope this helps / Lars-Åke

And this can be written more compact, like

=IF(M7<8,0,IF(M7<23,15,IF(M7<38,30,IF(M7<53,45,0))))

/ Lars-Åke

 

[Lars-Åke Aspelin]

Hi Everyone,

I have a formula and not sure why it is not working properly, was hopeing
someone could help me with it?

the formula is:
=IF(OR(M7>=53,M7<=7),0,IF(OR(M7>=8,M7<=22),15,IF(OR(M7>=23,M7<=37),30,IF(OR(M7>=38,M7<=52),45,FALSE))))

The problem is if M7 is say 57 it works fine, if M7 is 7 it works fine, if
M7 is 9 it works fine, if M7 is 23 it doesn't work it gives an answer of 15
(should be 30), any number above 23 to 53 it gives an answer of 15. What am
I doing wrong in this formula? Am I using the right formula and if so how
come it is not reading the entire formula to produce the right answer?

I have tried the Evaluate Formula Auditing Box and it shows me that when it
reaches M7<=7, it is a True statement.

Thanks in advance for all help

mike

The reason why your formula does not work is that the second OR, i.e.
OR(M7>=8,M7<=22)
is always true. M7 is always "greater than or equal to" 8 OR "less
than or equal to" 22 .

That means that the third and fourth OR in this formula will never be
evaluated as the second OR always yields TRUE.

/ Lars-Åke

 

[chefmike]

Hi Lars-Ã…ke,

Thanks for correcting the formula it works great now, and thanks for
answering why my formula didn't work that information makes a lot of sense
now that you explained it to me. I tried looking in the help menu, but was
lost at why it didn't work.

Thanks again for the help and information

mike