#1
|
|||
|
|||
Formula error
This is my formula. Everything works except I cannot differentiate between Cancelled and Partial Cancelled.
=IF(AND(P2<I2,R2>="CANCELLED"),"CANCELLED PARTIAL SSUE",IF(I2<1,"",IF(P2=0,"OUTSTANDING",IF(P2<I2,"P ARTIAL",IF(P2=I2,"COMPLETE",""))))) COLUMNS I P Q R Qty QTY STATUS REMARKS RCVD 10 10 COMPLETE 10 5 PARTIAL 1 1 COMPLETE 1 OUTSTANDING 10 5 CANCELLED PARTIAL ISSUE CANCELLED AS PER MC 5 CANCELLED PARTIAL ISSUE CANCELLED AS PER SO1 THIS SHOULD READ CANCELLED because P is blank yet it is showing Cancelled Partial Issue |
#2
|
|||
|
|||
I've added an attachment
|
#3
|
||||
|
||||
In your formulae in column Q there is no 'output' which just says 'CANCELLED', so in no circumstances can you get just 'CANCELLED'.
I've highlighted in red the various 'outputs' in the formula: =IF(AND(P14<I14,R14>="CANCELLED"),"CANCELLED PARTIAL ISSUE",IF(I14<1," ",IF(P14=0,"OUTSTANDING",IF(P14<I14,"PARTIAL",IF(P14=I14,"COMPLETE",""))))) |
#4
|
|||
|
|||
Formula Error
I added the additional information.
=IF(AND(P2=0,R2>="CANCELLED"),"CANCELLED",IF(I2<1, " ",IF(P2=0,"OUTSTANDING",IF(P2<I2,"PARTIAL",IF(P2=I 2,"COMPLETE",IF(AND(P16<I16,R15>="CANCELLED"),"PAR TIAL ISS REMAINING QTY CNX",)))))) I get the cancelled notification but now I cannot get the "PARTIAL ISS REMAINING QTY CNX" comment. The column reverts back to PARTIAL. Maybe my Snytax is off. As I stated before, I would like to have any Document Number that is cancelled but received a partial issus to read "PARTIAL ISS REMAINING QTY CNX" |
#5
|
||||
|
||||
How about this:
=IF(AND(P2=0,R2>="CANCELLED"),"CANCELLED",IF(I2<1, " ",IF(P2=0,"OUTSTANDING",IF(AND(P2<I2,R2>="CANCELLE D"),"PARTIAL ISS REMAINING QTY CNX",IF(P2=I2,"COMPLETE","PARTIAL"))))) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cell formula error? | PomDave | Excel | 9 | 10-29-2020 05:43 AM |
LOOKUP Formula error but WHY??? | Haga | Excel | 1 | 04-17-2019 11:13 PM |
Array formula value not available error for one result | emarkona | Excel | 1 | 03-08-2018 03:00 PM |
Error handling in formula - suppress 'Syntax Error' | Cosmo | Word | 7 | 02-24-2014 07:08 AM |
formula divide by serio error | vthomeschoolmom | Excel | 1 | 04-18-2012 02:29 PM |