Hello,
I'm looking for a formula in column G that returns YES or NO when YES is repeated more than once in columns A to F, but the previous and next columns are NO, as described in rows 2 and 4 in the example.
Thanks in advance!
Be healthy!
Hello,
I'm looking for a formula in column G that returns YES or NO when YES is repeated more than once in columns A to F, but the previous and next columns are NO, as described in rows 2 and 4 in the example.
Thanks in advance!
Be healthy!
I do not understand what you are asking from the sample file can you try to explain more?
If in columns 1 to 6 YES is repeated more than once and if before and after YES there is NO the expected result is YES otherwise it is NO.
or
If before and after NO there is YES the expected result is YES otherwise it is NO.
I supplemented the example with two new lines.
Please try
=IF(COUNT(FIND({12,21},NPV(9,1+(A2:F2="NO"))))=2,"YES","NO")
why is row 9 yes? when there is not a no before and after? I have to confess the formula is beyond me and a very clever solution, which I will have to sit down and work out!
Look above:
"or
If before and after NO there is YES the expected result is YES otherwise it is NO."
Thank you!
I need one report, the columns are the twelve months, but the principle for the other 6 is the same.
Try in G2:
Please Login or Register to view this content.
Quang PT
Thank you so much!
Only one equal (=) after the greater (>) and it works perfectly.
IF(COUNT(SEARCH({"NOYESNO","YESNOYES"},A2&B2&C2&D2&E2&F2))>=1,"YES","NO")
Be healthy!
@davsth Thanks for kind comment and the reps
Before I came up with
=IF(COUNT(FIND({12,21},NPV(9,1+(A2:F2="NO"))))=2,"YES","NO")
I used
=IF(COUNT(SEARCH({"OY","SN"},A2&B2&C2&D2&E2&F2))=2,"YES","NO")
But if we want to expand to more column like A:Z then we need to add 25 more & ampersand which is not fun
for MS 365 we can use Concat(A2:Z2) but other vesion don't have this.
NPV trick to concat number, I learn from XOR LX
https://www.excelforum.com/excel-for...ml#post5378511
I have Thai Clip to explain NPV
https://youtu.be/dbTH590_RDg
1+(A2:F2="NO") Change Yes to 1 and NO to 2
We can concat number from A:Z using just
NPV(9,1+(A2:Z2="NO")))
instead of
A2&B2&C2&D2&E2&F2…&Y2&Z2
Then find 12 for YesNO and 21 for NoYes
Last edited by Bo_Ry; 09-11-2020 at 08:44 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks