*Help* Formula for different hourly rate for multiple workers

[Aaron_Preston]

Bit of a tricky one for me, im looking for a formula for this situation with a multiple select box:
if ‘Jon’ selected =£18,
if ‘Carl’ selected =£20,
if ‘Jon’&‘Carl’ or ‘Carl&Jon’ selected £38

just a snippet of what im after, i have 7 opperatives on different rates, basically looking to then multiply by hours on site to get a final expenditure.

Any help i would be greateful

[TheTimeSavingCo]

EDIT: Hey Aaron, here’s how you would accomplish this with a formula, but I do not think you should do this and recommend @Justin_Barrett’s suggestion below instead

We bundle everything into a single formula field that checks a multiple select field
e.g.

IF(
  AND(
    FIND('Jon', People),
    FIND('Carl', People)
  ),
  38,
  IF(
     FIND('Jon', People),
     18,
     IF(
      FIND('Carl', People),
      20
    )
  )
)

I do not recommend this as it would get super confusing after awhile; and adding / removing people would be a huge pain with modifying the formula

===

Edit: Also deleted my second suggestion that involved multiple formula fields as @Justin_Barrett’s solution is much much better

[Justin_Barrett]

Welcome to the community, @Aaron_Preston! :grinning_face_with_big_eyes: Because you’ve got data that’s uniquely tied to each worker, I recommend creating a [Workers] table. Each worker would have a record in the table, and a {Rate} field would contain each worker’s unique rate.

With that set up, you link the records in your main table to as many workers as needed from the [Workers] table. To bring in their respective rates, add a rollup field. This would retrieve the value from the {Rate} field, with a SUM(values) aggregation formula adding it all together. No matter the order of the links, each worker’s unique rate would be added to the total, removing the need for complex formulas.

[Aaron_Preston]

Thank you for the response! you can tell im new at this, ive realised over night that the plan i had for these feilds would not work in the grand scheme of thuings for what im trying to achieve due to the mathmatics.
Back to the drawing board i go!

Thank You