How to sort out the algorithm for "Cancel" without changing the other logic.

[Eloise T]

Please see the two attached files.
1. MS Excel spreadsheet.
2 MS Word - the explanation.

Cells Q4 and R4 need to be blank since Cells J3 and J4 equal "canceled."

Thank you in advance.

 

[chirayu]

I honestly couldn't understand your requirements. They're quite confusing.

Let me confirm some things first.


1) Screen Size <> 0 AND Helper = "" THEN "Missing Data"
2) Screen Size <> 0 AND Helper = "Y" THEN ""
3) Screen Size <> 0 AND Helper = "N" AND Screen Size < 42 THEN ""
4) Screen Size <> 0 AND Helper = "N" AND Screen Size > 41 AND K = "Cancel*" THEN ""

Heres where its starts getting confusing

5) Screen Size <> 0 AND Helper = "N" AND Screen Size > 41 And K = "" THEN "^ to Y"

6) No clue what "Else Blank"/ "Confirm N/A" depends on

Looking at the formula I know its not correct so instead can you just clarify your requirements and I'll make a new formula. Also not really sure why you have step 3 or 2 at all because you're doing an else blank anyways

 

[Eloise T]

I honestly couldn't understand your requirements. They're quite confusing. I KNOW it's confusing and I apologize...it's confusing to me! ...which is why I need help sorting the logic out. THANK YOU for digging into this mess.

Let me confirm some things first.


1) Screen Size <> 0 AND Helper = "" THEN "Missing Data" Helper cell (E column) was left blank and needs answered, either Y or N. This works.
2) Screen Size <> 0 AND Helper = "Y" THEN "" Helper cell has been satisfied with data and no further action is necessary. This appears to work.
3) Screen Size <> 0 AND Helper = "N" AND Screen Size < 42 THEN "" Helper cell has been satisfied particularly since the TV Screen Size is under the company requirement of needing a helper and no further action is necessary. This appears to work.
4) Screen Size <> 0 AND Helper = "N" AND Screen Size > 41 AND K = "Cancel*" THEN "" ← This is where the formula is not doing what I need it to do. IF Screen Size = something and the Screen Size is greater than 41” AND Column K = cancel (The technician acknowledges that the work order was canceled), then leave Column Q blank (Column Q is my "error catching" notification cell). Instead the formula (see cells Q4 and R4) is displaying Δ to Y (change to YES). What I think I need to clarify for you to understand is that if the work order was canceled, then the technician wasn't on site to see the TV so there’s no possible way the technician can say, YES or NO, I had a Helper or if the TV was mounted on a wall. If you change cells E4 and F4 to N/A ...similar to ROW 3, then it works.
Here’s where its starts getting confusing:
5) Screen Size <> 0 AND Helper = "N" AND Screen Size > 41 And K = "" THEN Δ to Y" The technician is reporting there is no helper, and by company requirements, he must have a helper if the TV Screen Size is greater than 41", therefore the reminder to put YES and the note in Column Q to Δ to Y (change to Y).

6) No clue what "Else Blank"/ "Confirm N/A" depends on In cells Q3 and R3 it seems to be working as the work order has been canceled and "Confirm N/A" indicates that because it is a canceled work order the Helper and Wall Mounted questions have been confirmed as Not Applicable....but it also correctly says N/A in cells E3 and F3. If you change E4 and F4 to N/A, they work as well, but not with N as an answer, hence where the formula breaks down.

Does this help you?

 

[KenU]

It looks like the change below should correct the problem (if I understand correctly)"

Change formula in Q4 to:
=IF(AND(N4<>0,TRIM(E4)= ""),"Missing Data",IF(N4<>0,IF(TRIM(E4)="Y","",IF(TRIM(E4)="N",IF(N4<42,"",IF(AND(N4>41,SEARCH("cancel",K4)),"",IF(N4>41,"Δ to Y",""))),"Confirm N/A")),""))

The previous use of ' K4 = "*cancel*" ' was returning FALSE whereas the SEARCH function will return TRUE as expected.

I hope that helps.

Regards,
Ken

 

[Eloise T]

It looks like the change below should correct the problem (if I understand correctly)"

Change formula in Q4 to:
=IF(AND(N4<>0,TRIM(E4)= ""),"Missing Data",IF(N4<>0,IF(TRIM(E4)="Y","",IF(TRIM(E4)="N",IF(N4<42,"",IF(AND(N4>41,SEARCH("cancel",K4)),"",IF(N4>41,"Δ to Y",""))),"Confirm N/A")),""))

The previous use of ' K4 = "*cancel*" ' was returning FALSE whereas the SEARCH function will return TRUE as expected.

I hope that helps.

Regards,
Ken

I have been quietly struggling with this for weeks wondering what in the world have I done wrong? I finally broke down and posted here on Chandoo. You win the Wile E. Coyote, Super Genius award. THANKS! ...and Thanks to chirayu as well.

 

[Eloise T]

It looks like the change below should correct the problem (if I understand correctly)"

Change formula in Q4 to:
=IF(AND(N4<>0,TRIM(E4)= ""),"Missing Data",IF(N4<>0,IF(TRIM(E4)="Y","",IF(TRIM(E4)="N",IF(N4<42,"",IF(AND(N4>41,SEARCH("Cancel",K4)),"",IF(N4>41,"Δ to Y",""))),"Confirm N/A")),""))

The previous use of ' K4 = "*cancel*" ' was returning FALSE whereas the SEARCH function will return TRUE as expected.

I hope that helps.

Regards,
Ken

I had to add one thing to make it work perfectly:

=IF(AND(N4<>0,TRIM(E4)= ""),"Missing Data",IF(N4<>0,IF(TRIM(E4)="Y","",IF(TRIM(E4)="N",IF(N4<42,"",IF(AND(N4>41,IF(AND(N7>41,IFERROR(SEARCH("*Cancel*",K4)>0,0)),"",IF(N4>41,"Δ to Y",""))),"Confirm N/A")),"")

....otherwise I kept getting #VALUE! (A value used in the formula is of the wrong data type.)