Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-06-2022, 03:31 AM
DavidRood DavidRood is offline Formula error Windows 11 Formula error Office 2013
Novice
Formula error
 
Join Date: Sep 2022
Posts: 4
DavidRood is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-06-2022, 07:49 AM
DavidRood DavidRood is offline Formula error Windows 11 Formula error Office 2013
Novice
Formula error
 
Join Date: Sep 2022
Posts: 4
DavidRood is on a distinguished road
Default

I've added an attachment
Attached Files
File Type: xlsx Sample Material Control Logbook.xlsx (10.4 KB, 3 views)
Reply With Quote
  #3  
Old 09-06-2022, 08:40 AM
p45cal's Avatar
p45cal p45cal is online now Formula error Windows 10 Formula error Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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","")))))
Reply With Quote
  #4  
Old 09-06-2022, 01:35 PM
DavidRood DavidRood is offline Formula error Windows 11 Formula error Office 2013
Novice
Formula error
 
Join Date: Sep 2022
Posts: 4
DavidRood is on a distinguished road
Default 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"
Attached Files
File Type: xlsx Sample Material Control Logbook.xlsx (10.8 KB, 5 views)
Reply With Quote
  #5  
Old 09-12-2022, 02:05 AM
Marcia's Avatar
Marcia Marcia is offline Formula error Windows 11 Formula error Office 2021
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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")))))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula error 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 error formula divide by serio error vthomeschoolmom Excel 1 04-18-2012 02:29 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft