# *Help* Formula for different hourly rate for multiple workers

Topic Labels: Formulas
322 3
cancel
Showing results for
Did you mean:
4 - Data Explorer

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
16 - Uranus

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

18 - Pluto

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.

4 - Data Explorer

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