Help on if formula,

[coopermarsh]

The goal is to display "recall"or a "no action" based on the date and where a tape is located

K12 is the date field
I12 is the location field

problem
If i set date to in use and location to quantum it dipslays corectly "No Action".

if i set day to a date and location to either Firesafe or Iron mountain, false is diplayed and not "no action"


here is the formula

=IF(K12="In Use","No Action",IF(I12="Iron Mountain",IF(TODAY()<K12-10,"Recall IM",IF(I12="Firesafe",IF(TODAY()<K12-5,"Retrieve FS","NO Action")))))

All i am getting as a result is false

 

[SkipVought]

hi,

K12 is the date field

=IF(K12="In Use"

How can K12 be a DATE and have "In Use"?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

There is no formula for false condition in:
[tt]IF(I12="Firesafe",IF(...[/tt]
(Similar to =IF(1<0,"x"))

combo

 

[coopermarsh]

oops

sorry wrong formula posted :0

=IF(I11="Iron Mountain",IF(TODAY()<K11-10,"Recall IM",IF(I11="Firesafe",IF(TODAY()<K11-5,"Retrieve FS","No Action"))))

 

[SkipVought]

So what values are in I11 & K11?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

The reason for FALSE as a result of IF function remains. The third argument (for false condition) is optional, but if the condition is evaluated as FALSE the IF function returns FALSE.

combo

 

[zelgar]

Look at your logic:
1st Condition: I11 = "Iron Mountain"
- True - goto 2nd Condition
- False - nothing - gives FALSE result

2nd Condtion: TODAY()<K11-10
- True - "Recall IM"
- False - goto 3rd condition

3rd condtion: I11="Firesafe"
- True - goto 4th condition
- False - nothing - gives FALSE result

4th Conditon: TODAY()<K11-5
- True - "Retrieve FS"
- False - "No Action"

You have a few times where you don't have anything when you get a FALSE result in your IF statement (1st and 3rd conditions). One way you can easily see your logic for your statements is to hit the fx button next to the formula bar. It will give you a step by step view of the logic. You can even step through your formula if you click within it to the different parts of your formula.

 

[coopermarsh]

here is the solution

Thanks for all your help

=IF(I11="Iron Mountain",IF(TODAY()<K11-10,"Recall IM",IF(I11="Firesafe",IF(TODAY()<K11-5,"Retrieve FS","No Action"),"No Action")),"No Action")

 

[macropod]

here is the solution

Somehow I don't think so. The only time you'll get a result other than 'No Action' is when I11="Iron Mountain" and TODAY()<K11-10. Try:
=IF(AND(I11="Iron Mountain",TODAY()<K11-10),"Recall IM",IF(AND(I11="Firesafe",TODAY()<K11-5),"Retrieve FS","No Action"))

Cheers
Paul Edstein
[MS MVP - Word]