I need help with a formula please.

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    The community is here to help!

    Let us know what you're struggling with, and we'll do our best!

    Break it down, and provide screenshots to help paint the picture.

  • Thanks. I am looking to create a sheet to track attendance points.

    I would use a drop down menu with various options. Each option will have an assigned point value per associate. So each associate will be assigned a row, and their Supervisor will choose the correct drop down option daily. I would also like to have a column that keeps a sum of points, so that we can assign any disciplinary action according to our policies.

    First question: How do I assign a point value to each of the drop down options.
    2nd: How do I keep track of the accumulated points per associate.

    Example of the layout.

    Thanks for the help.

  • =IF([THU]@row = "Tardy", 0.5, IF([THU]@row = "NCNS", 6, IF([THU]@row = "AWOL", 1,)))

    This has been my attempt to assign a point value to a couple of our dropdown options. I am getting an error message.

  • heyjay
    heyjay ✭✭✭✭✭
    edited 07/09/24
    =IF([THU]@row = "Tardy", 0.5, 
    IF([THU]@row = "NCNS", 6,
    IF([THU]@row = "AWOL", 1, "Otherwise"
    )))
    

    Just fixing your formula, seems like you have an extra comma or missing 'IF FALSE' arguement.

    2nd: How do I keep track of the accumulated points per associate.

    You can use SUM function. But your formula might be longer than expected.

    With limited view of your sheets, and without adding helper column I would multiply the count each status with its weight and then add them. Something like:

    ATTENDANCE POINTS 
    =SUM(
    COUNTIF(THU@ROW:THU(9)@ROW, "Tardy") * .5,
    COUNTIF(THU@ROW:THU(9)@ROW, "NCNS") * 6,
    COUNTIF(THU@ROW:THU(9)@ROW, "AWOL") * 1)
    

    ...

  • Thank you so much. This worked perfectly!!!! @heyjay

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!