SOLVED

Help with an "IF" style function

Brass Contributor

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

 

 

 

4 Replies

@Mr_Raj_C 

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.

help.png

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

best response confirmed by HansVogelaar (MVP)
Solution

@Mr_Raj_C 

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")))

help with if style function.png

@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

@Mr_Raj_C 

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")))

help with if style function.png

View solution in original post