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

Heather Mize

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!

Mark Cronk

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

Mark Cronk

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

Heather Mize

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

@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

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

Heather Mize

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

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

Heather Mize

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

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