SOLVED

# Help with an "IF" style function

Brass Contributor

# Help with an "IF" style function

Dear Excel community,

I'm hoping you can assist with my latest conundrum !

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

4 Replies

# Re: Help with an "IF" style function

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.

# Re: Help with an "IF" style function

@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

best response confirmed by HansVogelaar (MVP)
Solution

# Re: 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")))

# Re: Help with an "IF" style function

@OliverScheurich Thank you for you help on this matter.

Seems to have done the trick

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: 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")))