# IF,AND Formula Problem evaluating

#### delta_negative

##### New Member
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.
Code:
=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.

Last edited:

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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),"")

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.
Code:
=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:
Code:
=MID(E13,3,1)
with cell value of 105 returns 5.
Code:
=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?

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?

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

Last edited:
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?

you could try

=ROUND(E13,-1)

not sure that is exactly what you're after

you could try

=ROUND(E13,-1)

not sure that is exactly what you're after

Replies
7
Views
202
Replies
2
Views
179
Replies
6
Views
383
Replies
2
Views
510
Replies
2
Views
233

1,196,507
Messages
6,015,604
Members
441,905
Latest member
Jean207

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back