Multiple Statements in Formula

[Dummy Excel]

Hi All,
the below is my formula and it works

=If(and(O84="Unavailable (Tagged Out)",E84=""),"Unavailable",If(and(O84="Unavailable (Tagged Out)",E84="Completed (Repairs completed, <b>Available</b> and safe to use)"),"Available", If(and(O84="Unavailable (Tagged Out)",E84="In-Progress (<b>Unavailable</b>, tagged out and not safe to use)"),"Unavailable", If(and(O84="Available (Not Tagged Out)" ,E84="Completed (Repairs completed, <b>Available</b> and safe to use)"),"Available", If(and(O84="Available (Not Tagged Out)" ,E84="In-Progress (<b>Unavailable</b>, tagged out and not safe to use)"),"Unavailable", If(and(O84="Available (Not Tagged Out)",E84=""),"Available" , "Available"))))))

Although now i need to add another "AND" statement (i think?) in and i cant get it to work. The addition is i need to check the date as well which is in column "G". Basically if the date = today then make it "Unavailable", otherwise continue on with the formula. Is this possible?

thanks for your help
Sam

 

[MartyS]

The addition is i need to check the date...

Basically if the date = today then make it "Unavailable", otherwise continue on with the formula.

The date .. is in column "G"

Try this....

=IF(G84=TODAY(),IF(AND(O84="Unavailable (Tagged Out)",E84=""),"Unavailable",IF(AND(O84="Unavailable (Tagged Out)",E84="Completed (Repairs completed, Available and safe to use)"),"Available", IF(AND(O84="Unavailable (Tagged Out)",E84="In-Progress (Unavailable, tagged out and not safe to use)"),"Unavailable", IF(AND(O84="Available (Not Tagged Out)",E84="Completed (Repairs completed, Available and safe to use)"),"Available", IF(AND(O84="Available (Not Tagged Out)",E84="In-Progress (Unavailable, tagged out and not safe to use)"),"Unavailable", IF(AND(O84="Available (Not Tagged Out)",E84=""),"Available", "Available")))))),"Unavailable")

 

[Dummy Excel]

thanks for your help MartyS
it works correctly for some of the scenarios, although it give the wrong result for the other scenarios.
is it possible to have the today() for all scenarios?

 

[MartyS]

Hi...

I'm guessing you possibly mean something along the lines of:

=IF(AND(O84="Unavailable (Tagged Out)",E84="",G84=TODAY()),"Unavailable",IF(AND(G84=TODAY(),O84="Unavailable (Tagged Out)",E84="Completed (Repairs completed, Available and safe to use)"),"Available", IF(AND(G84=TODAY(),O84="Unavailable (Tagged Out)",E84="In-Progress (Unavailable, tagged out and not safe to use)"),"Unavailable", IF(AND(G84=TODAY(),O84="Available (Not Tagged Out)",E84="Completed (Repairs completed, Available and safe to use)"),"Available", IF(AND(G84=TODAY(),O84="Available (Not Tagged Out)",E84="In-Progress (Unavailable, tagged out and not safe to use)"),"Unavailable", IF(AND(G84=TODAY(),O84="Available (Not Tagged Out)",E84=""),"Available", "Available"))))))

If not, you'll need to be more specific

 

[Eric W]

I am not a fan of deeply nested IF statements like that. They're hard to read and maintain. Instead, let me offer a few alternatives.

	A	B	C
Unavailable (Tagged Out)	Available (Not Tagged Out)
(empty)	U	A
Completed (Repairs completed, Available and safe to use)	A	A
In-Progress (Unavailable, tagged out and not safe to use)	U	U
Unavailable	Unavailable	Unavailable
Unavailable	Unavailable
Column O	Column E	Result
Unavailable (Tagged Out)	Unavailable
Unavailable (Tagged Out)	Completed (Repairs completed, Available and safe to use)	Available
Unavailable (Tagged Out)	In-Progress (Unavailable, tagged out and not safe to use)	Unavailable
Available (Not Tagged Out)	Available
Available (Not Tagged Out)	Completed (Repairs completed, Available and safe to use)	Available
Available (Not Tagged Out)	In-Progress (Unavailable, tagged out and not safe to use)	Unavailable

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A6[/TH]
[TD="align: left"]=IF(AND(O84="Unavailable (Tagged Out)",E84=""),"Unavailable",
IF(AND(O84="Unavailable (Tagged Out)",E84="Completed (Repairs completed, Available and safe to use)"),"Available",
IF(AND(O84="Unavailable (Tagged Out)",E84="In-Progress (Unavailable, tagged out and not safe to use)"),"Unavailable",
IF(AND(O84="Available (Not Tagged Out)",E84="Completed (Repairs completed, Available and safe to use)"),"Available",
IF(AND(O84="Available (Not Tagged Out)",E84="In-Progress (Unavailable, tagged out and not safe to use)"),"Unavailable",
IF(AND(O84="Available (Not Tagged Out)",E84=""),"Available",
"Available"))))))[/TD]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"]=IF(OR(E84="In-Progress (Unavailable, tagged out and not safe to use)",AND(O84="Unavailable (Tagged Out)",E84="")),"Unavailable","Available")[/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=IF(OR(G84=TODAY(),E84="In-Progress (Unavailable, tagged out and not safe to use)",AND(O84="Unavailable (Tagged Out)",E84="")),"Unavailable","Available")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B7[/TH]
[TD="align: left"]{=IFERROR(INDEX($C$11:$C$16,MATCH(O84&"|"&E84,$A$11:$A$16&"|"&$B$11:$B$16,0)),"Available")}[/TD]
[/TR]
[TR]
[TH]C7[/TH]
[TD="align: left"]{=IF(G84=TODAY(),"Unavailable",IFERROR(INDEX($C$11:$C$16,MATCH(O84&"|"&E84,$A$11:$A$16&"|"&$B$11:$B$16,0)),"Available"))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Your formula is in A6 (with some line feeds for readibility). However, by dissecting the formula I constructed a truth table in A1:C4. Based on this table, there are only 3 combinations that generate "Unavailable". Both of the cases where O84="In-progress ...", and one where O84="" and E84 = "Available...". If we only check for those cases, we get the much shorter formula in B6. And from this formula, it's very easy to add the additional condition where if E84 = TODAY(), then it's "Unavailable".

We can also go another route. Create a list of all the combinations in A10:C16, with column C being the result you want. Then the B7 formula looks the combination up in the table and grabs the appropriate response. This is very easy to add or change the table, and the formula does not need to be changed at all. The updated formula with your additional condition is in C7.

Hope this helps.

 

[Dummy Excel]

.

 

[Dummy Excel]

ive decided to go with the worksheet formula to reduce the length to make it easier for my brain to understand.

=IF(OR(G84=TODAY(),E84="In-Progress (Unavailable, tagged out and not safe to use)",AND(O84="Unavailable (Tagged Out)",E84="")),"Unavailable","Available")

This works although, if column B (in Eric's data example) is "Completed (Repairs completed, Available and safe to use)" and the date is in the past, the result of the formula is "unavailable". The result of the formula needs to be available as it is completed

So basically it needs to be made unavailable if its "Unavailable (Tagged Out)", and if the date is today, older or blank

hope that makes more sense
thanks

 

[Eric W]

I'm afraid I'm a bit confused, I don't understand what you want. The formula does not reference column B, did you mean E? Could you make a list of the possible combinations of values in E84, G84, and O84, like I did in A10:C16? You'll need to add another column for the date, with "Today", "older", and blank as options.

 

[Dummy Excel]

Hi Eric,
I started this thread without my full attention, I wont be doing that again as it confuses the people that are trying to help me - i apologize

so below is a sample of my data...

Book1
	E	G	O	P
1	Action Status	Estimated date when repairs will be completed	Status of the Vehicle/P&E	Final Status
2			Available (Not Tagged Out)	Available
3		Date in the past	Available (Not Tagged Out)	Unavailable
4		Date in the future	Available (Not Tagged Out)	Available
5			Unavailable (Tagged Out)	Unavailable
6		Date in the past	Unavailable (Tagged Out)	Unavailable
7		Date in the future	Unavailable (Tagged Out)	Unavailable
8	Confirmed (Available, safe to use, although repairs to be completed)		Available (Not Tagged Out)	Available
9	Confirmed (Available, safe to use, although repairs to be completed)	Date in the past	Available (Not Tagged Out)	Unavailable
10	Confirmed (Available, safe to use, although repairs to be completed)	Date in the future	Available (Not Tagged Out)	Available
11	Confirmed (Available, safe to use, although repairs to be completed)		Unavailable (Tagged Out)	Unavailable
12	Confirmed (Available, safe to use, although repairs to be completed)	Date in the past	Unavailable (Tagged Out)	Unavailable
13	Confirmed (Available, safe to use, although repairs to be completed)	Date in the future	Unavailable (Tagged Out)	Unavailable
14	In-Progress (Unavailable, tagged out and not safe to use)		Available (Not Tagged Out)	Unavailable
15	In-Progress (Unavailable, tagged out and not safe to use)	Date in the past	Available (Not Tagged Out)	Unavailable
16	In-Progress (Unavailable, tagged out and not safe to use)	Date in the future	Available (Not Tagged Out)	Unavailable
17	In-Progress (Unavailable, tagged out and not safe to use)		Unavailable (Tagged Out)	Unavailable
18	In-Progress (Unavailable, tagged out and not safe to use)	Date in the past	Unavailable (Tagged Out)	Unavailable
19	In-Progress (Unavailable, tagged out and not safe to use)	Date in the future	Unavailable (Tagged Out)	Unavailable
20	Completed (Repairs completed,Available and safe to use)		Available (Not Tagged Out)	Available
21	Completed (Repairs completed,Available and safe to use)	Date in the past	Available (Not Tagged Out)	Available
22	Completed (Repairs completed,Available and safe to use)	Date in the future	Available (Not Tagged Out)	Available
23	Completed (Repairs completed,Available and safe to use)		Unavailable (Tagged Out)	Available
24	Completed (Repairs completed,Available and safe to use)	Date in the past	Unavailable (Tagged Out)	Available
25	Completed (Repairs completed,Available and safe to use)	Date in the future	Unavailable (Tagged Out)	Available
Sheet2

So column "G" obviously has proper dates in there although didnt want to put dates as i wasnt sure if they would be valid when you looked at the data!
So column "P" is where i need the formula, although have shown what the outcome should be.

I think ive got all the scenarios, sorry if ive missed any scenarios
i know i didn't add the "Confirmed" status in my OP although confirmed status should always be "Available (Not Tagged Out)" although if we are going to do the formula i just put all the scenarios in just in case.

Just in case you are wondering about the "<b>Available</b>", the data is imported from a google sheet like that


again, I apologize for the confusion.
Appreciate your help
Sam

 

[Eric W]

OK, based on that table, I came up with this:

=IF(LEFT(E84,9)="Completed","Available",IF(LEFT(E84,11)="In-Progress","Unavailable",IF(LEFT(O84,11)="Unavailable","Unavailable",IF(OR(G84="",G84>=TODAY()),"Available","Unavailable"))))

By analyzing the table, I found these rules:
1) If E84 = "Completed ..." the result is always "Available."
2) If E84 = "In-Progress..." the result is always "Unavailable."
3) Otherwise, if O84 = "Unavailable" the result is always "Unavailable."
4) Otherwise, if G4 is empty or in the future, the result is "Available"
5) Otherwise, "Unavailable".

The formula above encompasses those rules. I also used LEFT to check for the cell values to shorten the formula. Let me know if this works for you.