+ Reply to Thread
Results 1 to 5 of 5

Excel formula / code for calculating value based on multiple cells

  1. #1
    Registered User
    Join Date
    07-11-2014
    Location
    Cochin
    MS-Off Ver
    MS Office 2007
    Posts
    7

    Post Excel formula / code for calculating value based on multiple cells

    Hi!

    Good day!

    I am new to ExcelForum, appreciate your support throughout!




    I need to get a formula to calculate the following:

    Assuming

    A1 has Loan Percentage values
    B1 has year range values

    C1 = result

    The suggestion i have made is just a Logic (may contain errors)


    :

    If the loan percentage is below 80.01% (starting from 80% to 1%)
    no calculation , need to write NA

    Sugg:
    (A1<=80 return "NA")

    If the loan percentage is 80.01% to 85%
    for 1 to 20 years 0.19%
    for 21 to 30 years 0.32%

    Sugg:
    IF(A1>80 <=85 AND IF B1 >=1 <=20 RETURN C1=0.19)
    OR IF
    (A1>80 <=85 AND IF B1 >=21 <=30 RETURN C1=0.32)




    If the loan percentage is 85.01% to 90%
    for 1 to 20 years 0.23%
    for 21 to 30 years 0.52%

    Sugg:
    IF(A1>85 <=90 AND IF B1 >=1 <=20 RETURN C1=0.23)
    OR IF
    (A1>85 <=90 AND IF B1 >=21 <=30 RETURN C1=0.52)


    If the loan percentage is 90.01% to 95%
    for 1 to 20 years 0.26%
    for 21 to 30 years 0.78%

    Sugg:
    IF(A1>90 <=95 AND IF B1 >=1 <=20 RETURN C1=0.26)
    OR IF
    (A1>90 <=95 AND IF B1 >=21 <=30 RETURN C1=0.78)




    If the loan percentage is 95.01% to 97%
    for 1 to 20 years 0.79%
    for 21 to 30 years 0.90%

    Sugg:
    IF (A1>95 <=97 AND IF B1 >=1 <=20 RETURN C1=0.79)
    OR IF
    (A1>95 <=97 AND IF B1 >=21 <=30 RETURN C1=0.90)




    IF the loan percentage found with above 97.01% to 100%
    no calculation , need to write NA

    Sugg:
    IF(A1>=97 <-100 RETURN "NA")


    Appreciate your early response , thank you

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Excel formula / code for calculating value based on multiple cells

    Welcome to the Forum.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    07-11-2014
    Location
    Cochin
    MS-Off Ver
    MS Office 2007
    Posts
    7

    Question Re: Excel formula / code for calculating value based on multiple cells

    Hi! thank your for your early reply!

    please help me with a formula to get the following result!

    As mentioned by you, i have attached an excel file with calculations and desired results: for further clarifications i am repeating the same as i have already posted: -

    Assuming (i have explained this only in terms of A2, B2 and C2)
    (A1,B1,C1 are heading in the file attached)
    A2 has Loan Percentage values
    B2 has year range values

    C2 = result

    The suggestion i have made is just a Logic (may contain errors)
    :
    If the loan percentage is below 80.01% (starting from 80% to 1%)
    no calculation , need to write NA

    Sugg:
    (A2<=80 return "NA")

    If the loan percentage is 80.01% to 85%
    for 1 to 20 years 0.19%
    for 21 to 30 years 0.32%

    Sugg:
    IF(A2>80 <=85 AND IF B2 >=1 <=20 RETURN C2=0.19)
    OR IF
    (A2>80 <=85 AND IF B2 >=21 <=30 RETURN C2=0.32)



    If the loan percentage is 85.01% to 90%
    for 1 to 20 years 0.23%
    for 21 to 30 years 0.52%

    Sugg:
    IF(A2>85 <=90 AND IF B2 >=1 <=20 RETURN C2=0.23)
    OR IF
    (A2>85 <=90 AND IF B2 >=21 <=30 RETURN C2=0.52)

    If the loan percentage is 90.01% to 95%
    for 1 to 20 years 0.26%
    for 21 to 30 years 0.78%

    Sugg:
    IF(A2>90 <=95 AND IF B2 >=1 <=20 RETURN C2=0.26)
    OR IF
    (A2>90 <=95 AND IF B2 >=21 <=30 RETURN C2=0.78)



    If the loan percentage is 95.01% to 97%
    for 1 to 20 years 0.79%
    for 21 to 30 years 0.90%

    Sugg:
    IF (A2>95 <=97 AND IF B2 >=1 <=20 RETURN C2=0.79)
    OR IF
    (A2>95 <=97 AND IF B2 >=21 <=30 RETURN C2=0.90)



    IF the loan percentage found with above 97.01% to 100%
    no calculation , need to write NA

    Sugg:
    IF(A2>=97 <-100 RETURN "NA")


    Appreciate your early response , thank you
    Attached Files Attached Files

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Excel formula / code for calculating value based on multiple cells

    Please find the attached to see if this is something you can work with.
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Excel formula / code for calculating value based on multiple cells

    Find the attached format - I made some changes to that format
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-20-2012, 10:08 AM
  2. Replies: 2
    Last Post: 02-24-2012, 12:36 PM
  3. Replies: 2
    Last Post: 11-06-2011, 08:16 PM
  4. VBA code for inserting formula, based on another cells text value.
    By Pancho3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-05-2011, 04:30 PM
  5. [SOLVED] color code cells based on golddigger formula
    By Network Guru in forum Excel General
    Replies: 2
    Last Post: 03-22-2006, 11:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1