Forum Discussion
Help with an "IF" style function
Dear Excel community,
I'm hoping you can assist with my latest conundrum !
Please find attached workbook.
With in the workbook, i need assistance with two issues.
Issue 1
In Column A, there is a count of the number of entries between columns B-H. In Colum I, i would like to have a formula return a result if the value of the total count (i.e 3) happens to fall on the same day (i.e if there is a in the total column, all 3 must relate to the same day)
Issue 2
Columns K - R show represent a schedule. In Column S, i would like to add a calendar variation based on the key which is in column V.
For instance, if column K, M and Q have a "1" then based on the key, that would be calendar variation 1.
I hope that makes sense once you see the workbook 🙂
As always, i'm grateful for the community support. Thanks you in advance.
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")))
4 Replies
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
RajYou 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")))
