Numeric If Formula not working with three If's.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I am trying to calculate the deposit required amount with an option to override that number. 

Here is my Formula : 

If([System Cost]*0.1<1000, [System Cost]*0.1,[System Cost]*0.1>xxx.xx,xxxx,[Deposit Override]>=0, [Deposit Override])

The Deposit override part is not working. If i move it to the front in the formula it worlds. please help.                                 

Photo of Akash

Akash

  • 20 Points

Posted 4 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
Try this

If(

[Deposit Override]>=0, [Deposit Override],

[System Cost]<1000, round([System Cost]*0.1,0.01),1000)

That says, if there is a deposit override, use it, else if the system cost is under $1,000, charge 10% of the system cost rounded to the penny, else charge $1,000.


Photo of Akash

Akash

  • 20 Points
That works for the override but it wont show 10% of the system cost anymore. I need to show a maximum of $1000 deposit if 10% of the system cost is greater than 1000. If its lower than $1000 it needs to be 10% of system cost.  Really appreciate the quick response.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
Here is a simpler version which also adjusts the result.

If(

[Deposit Override]>=0, [Deposit Override],

min(1000, round([System Cost]*0.1,0.01)))
Photo of Akash

Akash

  • 20 Points
Still wont calculate the 10% or show $1000.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
What is in the field [system cost] on the record that you are testing on?  Are you sure it has a value in it?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
actually, there is an error in the first line

If(

[Deposit Override]>0, [Deposit Override],

min(1000, round([System Cost]*0.1,0.01)))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
s/b ">0" not ">= 0"
Photo of Akash

Akash

  • 20 Points
[System Cost] is a Numeric Currency field used for user input. It does have a value in there.
Photo of Akash

Akash

  • 20 Points
Great almost there. So its now calcualting the $1000 or less. But if i want to override the down payment to show zero how do i do that ? Thats why i had the [Deposit Override]>=0. Let me try doing >-1.
Photo of Akash

Akash

  • 20 Points
that didnt work lol.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
OK, now that I understand what you want ....

Change the field properties on the [Deposit Override] field to not treat blank as zero. Uncheck the box.

Then make the formula

If(

not IsNull([Deposit Override]), [Deposit Override],

min(1000, round([System Cost]*0.1,0.01)))
Photo of Akash

Akash

  • 20 Points
That worked perfect. If i didn't want it rounded off can I use : If(not IsNull([Deposit Override]), [Deposit Override],Min(1000, ([System Cost]*0.1)))
Seems to work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
I figured that if the system price was $1234.56, that you not want the Deposit to be $123.456. There is no 1/10th coin and you can't do fractional cents on VISA :)
Photo of Akash

Akash

  • 20 Points
that makes sense. You are a genius Sir. Thank you so much for all your help. Don't want to waste your time but why didn't my original formula work ?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
The main problems with your original foula was that checked he deposit override last, not first.

The IF statement checks the conditions in order and takes the first true one it finds.

Then there was the issue of needing to differentiate between a blank and a zero in the deposit override.
Photo of Akash

Akash

  • 20 Points
I see it goes in sequence. That makes complete sense. Thank you so much for all your help. You are a Master at this. Have used your other posts for help in the past. Thanks for all the hard work.