Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

*Help* Formula for different hourly rate for multiple workers

Topic Labels: Formulas
127 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

3 Replies 3

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

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.

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