Results 1 to 3 of 3

Thread: Calculation using table and two variable

  1. #1

    Calculation using table and two variable



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Ok, I hope I can explain this correctly...

    I am trying to create a formula that populates a cell from a table. The table as below:
    LVR/Loan value $300k-$500k $501k-$1,000,000
    81%-85% 0.5% 0.6%
    86%-90% 0.7% 0.7%
    91%-95% 0.8% 0.8%

    The outcome that I need is for the cell to calculate the following:
    LMI = Loan value x (The percentage from the table using the LVR and total lending amount).

    Current Debt LVR Loan value LMI
    $500,000 90% $400,000 XXX


    Please let me know if this makes no sense and thanks in advance for your help!

    Alex

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    If you list the headers in your first table to show only the lower bounds:

    e.g.

    LVR/Loan value $300,000 $501,000
    81% 0.5% 0.6%
    86% 0.7% 0.7%
    91% 0.8% 0.8%

    Then you can use a formula like:

    =I2*INDEX($B$2:$C$4,MATCH(H2,$A$2:$A$4),MATCH(I2,$B$1:$C$1))

    where your lower table is contatined in G1:J2, making I2 the first Loan Value, and H2 containing the LVR to look up.


  3. #3
    You are truly an excel master. Thank you very much. It has worked perfectly!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •