# Numeric If Formula not working with three If's.

• 0
• Question
• Updated 4 years ago

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.

• 32 Points

Posted 4 years ago

• 0
• 72,478 Points 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.

• 32 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.
• 72,478 Points 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)))
• 32 Points
Still wont calculate the 10% or show \$1000.
• 72,478 Points What is in the field [system cost] on the record that you are testing on?  Are you sure it has a value in it?
• 72,478 Points actually, there is an error in the first line

If(

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

min(1000, round([System Cost]*0.1,0.01)))
• 72,478 Points s/b ">0" not ">= 0"
• 32 Points
[System Cost] is a Numeric Currency field used for user input. It does have a value in there.
• 32 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.
• 32 Points
that didnt work lol.
• 72,478 Points 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)))
• 32 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.
• 72,478 Points 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 :)
• 32 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 ?
• 72,478 Points 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.
• 32 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.