Formula to check a box if there is a discrepancy in pay rates for one person

Options

Hello! I need a formula that will look at all of the line items of an employee ID and then look at the column for the pay rates and if they are different, to check a box:

This screenshot has 3 line items for one person and then in the pay rate section there are 2 pay rates. Since there are different rates, I need it to check a box.

Thank you so much for your help!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Heather, Sounds like you have an issue with your range names, column properties or the @row value. The pieces of the formula should have returned values. All of the columns used in the formula should be formatted as text/number. Confirm the column names used in the formula match your actual column names. When you click on the formula the referenced columns and cells should highlight. If they don't you have a naming issue. Good luck! Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Try entering this formula into a checkout column:

    =IF(Count(distinct(collect([pay rate]:[pay rate], [empl id]:[empl id], [empl id]@row, [title / type]:[title / type], [title / type]@row))>1, 1, 0)

    It collects the pay rate for rows that match the empl id and title/type of the row. Then it counts the distinct values. If there is more than 1 it checks the box.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Heather Mize
    Heather Mize ✭✭✭✭
    Options

    Hi there! It comes back with #INCORRECT ARGUMENT SET. Screenshot below with the formula entered. Further thoughts? (I am so thankful for your assistance! I am not very experienced with formulas and these seemed like they'd be easy, but proving to not be at all! LOL)



  • Heather Mize
    Heather Mize ✭✭✭✭
    Options

    @Mark Cronk - After watching some tutorials I saw there was just 1 parenthesis missing (added it below)

    =IF(COUNT(DISTINCT(COLLECT([Pay Rate]:[Pay Rate], [Emp ID]:[Emp ID], [Title / Type]:[Title / Type], [Title / Type]@row))) > 1, 1, 0)

    So I added it into the formula you provided and the checkbox cell remained blank. Which was amazing! NO ERROR! However, I changed the pay rate on one of them to see what it would show me if there was more than 1 pay rate for a employee ID and it remained blank. So it's not calculating if there is two different pay rates associated to one employee ID. Any additional suggestions?



  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Your on the right track. Try:

    =IF(COUNT(DISTINCT(COLLECT([Pay Rate]:[Pay Rate], [Emp ID]:[Emp ID], [emp ID]@row, [Title / Type]:[Title / Type], [Title / Type]@row))) > 1, 1, 0)

    You were missing [emp ID]@row.

    If this doesn't work, try breaking the formula into pieces to find the error.

    Change the column to a text/number and try:

    =Join(COLLECT([Pay Rate]:[Pay Rate], [Emp ID]:[Emp ID], [emp ID]@row, [Title / Type]:[Title / Type], [Title / Type]@row)). You should get a string of pay rates.

    Then try:

    =join(DISTINCT(COLLECT([Pay Rate]:[Pay Rate], [Emp ID]:[Emp ID], [emp ID]@row, [Title / Type]:[Title / Type], [Title / Type]@row)). You should get a string of unique pay rates.

    What part is giving an incorrect response?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Heather Mize
    Heather Mize ✭✭✭✭
    Options

    Oh yikes! So I was trying to break it all apart most of the day to try and figure out what was incorrect and didn't realize I had a piece missing on my final formula.

    The results for the first formula resulted in the box remaining blank on all cells, even those that should have indicated a different pay rate was seen.

    When I changed it to text (those with the above formula were showing with a value of 0 on each cell as well) and tried the second and the third formulas separately resulted in a #NO MATCH on all cells.

    No string of payrates for any formulas appeared.

    I'll try breaking it apart more tomorrow. I really really appreciate your help with this! I don't know what I'd do without it!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Heather, Sounds like you have an issue with your range names, column properties or the @row value. The pieces of the formula should have returned values. All of the columns used in the formula should be formatted as text/number. Confirm the column names used in the formula match your actual column names. When you click on the formula the referenced columns and cells should highlight. If they don't you have a naming issue. Good luck! Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Heather Mize
    Heather Mize ✭✭✭✭
    Options

    OMG I got it!!!!

    Your formula was right on, I just removed the Title / Type from it and it worked!!!

    =IF(COUNT(DISTINCT(COLLECT([Pay Rate]:[Pay Rate], [Emp ID]:[Emp ID], [Emp ID]@row))) > 1, 1, 0)

    You have NO IDEA how much I appreciate you!!! Thank you SOO very much for sticking with me and continuing to help trouble shoot ideas. I have been at this for a week (little embarrassed to say that, lol) and what a fabulous Friday it will be now! :)

    Thanks again! I hope you have a fantastic weekend!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Well done. Glad you figured it out. Thank you for contributing to the community. And, thank you for the "insightful" vote. You earned me a new badge! TY.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!