Calculation Condition with Multiple "IF" Statements Not Working for me

Ultimately trying to build conditions that calculate a lease rate based on multiple parameters.  End goal is to construct a method for deploying the logic demonstrated in the excel table below:

My lease rate field should be "calculated" based on the principal amount range and the number of months in the contract term.  Each possible "situation" has a defined rate.

When I try setting this up, it's as if the form doesn't know how to react to the multiple "IF" parameters.  See below:

The table of conditions above (2nd screenshot) is not completed as compared to the excel spreadsheet (1st spreadsheet), but the configured parameters are enough to satisfy the test inputs on my form (screenshots 3 & 4). 

I've been able to create a "dumbed-down" version of this logic (in a different form) but I am forced to error on the high side which often causes big discrepancies in my figures.  Instead of being so granular with the principal amount ranges, I've been able to use the highest rate for all ranges and base the output strictly on the term parameter.  However, this is not an ideal solution (see below). 

Can you please help me find the correct way to calculate the appropriate rate based on my many parameters? 

I could not find the excelsheet you mentioned in your initial post.

Please provide all the 4 screenshots referred in the post so we can understand the situation properly.

To insert the screenshot to the thread, please follow the steps below :

1. Click on Insert/Edit image button

2. Click on the "Upload" button

3. Click on Choose File and look in your local folders.

4. Click on Submit

You can set multiple "IF" conditions in order to get an exact match of what you wish to happen.

So on this end, you might have a long list of conditions in order to complete that table. 

But I also would like to try creating your form and see if there is something conflicting over conditions that may prevent your conditions to function properly. With this, can you tell us which part of your form would represent the Principal amount?

Thanks.

This form compiles data for two different engineered solutions.  Each solution carries its own "Principal Amount".  Those fields are either titled "SIP NRC Calculation" or "Hosted NRC Calculation".  I'm wanting to use condition calculations to define the appropriate rate and have that rate input to the "... Rental Rate" field for either solution.  I would like to then multiply the rental rate field by the NRC Calculation field and have the product appear as the value of the "$1BO Lease Calculation" field. 

Hope this makes sense...

I'm afraid we need more help from you in resolving this. Since there are many conditions and many fields so I'm just not sure how it should work.

Could you please explain more which exactly calculation is not working properly for you, which condition?

And how it should work. You can use screenshots to show us.

That would help a lot.

Thank you!

My form that I originally referenced when I opened this support ticket was under a deadline so I had to make a poor work-around and it's been published to my users.  Therefore, as we continue to troubleshoot this issue, we will need to reference this form which is nearly identical (https://form.jotform.com/62384816757165)

Once we find a solution to my problem, I'll find an opportune time to go back to the other form and update accordingly. 

Anyways, there are 3 fields in my form that I need to choose the 1 out of 24 possible rate factors (referencing excel snapshot above) available.  These fields are titled "Rental Rate (%)" (field 32), "SIP Rental Rate (%)" (field 47), & "Hosted Rental Rate (%)" (field 89).  Currently, I'm using some conditions that "get me by" but are not quite what I need (see new screenshots 1-5).  The work-around basically uses the highest possible rate factor (associated with the lowest principal amounts) for all principal amounts and just determines which term length should be referenced. 

I need the conditions for those rental rate fields to also factor in the "Principal Amount" parameter defined in the previously provided excel screenshot.  This would allow me to define a more precise rate so that my other calculation fields ("Additional Monthly Charges" #33, "SIP $1BO Lease Calculation (Monthly)" #52, & "Hosted $1BO Lease Calculation (Monthly)" #94) are much more accurate.  My theory would look similar to screenshot 6, but with many more conditions. 

However, the 2-3 times I've attempted to make these "IF" conditions so granular, it's like the form doesn't know how to accomplish what I've defined and doesn't provide any rate (see screenshot 7 & 8). 

Referencing the excel sheet again, I need my form to use the selected term (field #8 "Contract Term (Months)") and compare that against the "Principal Amount" ranges (calculated by fields #30, #51, & #93) to find the appropriate rate factor.  Now, just for clarity, the rate fields mentioned above (#32, #47, & #89) need to be independent from each other.  In other words, rate field #32 needs to use field #30 as its "Principal Amount", rate field #47 needs to use field #51 as its "Principal Amount", & rate field #89 needs to use field #93 as its "Principal Amount". All three rate fields should reference field #8 for the "Term" length. 

Hope this clears up my intention.  Let me know if there's anything else I can further clarify.  (Sure would make things A LOT EASIER if we could actually have a conversation over the phone instead restricting your support to EXCLUSIVELY email...)

Hello,

We're sorry for the inconvenience you had with the calculation conditions.

First of all, let me inform you about that: we had some issues with the calculation conditions 2 days ago and our software developers have fixed it. So, calculation conditions you have set should work properly, currently and you will not have any problems, hopefully.

Besides, I have cloned your form named "Voice Services Payment Calculator (Internal)" on my side in order to test it. I have seen that Hosted Rental Rate field did not be calculated because your one of the calculation widget has wrong configurations as seen below:

The dollar sign ($) should be a string, not a number. Please be careful about this and click on the $ sign in the widget in order to make it a string.

Moreover, your conditions for the Hosted Rental Rate field overlaps. when contract terms field equals to 12, Hosted Rental Rate has been calculated properly now. However, your other conditions related to this field are not logically correct as seen below:

Because for example, Hosted NRC Calculation could be $0 or could be less than 3000. So in this case, the conditions will not work. You need to add extra conditions for these cases.

Also, I would recommend you to remove the $ sign when setting calculation conditions. It can cause some issues.

Please test more and if you need further assistance, please let us know.

Thanks.

If you wouldn't mind, I would like to see if you can make this work on your cloned form.

I'm not sure I'm convinced the dollar sign ($) in my "Hosted $1BO..." calculation field is causing any issues. I have no problem changing it to a string, but it's been set as a number for months and works just fine when I use my "work-around" conditions defined in my previous posts. 

Also, I'm not sure I agree with the statement about my conditions "overlapping". I don't see anywhere that the condition parameters overlap. Yes I understand my current conditions are an incomplete list (compared to the excel screenshot), but as I previously stated I only defined a couple of them to demonstrate my issue. The full list would consist of 24 different conditions and I felt it not worth the time to create all of them (again) just to demonstrate the problem so we could troubleshoot. 

To say the "hosted rental rate has been calculated properly..." "when contract terms field equals to 12" is not quite true either. The current condition for 12 month terms is the less effective work-around that I had previously been using. It is most certainly not defined as I would like or even anywhere close to what is demonstrated in the excel screenshot. 

Please further explain the mistakes I've made and/or properly execute my desired conditions in your cloned form so that I can better understand how to resolve this issue. 

Here's my colleagues cloned form:

https://form.jotformpro.com/71094341831957

You can see that changing the $ to a string has caused the calculation to work, otherwise, the widget wouldn't recognize the numbers and perform the calculations. I'm not sure how it worked for you before, but it is recommended that you remove the $ sign.

The conditions are not overlapping, but as you stated, they are not defined to cover more range of numbers, which can cause the calculation to break, whenever there's a value outside these conditions.

I recommend that you remove the $ sign from your calculations, and do some testing within the defined conditions, and slowly add more conditions to test the outcome, if the issue persists, then please get back to us with details about it so we can break things down and work on it, as it is quite a lengthy form and things can be missed out quite easily.

Thank you.