IF/AND formula to find new value for some data but leave other data alone

[Kristina_Cadle]

Hello!
My airtable base gets new data imported into it every week from a separate report that is run. One of the drawbacks of the report is that it calculates revenue for our contracted associates, but not for our direct hires. In order to find the revenue for our direct hires, we have to divide the direct hire fee (DP Fee) by 13.

Since we have the data for the contracted associates, is there a way to manipulate this field to leave certain data alone, but also divide other data by a factor?

Screenshot below of data set to give you a better idea! Note that the field in question is “PIT Spread”. We are currently calculating these values for direct hires prior to adding new data to our database.

[Reid_Thomas]

Hey,

If I am understanding you would like to divide numbers in the DP Fee field by 13. The way I would go about this would be to make a Formula field using the formula ‘{DP Fee} /13’. This should give you the selected outcome.
If you would only like to do this with certain numbers in the DP Fee field then I would suggest adding tags for direct hires and then rolling up only the values with tags associated.

Best of luck!

[Kristina_Cadle]

Sorry, so yes, I would like that equation to be added to the PIT Spread field, but only for DPs. The PIT Spread field also contains data already for our contracted associates, so I would like to know how to leave the existing data in the PIT Spread field alone while at the same time calculating PIT Spread for DP.

Hopefully this is more clear!

[Justin_Barrett]

Welcome to the community, @Kristina_Cadle! :grinning_face_with_big_eyes: One way to do this would be to make a formula field as @Reid_Thomas suggested that calculates the end value, dividing {DP Fee} by 13. Create a new automation that triggers when {DP Fee} is not empty (i.e. it’s a direct hire). The only action needed is an “Update record” action, which will take the value from the formula field and copy it to {PIT Spread}.

[Kristina_Cadle]

Thank you! How do I add tags for direct hires? New to this! :upside_down_face:

[Justin_Barrett]

I’m guessing that @Reid_Thomas is referring to something like a single select field, where you manually mark records as direct hires vs contractors. However, if the data itself can provide an indicator of which is which—like the {DP Fee} only containing data for direct hires and remaining empty for contractors—then you can save yourself the extra effort of manually marking each record and use that built-in logic to get the job done as I described above.