Forum Discussion
Help with an "IF" style function
You are welcome. This formula should return the intended result for issue 1. However i'm not sure why the total count in cells A10 and A13 is 3 because in ranges B10:H10 and B13:H13 are only 0s.
=BYROW(A2:H13,LAMBDA(x,IF(MAX(DROP(x,,1))=TAKE(x,,1),"Pass","Fail")))
Issue 2
=BYROW(K2:R13,LAMBDA(z,XLOOKUP(TEXTJOIN("|",,FILTER(K1:R1,z=1)),BYROW(V2:X5,LAMBDA(x,TEXTJOIN("|",,x))),Y2:Y5)))
If you want to return the No of the calendar version you can apply this formula.
Issue 1
Can you give an example of the expected result? I'm unsure what you are looking for.
OliverScheurich Thank you for your assistance with Issue 2. That worked fantastically 🙂
With regard to issue 1, the expected result is if all of the total number (column A) fall on the same day then i want put "Pass" in column I. If they don't fall on the same day, then i want to to say "Fail".
So if we take row 2 and 3 for example, the number in "Wednesday" and "Tuesday" (3) matches the total so i would want to return a "Pass" for these two rows.
Whereas if you look at row's 5,6 & 7, the total amount is split over multiple days so this should return a "fail".
So in a nutshell, if the total is split over multiple days, its a "fail". If they are in one day, its a "pass".
Apologies if this was not clear in the original workbook. Have reattached.
Thanks
Raj
You are welcome. This formula should return the intended result for issue 1. However i'm not sure why the total count in cells A10 and A13 is 3 because in ranges B10:H10 and B13:H13 are only 0s.
=BYROW(A2:H13,LAMBDA(x,IF(MAX(DROP(x,,1))=TAKE(x,,1),"Pass","Fail")))
OliverScheurich Thank you for you help on this matter.
Seems to have done the trick