There are different tiers in calculating a fee for title insurance based off of the loan amount. The cells in J12:L15 are the tiers and L12:L15 are the rate and K12:K15 are the base rates for that tier. For example if the loan amount was $450,000.00 the formula would be C12*L13+K13
The value should be calculated in cell G11 (ALTA insurance, tried using an if then then realized there were more than two tiers)
I have attached the spreadsheet it would be on the setup page
Does the below in G11 work as you'd expect?
Formula:
=C12*INDEX(L12:L15,MATCH(C12,J12:J15,1))+INDEX(K12:K15,MATCH(C12,J12:J15,1))
BSB
EDIT: Sorry, forgot to mention that I too had amended cells J12:J15 to show the lower boundary value only.
Last edited by BadlySpelledBuoy; 04-20-2016 at 03:32 PM.
See attached. It's a common mistake, I've seen two similar ones today. By all means have a single cell that shows the band range but a VLOOKUP table only needs to know the band boundaries. I've added a new column K which contains just the band start number and then the VLOOKUP is
Formula:=C12*VLOOKUP(C12,K10:M15,3,TRUE)+VLOOKUP(C12,K10:M15,2,TRUE)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
For simplicity you could use the DGET function to fetch the two values.
Last edited by EchoPassenger; 04-20-2016 at 03:58 PM.
Thank you all! Richards worked best for me, I can use a similar formula for purchase scenarios
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks