+ Reply to Thread
Results 1 to 5 of 5

Having trouble with returning a number/result if an entered value is within certain range

  1. #1
    Registered User
    Join Date
    11-11-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    6

    Having trouble with returning a number/result if an entered value is within certain range

    Hi all,

    I am somewhat of a novice with Excel so please excuse my ignorance. I am trying to write a formula which will ensure that a given number (percentage actually) is returned when the number entered into my entry field (B9) is within a certain range.

    The ranges and related values that I have are as follows:-
    Up to $1,000,000 : 0.250%
    1,000,001 - 2,000,000 : 0.225%
    2,000,001- 3,000,000 : 0.200%
    3,000,001 - 5,000,000 : 0.175%
    5,000,001 - 7,500,000 : 0.150%
    7,500,001 and over : 0.125%

    So, for instance if I were to enter a value of $4,250,000 into cell B9 it should return a value of 0.175% into cell C9.

    Does anyone have any advice as to how I can solve this issue? Any help you can provide will be greatly appreciated! I have attached my spreadsheet. Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Having trouble with returning a number/result if an entered value is within certain ra

    with Vlookup (see the attached file).
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    10-28-2012
    Location
    Poland
    MS-Off Ver
    Excel 2007/2010
    Posts
    94

    Re: Having trouble with returning a number/result if an entered value is within certain ra

    please see the attachement
    i use formula:
    =IF(B9<1000000;0,0025;IF(B9<2000000;0,00225;IF(B9<3000000;0,002;IF(B9<5000000;0,00175;IF(B9<7500000;0,15;0,00125)))))
    Attached Files Attached Files

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Having trouble with returning a number/result if an entered value is within certain ra

    Another way:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Having trouble with returning a number/result if an entered value is within certain ra

    Use a lookup
    =VLOOKUP(B9,Sheet2!$A$1:$B$6,2,TRUE)
    and put the table on another sheet
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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