I have a cell A1 that I want to show as "Compliant" or "Not Compliant." This cell is based on columns O and P. A1 should be "Compliant" if all of the values in column O are less than the corresponding values in column P (ie. O1<P1, O2<P2, O3<P3, etc.). If there is any case where a value in column O is greater than the value in column P, then A1 should be "Not Complaint."
Insert a help column that count the instance when O is greater than P. If the instance is greater than 0 then result will be Not Compliant.
Hi
If i get your point, then,Try.
=IF(O1<P1;"Complaint"."Not Complaint")
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
You can do this with a user defined function
Open the VBA editor (Alt F11). Insert a new module (Insert - Module) and paste in the above. Alt F11 back to worksheet.
Please Login or Register to view this content.
in A1 type
=CheckCompliance(O1:O22,P1:P22)
adjusted for your actual range.
Note that you didn't specify what should happen if the values in O and P were the same. I've assumed this means non-compliant. If this is not the case, change the >= into just >.
Martin
Thanks. Is there a way to do this without VBA? I was hoping there was a formula I could use in cell A1.
Try this array formula (entered with Ctrl+Shift+Enter)
=IF(PRODUCT(IF(O1:O11<P1:P11,1,0)),"","Not ")&"Compliant"
Adjust the ranges to suit
Note that you haven't allowed for the situation where a value in O equals it's adjacent value in P
That's perfect. Thanks, Cutter! I made sure to change the formula to <= when O equals P.
One more question, Cutter. Any advice on how to make the formula work such that the formula only accounts for values in columns O and P, when the corresponding value in column D = "adhesive"? Meaning, A1 should say "Compliant" if D4="adhesive" and O4<=P4 AND D5="other" and O5>P5.
Sorry, I don't get what you're trying to achieve. You're asking for a result based on values in one row compared to values in the row below?
A1 indicates "compliant" or "not compiant". In rows 5-30, it first looks at column D to find "adhesive". When cell D# = "adhesive," it then checks to see if cell O#<=P#. (# represents any number between 5-30.)
So, when looking at rows 5-30, if the following conditions are true, A1 is "compliant":
D7 = adhesive, O7<=P7
D8 = adhesive, O8<=P8
D22 = adhesive, O22<=P22
D25 = paint, O25>P25 (even though O25>P25, it's doesn't affect compliance because it's a paint instead of an adhesive)
Hope that's a better explanation
How about:
=IF(SUMPRODUCT((D5:D30="adhesive")*(O5:O30>P5:P30)),"Not ","")&"Compliant"
PERFECT! Thanks!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks