If/Then Statement

[Guest]

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

 

[Guest]

in U1
=if(or(F1=1,F1=2,F1=3),N1+16,if(or(F1=4,F1=5),"N/A","otherwise"))
if f1 can only be 1-5 then
=if(f1<=3,N1+16,"N/A")
if you want the N/A error then replace "N/A" with NA()

 

[Leo Rod]

Assuming N1 as a date

On cell U1 write:
=if(or(f1=1,f1=2,f1=3),n1+16,if(or(f1=4,f1=5),"N/A","not defined"))

This will work as requested and will write "not defined" for values
different than 1,2,3,4,5.

Let me know how it works.

Greetings

Leo.

 

[Ron Rosenfeld]

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

U1: =N1+HLOOKUP(F1,{1,2,3;16,16,16},2,0)


--ron

 

[Guest]

Thank you VERY much for your help.

U1: =N1+HLOOKUP(F1,{1,2,3;16,16,16},2,0)


--ron

 

[Guest]

Thank you VERY much for your help.

Assuming N1 as a date

On cell U1 write:
=if(or(f1=1,f1=2,f1=3),n1+16,if(or(f1=4,f1=5),"N/A","not defined"))

This will work as requested and will write "not defined" for values
different than 1,2,3,4,5.

Let me know how it works.

Greetings

Leo.

 

[Guest]

Thank you VERY much for your help.

in U1
=if(or(F1=1,F1=2,F1=3),N1+16,if(or(F1=4,F1=5),"N/A","otherwise"))
if f1 can only be 1-5 then
=if(f1<=3,N1+16,"N/A")
if you want the N/A error then replace "N/A" with NA()

 

[Guest]

Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

 

[Pete_UK]

Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

 

[Guest]

Thank you VERY much for your help.

Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Karen - thanks for feeding back.

Pete

 

[Guest]

Thanks again Pete - If you could help me with one ore conditional
formatting. I have a date in cell N1 and the conditional formatting is set
to turn red if it's <= Today - Like you just helped me with. If I enter a
date in cell O1, I want the date in cell N1 to change back from red to black.
How can this be done?

ANY help would be greatly appreciated, Karen

 

[Pete_UK]

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete

 

[Guest]

Thank you again for your help and time. I must be doing something wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() - The date
will format to red
If I enter a date in column O, I want the date (it could be both black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen


:

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete

Thanks again Pete - If you could help me with one ore conditional
formatting. I have a date in cell N1 and the conditional formatting is set
to turn red if it's <= Today - Like you just helped me with. If I enter a
date in cell O1, I want the date in cell N1 to change back from red to black.
How can this be done?

ANY help would be greatly appreciated, Karen

:
You're welcome, Karen - thanks for feeding back.

Pete

Thank you VERY much for your help.

:
Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()
then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

 

[Pete_UK]

Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ----> red
O1 empty, N1 greater than today ---> black
O1 not empty, N1 less than or equal to today ---> black
O1 not empty, N1 greater than today ---> black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula with
the 4 variations of O1 and N1 listed above?

Pete

 

[Guest]

Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ----> red
Formula in cell N1 ----> =IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: > =$P$2)

At this point, If I enter a date in column O, I want the date (it could be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O is
blank.

Sorry, I'm confused

 

[Pete_UK]

You need to go in and edit the formula in the CF panel - you can see
that Excel (trying to be helpful) has adding some extra " symbols that
are not needed. Get rid of these and it should work how you want it
to.

Hope this helps.

Pete

 

[Guest]

Pete,

Please forgive my confusion. What extra symbols?

Thanks, Karen

 

[David Biddulph]

As Pete said, extra " symbols (quote marks).

You've got
="AND(O1="""",N1<=TODAY())"
where you should have
=AND(O1="",N1<=TODAY())

 

[Guest]

I'm sorry, but it still isn't working.

As Pete said, extra " symbols (quote marks).

You've got
="AND(O1="""",N1<=TODAY())"
where you should have
=AND(O1="",N1<=TODAY())