Forum Discussion
List Calculated Field with table reference
Hi, in a SharePoint List, I need a calculated field, and I wonder if it's feasible with just JSON. Users already input: location (drop-down list) & year. Based on those 2 values, I input manually a labour rate, using a reference table. Is it possible (without transtionning to PowerAutomate) to have a calculated field spitting out that labour rate? Would a very long list of 'if statements' work?
If the number of combinations for location and year is relatively small, you can maybe use nested IF statements in a calculated column. Here is an example of how you might set it up:
- Add a Calculated Column:
- Go to your SharePoint list settings.
- Click on "Create column".
- Choose "Calculated (calculation based on other columns)" as the column type.
- Enter your formula in the "Formula" box.
Here is an example formula using nested IF statements:
=IF(AND([Location]="Location1",[Year]=2023), 30, IF(AND([Location]="Location1",[Year]=2024), 35, IF(AND([Location]="Location2",[Year]=2023), 32, IF(AND([Location]="Location2",[Year]=2024), 36, "Unknown"))))
This formula checks combinations of Location and Year and returns the corresponding labor rate. Expand the IF statements to cover all combinations. The text, steps and formulas was created with the help of AI
Hope this will help you.
My answers are voluntary and without guarantee!
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
If the number of combinations for location and year is relatively small, you can maybe use nested IF statements in a calculated column. Here is an example of how you might set it up:
- Add a Calculated Column:
- Go to your SharePoint list settings.
- Click on "Create column".
- Choose "Calculated (calculation based on other columns)" as the column type.
- Enter your formula in the "Formula" box.
Here is an example formula using nested IF statements:
=IF(AND([Location]="Location1",[Year]=2023), 30, IF(AND([Location]="Location1",[Year]=2024), 35, IF(AND([Location]="Location2",[Year]=2023), 32, IF(AND([Location]="Location2",[Year]=2024), 36, "Unknown"))))
This formula checks combinations of Location and Year and returns the corresponding labor rate. Expand the IF statements to cover all combinations. The text, steps and formulas was created with the help of AI
Hope this will help you.
My answers are voluntary and without guarantee!
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
