Using "if" and "or" together

[pboost1]

Hello everybody. I am trying to write a formula to do something simple, I'm just having trouble writing it.

The formula I am trying to write is going in column I, and looks at cloumn A.

I want the cell in column I to display one of four values if a certain number in column A is displayed.

somehing along the lines of this:

if(a2=(1:28 or 58:117or145:174),"03X05", if(a2=(29:45 or 118:132,)"03X10").

Any help would be greatly appreciated. Thank you in advance.

[peterjuhnke]

Please attach a copy for us to see
Regards
Peter

[pboost1]

Here is a sample spreadsheet of what I am trying to do.

[arthurbr]

=if(or(a2="1:28",a2="58:117",a2="145:174"),"03X05", if(or(a2="29:45",a2="118:132",)"03X10",""))

Assuming all your test values are strings

[MarvinP]

You are close with what you have.
The following works for me. It is BLANK if no matches. NOTE - you must format column A cells as TEXT for this to work, otherwise Excel thinks they are times.

Please Login or Register  to view this content.

If you meant the cells in column A to be time then this doesn't work.

[pboost1]

Dear Arthurbr,

I copied your formula to my sample spreadsheet and it is telling me "The formula you typed contains an error". Is there something wrong, or am I missing something?

[pboost1]

Dear MarvinP,

I copied your formula to a different cell and all I am getting is a blank cell. I have formatted column A as text as per your instructions. I even took it a step further and formatted column A as numbers and am still getting a blank cell. I am using Excel 2003 SP3 if that makes a difference. And no, I do not mean for the values to be as time.

[arthurbr]

There was a typo in my formula.
But your A column does not contain any values as you described as xx:yy
Do you mean that A2 should be BETWEEN1 and 28, etc...

[pboost1]

Correct, If A2 contains any value BETWEEN 1 and 28, or between 58 and 117, then display 03X05 in cell I2. If A2 contains any value BETWEEN 29 and 45 or between 118 and 132, then display 03X10. I will be sorting this and moving the cells in column A around.

Example: on today I want cell A2 to contain a 1, displaying 03x05 in cell I2. Tomorrow I want cell A2 to contain 40, displaying 03X10 in cell I2. The following day cell A2 will contain 100, which will display 03X05 in cell I2.

Sorry for the confusing posts. Thanks again in advance.

[MarvinP]

OHhhh Between......

The reason for the blank in my formula is that if the text in column A isn't that exact strings my formula returns a blank.

[arthurbr]

Try this then

=IF(OR(and(A2>=1,a2<=28),and(a2>=58,a2<=117),and(a2>=145,a2<=174)),"03X05","03X10")

[pboost1]

Dear MarvinP,

Thank you, The formula you have provided seems to work. I just need to add a few more values to it, and your formula seems simple enough to be able to edit it. If I have any other problems I will reply to this. Thanks alot.

[pboost1]

Dear Arthurbr

This is to inform you that your formula was the one that worked. Thank you for all that you have provided. Thank you MarvinP as well. It is working as needed even with the additional information I added. This thread can be marked closed. Thanks again.

[arthurbr]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save