Forum Discussion

Neoma1304's avatar
Neoma1304
Copper Contributor
Jan 18, 2024
Solved

Trying to create attendance spreadsheet, stuck on a formula

I am trying to create a workbook to track staff attendance. I've tried a few formulas: IF(OR(B4="Absent","1",""),IF(OR(B4="Tardy","0.33",""),IF(OR(B4="Left Early","0.33",""),IF(OR(B4="FMLA","0","")I...
power bi
  • OliverScheurich's avatar
    OliverScheurich
    Jan 18, 2024

    Neoma1304 

    =IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,"")))

     

    =IF(B5="Absent",1,IF(OR(B5="Tardy",B5="Left Early"),0.33,""))

     

    =IF(B6="Absent",1,IF(OR(B6="Tardy",B6="Left Early"),0.33,""))

     

    If these formulas return the intended result it should be possible to add criteria.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 18, 2024

Neoma1304 

=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,"")))

 

=IF(B5="Absent",1,IF(OR(B5="Tardy",B5="Left Early"),0.33,""))

 

=IF(B6="Absent",1,IF(OR(B6="Tardy",B6="Left Early"),0.33,""))

 

If these formulas return the intended result it should be possible to add criteria.

mtarler's avatar
mtarler
Silver Contributor
Jan 18, 2024

alternatively use SWITCH or LOOKUP:
=SWITCH(B4, "Absent", 1, "Tardy", 0.33, "Left Early", 0.33, "FMLA", 0, "LOA", 0, "")
=XLOOKUP(B4, {"Absent", "Tardy", "Left Early", "FMLA", "LOA"}, {1, 0.33, 0.33, 0, 0}, "")

and better yet create a table for the IN-OUT and use LOOKUP on that table

=XLOOKUP(B4, [category range], [out value range], "")

Resources