Rewrite IF function

[Anonymous]

I am using this formular, in order to evaluate if some requirement are fulfil. But it does not give me "No" for B10, but works with B12 for both "Yes" and "No". 

Dag =

VAR dag = Ark1[øvelsesnummer]

return

if(ISBLANK(Ark1[Dato]),"No",

if( Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B10", "Yes",

if( Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B12", "Yes",

if( dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Frederiksberg" && [Week Nr] <= 17, "Yes", No"))))

I hope someone can help me figure this out or rewrite the formula 🙂 

Kind regards

Line

[VijayP]

@Anonymous 

SWITH(TRUE(),ISBLANK(Ark1[Dato]),"No",

Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B10", "Yes",

Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B12", "Yes",

dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Frederiksberg" && [Week Nr] <= 17, "Yes")

Try this 

in you formula you have already given now in case the value is blank and again mentioning the at the ending which i have removed and also converted to switch function for more readability

[Anonymous]

It did not work. The formula has to end with a no, as no is given for both blank cells but also if the criteria in are not fulfill in any of the iterations. 

Dag =

VAR dag = Ark1[øvelsesnummer]

return

SWITCH(TRUE(),

ISBLANK(Ark1[Dato]),"Nej",

Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B10", "Ja",

Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B12", "Ja",

dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Frederiksberg" && [Week Nr] <= 17, "Ja",

dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Fælledvej" && [Week Nr] <= 4, "Ja", "Nej"

It works perfectly for B12. But for B1, B2 and B10 it gives me incorrect values for yes and no due to the provided criteria. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of calculated column [Dag] as below and check whether you can get the correct result.

Dag =
VAR dag = Ark1[øvelsesnummer]
RETURN
    IF (
        (
            Ark1[Station] = "Christianshavn"
                && Ark1[Dato] <= DATE ( 2020, 10, 17 )
                && dag IN { "B10", "B12" }
        )
            || ( dag
            IN { "B1", "B2", "B10", "B12" }
            && Ark1[Station] = "Frederiksberg"
            && [Week Nr] <= 17 ),
        "Ja",
        "Nej"
    )

If the above one can't get the correct result, please provide some sample data(exclude sensitive data) and the related calculation logic in order to provide the suitable solution for your scenario. Thank you.

Best Regards

[Anonymous]

It helped! Thank you for your help!