Two IF functions in one?

[willisbr]

Can you have 2 IF functions in 1?

example:
=IF(AC46=0,"CLOSED",AC46) or =IF(V46=32,"NULL",V46)

Basically if the value equals zero, i want it to say Closed, OR if the value of another cell equals 32 I want it to say Null.

There is no way for both to be true.

Is this possible? Thanks!

 

[dcardno]

Under what conditions do you want the formula to return the values in AC46 of V46? There are four possible combinations of 0 / non-zero in AC46 and 32 / not-32 in V46 - you have specified that one of them (0 & 32) cannot occur and provided the return vaues for two of the others - what resut do you expect for non-zero / non-32?

 

[willisbr]

Well the original function was just =IF(AC46=0,"CLOSED",AC46). AC46 is a cell that shows how many vacation slots are open. So if there are 5 slots available...it will show 5. AC46 is a hidden cell because it's just another function with other rules for counting available slots.

So I want this visible cell to show AC46 value (a number besides zero)...and when all the slots are filled and the value is actually zero, i want it to say Closed. That works fine.

NOW, If there are no slots even open for that day...I dont want a number or Closed on there cause it's not really open in the first place. So I wanted to make a conditional format that says if the cell="NULL" then make the text white (invisible).

So to do this, I'm looking for another IF function in that same cell to say...if the value of another cell = 32 (which is just an arbitrary number) then = "Null".

Let me know if you are understanding all that, it's a alot. Thanks!

 

[xapie128]

Can you have 2 IF functions in 1?

example:
=IF(AC46=0,"CLOSED",AC46) or =IF(V46=32,"NULL",V46)

Basically if the value equals zero, i want it to say Closed, OR if the value of another cell equals 32 I want it to say Null.

There is no way for both to be true.

Is this possible?

You can have two IF functions in one, but what you wrote is confusing since you are saying if AC46 is not zero in one case then display what's in AC46, but if what's in V46 is not 32 in another case then display what's in V46. You have a truth table that looks like this:

 AC46   V46 
-----------
  0   32
  0    x
  x   32
  x    x

where x = any value except the test values (0 for AC46 and 32 for V46)
If you OR these, then the first three will be True and only the last False. But you then need to decide if it is False, what value do you want in the cell--AC46's or V46's? But if you AND these, then only the first one is True and you have another decision to make--do you want CLOSED or NULL in the cell? It seems that you really want two separate forumlas in two separate cells; unless you change something in your statement above.

Back to the generic question about two IF functions in one line: Here's what you can use:

=IF(AC46=0,"CLOSED",IF(V46=32,"NULL",V46))

What this will do is return CLOSED if AC=0 and return "NULL" if V46=32, but if both are true, it will say CLOSED (if you want NULL then reverse the order if the IFs). In this example, if neither are true, then the cell where this formula resides will contain the value of V46 (if you'd prefer it contain AC46 then change the last term in the formula--but you can't have values from BOTH cells, unless you want to add them, multiply them, or perform some other math on the two).

 

[just_jon]

Is this it?

=IF(AND(AC46=0,V46=32),"NULL",IF(AC46=0,"CLOSED",IF(AC46,AC46,V46)))

 

[willisbr]

Perfect that's exactly what I was looking for.

=IF(AC46=0,"CLOSED",IF(V46=32,"NULL",V46))

Thanks very much, I can work with that.