You are viewing limited content. For full access, please sign in.

Question

Question

Forms Formula

asked on October 24, 2017

I am trying to insert some formulas into a Form I am working on.  I have been successful with some formulas so I have a general understanding but currently these seems to be beyond my ability :) 

The first one is I am trying to calculate a debt to income ratio where I take my Total Payments divided by my Gross Monthly Income and I would like to yield that at a percentage as seen in my example.

 

Second is to take a list of possible loan interest discounts and them up with a max of 1% discount then take my posted rate minus my discounts to come up with my final rate and display that at number and 2 digit percentage.  


Any help would be greatly appreciated!  Thanks

DTI.png
Discounts.png
DTI.png (32.18 KB)
Discounts.png (44.65 KB)
0 0

Answer

SELECTED ANSWER
replied on October 24, 2017

You should be able to use the MIN function.  To return the minimum of 1 or the calculated value.  

=MIN(SUM(INDEX(Discounts.A,1),INDEX(Discounts.B,1)),1)

or with the percent
=CONCATENATE(MIN(SUM(INDEX(Discounts.A,1),INDEX(Discounts.B,1)),1),"%")

0 0

Replies

replied on October 24, 2017 Show version history

For your DTI calculation you can use:

= IF( gross_monthly > 0,CONCATENATE(   round(  ((total_payments/gross_monthly)*100)  ,0),"%"),"")

0 0
replied on October 24, 2017

Thank you!  That worked!

0 0
replied on October 24, 2017

Here is what I have so far adding up my discounts which functions correctly to give a total but looking on how to add a maximum value in there of 1%.  

 

=SUM(INDEX(Discounts_1.Qualify,1),INDEX(Discounts_1.Qualify,2),INDEX(Discounts_1.Qualify,3),INDEX(Discounts_1.Qualify,4),INDEX(Discounts_1.Qualify,5),INDEX(Discounts_1.Qualify,6),INDEX(Discounts_1.Qualify,7),INDEX(Discounts_1.Qualify,8),INDEX(Discounts_1.Qualify,9))

 

Or do I need to go about it a different way and let this total all but have a hidden field which list 1% and the formula for my rate incorporates which ever is less?

 

0 0
SELECTED ANSWER
replied on October 24, 2017

You should be able to use the MIN function.  To return the minimum of 1 or the calculated value.  

=MIN(SUM(INDEX(Discounts.A,1),INDEX(Discounts.B,1)),1)

or with the percent
=CONCATENATE(MIN(SUM(INDEX(Discounts.A,1),INDEX(Discounts.B,1)),1),"%")

0 0
replied on October 24, 2017

Wow I could have never done this without your help.  Thank you.

 

The part that I thought would be easy then is taking my posted rate minus my discounts and get my final rate but I have not had luck with that.  It just keeps saying an error exist even if I scale it down to just +Posted_Rate_1-Total_Discount it still says I have an error. 

0 0
replied on October 24, 2017

You will probably need to check that your Posted and Total_Discount fields are numeric and not Single_Line entries.

0 0
replied on October 24, 2017

I originally had them as numeric but it showed as invalid once we put the % on there in the formula. 

 

On my Posted Rate what would you put in there to convert that to show the %.  I have in there "%" which is working but I wonder if that is part of my problem. 

0 0
replied on October 24, 2017 Show version history

That would break things with the %.

You can keep the Single_Line entries with the % sign.  Wrap your fields with the VALUE() function.   If you have the % in the value you will further need to multiply it by 100 so that it is actually treated as the full value and not the percentage.

=VALUE(Posted) - VALUE(Total)*100

0 0
replied on October 26, 2017

Using VALUE made my other fields show as invalid so I went back to those changed them back to a number field and took out the % in the formulas then I was able to accomplish the end result.  Again thank you so much for your help on this!  On to the next one where I add more :)

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.