Help!?! Formula to perform a series of equations and comparisons.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
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.
Photo of Rhonda

Rhonda

  • 82 Points 75 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
Rhonda,

There is good help here https://www.quickbase.com/db/6ewwzuuj?a=q&qid=6 as to how the formula functions work, with examples.  Since you are new to QuickBase and building formuals, I suggest that you break up your formula into separate fields somewhat like columns in excel. That way you can debug is in smaller pieces.

When you go to build a formula, the fields are listed at the side and there is also a list of functions to choose form - so in that sense its easier than Excel once you get the hang of it.

I offer training for new and experienced users if you would like to contact me off line via the information in my Profile.

Here are a few clues.

The function Today() returns the current date.
The expression Year(Today()) would return the number 2015, right today as it 2015.
The IF function is similar to Excel, but in fact easier to use.
The symbol to multiply, like Excel is *
The MIN function returns the MINimum of multiple values
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
>I know this is complex ...


No it is simple and fun. To get you comfy working with equations and to turn learning mathematics into a fun exercise I would suggest you visit your local library and check out all the videos in this television series:

Numb3rs Working for the FBI, a mathematician uses equations to help solve various crimes.
http://www.imdb.com/title/tt0433309/
These handy resources will answer all your questions concerning the Numb3rs episodes:
Numb3rs Math Activities http://www.math.cornell.edu/~numb3rs/
TVTropes on Numb3rs http://tvtropes.org/pmwiki/pmwiki.php/Series/Numb3rs
Imbued with your new appreciation for Mathematics you will quickly recognize that this formula should do the trick:
Min([Cost] * (100 - Min((Year(Today()) - [Year]) * [Item - Depreciation % per year], [Item - Maximum Depreciation] ), [Claimed Amount]))

The nested parentheses is usually what trips people up when entering longer formulas. So you might be interested in this version of the formula that uses what QuickBase calls "formula variables" to hold temporary results of your calculations:

var Number TempA = Year(Today()) - [Year];
var Number TempB = $TempA * [Item - Depreciation % per year];
var Number TempC = Min($TempB, [Item - Maximum Depreciation]);
var Number TempD = 100 - $TempC;
var Number TempE = $TempD * [Cost];
var Number Result = Min($TempE, [Claimed Amount]);

$Result

I hope there are no typos in my formulas as I have not tested them. Formula variables are documented here:

Formula Variables
https://www.quickbase.com/help/default.html#formula_variables.html
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
Nicely answered.  I just didn't have the energy last night.