+ Reply to Thread
Results 1 to 5 of 5

Varying number in one cell, to change to a set number in another

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    34

    Varying number in one cell, to change to a set number in another

    The title is a little confusing, but what I would like to do is this;
    in cell 1A, I input a number between 1 & 10 (representing years), in cell1D the value would change depending upon which value you placed in 1A,
    1 = 0.99
    2 = 0.97
    3 = 0.95
    4 = 0.93
    5 = 0.91, and so on. This will be the divisible for cell 1Cto give an answer in 1F.

    I'm not sure this is possible, but if it is and I could get a solution it would really make my day
    Thank you in advance for your efforts

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Varying number in one cell, to change to a set number in another

    enter that range in a small table. then use a vlookup() to find what you need.

    =vlookup(A1,your-table-range,2,false)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-01-2012
    Location
    New Orleans, US
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Varying number in one cell, to change to a set number in another

    The vlookup above would work nicely. You could also use an if formula. It would look something like this:

    =IF(A1=1,0.99,IF(A1=2,0.97,IF(A1=3,0.95,IF(A1=4,0.93,IF(A1=5,0.91,""))))

  4. #4
    Registered User
    Join Date
    10-26-2012
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Varying number in one cell, to change to a set number in another

    Thank you both for your help, the vlookup solution worked best, the pricing list I'm working on works a treat now. A pity you can only give cred to one helper per thread

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Varying number in one cell, to change to a set number in another

    happy to help, and thanks for the rep

+ 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