I have a field called Offer (formula, numeric). This can be changed if necessary. I need it to perform a pretty complex series of steps and equations and return a value. The fields involved are labeled below exactly as they are in my application and the steps I need it to perform follow that. I do not even begin to have the formula building skills to make this happen. I do have it happening in another application but it does it using many many fields that are not visible to the user that essentially perform each step and build upon each other to provide the final result but in our new application, I’m not going to get away with that. I need this to be as clean as possible and I know there is a much better way to do it than what I already have. Any help would be greatly appreciated!

Fields:

Year: Numeric field, 4 digit year. EX: 2011 (The data that provides the information for this field is provided by the government and as such, I cannot change what they give me. I will never have the month and day so I cannot make this a date field which I know would be more convenient to obtain a duration as set out in step 1)

Cost: Numeric field, Currency. EX: $250.00

Claimed Amount: Numeric field, Currency. EX: $200.00

Item - Depreciation % per year: Numeric field, Percent, lookup from a related table. EX: 10%

Item - Maximum Depreciation: Numeric field, Percent, lookup from a related table. EX: 75%

Equation:

1. Subtract current year (2015 right now but I would need it to know to use 2016 when appropriate) from year in [Year] field (in this case, 2011) to obtain an age in years. In this example, that would be 4.

2. Then, multiply the age by the percent in the [Item - Depreciation % per year] field. So 4 x 10% = 40%

3. Compare the value from step 2 to the value in [Item - Maximum Depreciation] and select the lesser of the two for step 4. In this example: 40% is less than 75%, so it would use the 40%.

4. Subtract the lower of the two percent’s from 100%. In this example: 100% – 40% = 60%

5. Multiply the [Cost] field by the result of step 4. In this example: $250 * 60% (or .60) = $150.00

6. Compare the result of step 5 to the [Claimed Amount] field and return the lower of the two values. In this example, the final result would be $150.00 since the [Claimed Amount] is $200.00.

I know this is complex and while I need it to do exactly that, I am sure I do not know the best way to achieve it. I’m not certain if it can be neatly rolled in one formula or will take more than one field to based on the data types.

Thank you so much for your help.

Fields:

Year: Numeric field, 4 digit year. EX: 2011 (The data that provides the information for this field is provided by the government and as such, I cannot change what they give me. I will never have the month and day so I cannot make this a date field which I know would be more convenient to obtain a duration as set out in step 1)

Cost: Numeric field, Currency. EX: $250.00

Claimed Amount: Numeric field, Currency. EX: $200.00

Item - Depreciation % per year: Numeric field, Percent, lookup from a related table. EX: 10%

Item - Maximum Depreciation: Numeric field, Percent, lookup from a related table. EX: 75%

Equation:

1. Subtract current year (2015 right now but I would need it to know to use 2016 when appropriate) from year in [Year] field (in this case, 2011) to obtain an age in years. In this example, that would be 4.

2. Then, multiply the age by the percent in the [Item - Depreciation % per year] field. So 4 x 10% = 40%

3. Compare the value from step 2 to the value in [Item - Maximum Depreciation] and select the lesser of the two for step 4. In this example: 40% is less than 75%, so it would use the 40%.

4. Subtract the lower of the two percent’s from 100%. In this example: 100% – 40% = 60%

5. Multiply the [Cost] field by the result of step 4. In this example: $250 * 60% (or .60) = $150.00

6. Compare the result of step 5 to the [Claimed Amount] field and return the lower of the two values. In this example, the final result would be $150.00 since the [Claimed Amount] is $200.00.

I know this is complex and while I need it to do exactly that, I am sure I do not know the best way to achieve it. I’m not certain if it can be neatly rolled in one formula or will take more than one field to based on the data types.

Thank you so much for your help.