IF,AND Formula Problem evaluating

[delta_negative]

The first desired result is to evaluate cells formatted as numbers to determine if the value > **1.** and <=**6.** so that if the value is 123.40 the value of 120 will be returned. The desired result for the second part of the logical test would be to check if the value is >6 and <=1 and if so return the rounded value to the next higher increment, for instance 126.01 would return 130.Using the following formula, have checked everything but for some reason the AND portion returns false regardless of E13 cell value.

 =IF(AND(MID(E13,3,1)<=6,MID(E13,3,1)>1),E13-MID(E13,3,4),"")

Haven't yet started on the second part, don't know if it is possible in excel?Any help or suggestions greatly appreciated. Using excel 2007.

 

[Weazel]

I believe the mid's are returning text....

maybe something like

=IF(AND(--MID(E13,3,1)<=6,--MID(E13,3,1)>1),E13-MID(E13,3,4),"")

 

[delta_negative]

The first desired result is to evaluate cells formatted as numbers to determine if the value > **1.** and <=**6.** so that if the value is 123.40 the value of 120 will be returned. The desired result for the second part of the logical test would be to check if the value is >6 and <=1 and if so return the rounded value to the next higher increment, for instance 126.01 would return 130.Using the following formula, have checked everything but for some reason the AND portion returns false regardless of E13 cell value.

 =IF(AND(MID(E13,3,1)<=6,MID(E13,3,1)>1),E13-MID(E13,3,4),"")

Haven't yet started on the second part, don't know if it is possible in excel?Any help or suggestions greatly appreciated. Using excel 2007.

Am also experiencing the following result:

=MID(E13,3,1)

with cell value of 105 returns 5.

 =IF(MID(E13,3,1)>6,TRUE,FALSE)

with cell value of 105 returns TRUE. Am I using the wrong function for this operation in excel?

 

[delta_negative]

I believe the mid's are returning text....maybe something like =IF(AND(--MID(E13,3,1)<=6,--MID(E13,3,1)>1),E13-MID(E13,3,4),"")

Thanks a mint. That seems to do the trick. What does the -- do?

 

[Weazel]

ADVERTISEMENT

because the mid is returning the number as text you can't perform a math operation on it.
the double unary essentially is converting the text number to an actual number and your 'true' is then able to do the math more or less
so essentially it is coercing the trues and falses into 1's and 0's


sorry, I'm not very good at explaining it

 

[delta_negative]

because the mid is returning the number as text you can't perform a math operation on it.the double unary essentially is converting the text number to an actual number and your 'true' is then able to do the math more or lesssorry, I'm not very good at explaining it

Thanks again. Understand the explanation. Is there a better method to accomplish the same thing?

 

[Weazel]

you could try

=ROUND(E13,-1)

not sure that is exactly what you're after

 

[delta_negative]

you could try

=ROUND(E13,-1)

not sure that is exactly what you're after

Thanks for all your help.