Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: More of a formula question

  1. #1
    Board Regular
    Join Date
    May 2003
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default More of a formula question

    I have column A, where row 1 starts with 1 and each subsequent row adds 1. For example:

    Column A
    1
    2
    3
    4
    5
    ...
    100

    In column B, row 1 starts with 3. Row 100 ends with 1.

    I need a formula that will fill in column B with the appropriate value. Here's how it should look:

    ColumnA ColumnB
    0 3.00
    1 2.98
    2 2.96
    3 2.94
    4 2.92
    5 2.90
    6 2.88
    7 2.86
    8 2.84
    9 2.82
    10 2.80
    ...
    100 1.00
    Last edited by allen121; Nov 30th, 2017 at 09:49 PM.

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,398
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: More of a formula question

    If you put 3.00 in B1 and 2.98 in B2, select both cells and double click the bottom right hand corner of B2 it will autofill to where your data in column A ends in that pattern.

    If column A was empty you would put 3.00 in B1 and 2.98 in B2, select both cells and left click and hold the bottom right hand corner of B2 and drag down to row 101.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Oct 2002
    Location
    Sydney, Australia
    Posts
    781
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More of a formula question

    Hi allen121

    This will work (place in B1 and drag down to B100):

    Code:
    =3-(((A1-1)/100)*2)
    except that your data sets are inconsistent across the two examples.

    Your first row in your first data set starts at 1 (A1 = 1), yet your second data set starts at 0. Which is correct?

    Cheers

    pvr928
    Work: Excel 2010 64 bit
    Home: Excel 2016 64 bit

  4. #4
    Board Regular
    Join Date
    May 2003
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More of a formula question

    Sorry about that. The second data set is the correct one.

    ColumnA ColumnB
    0 3.00
    1 2.98
    2 2.96
    3 2.94
    4 2.92
    5 2.90
    6 2.88
    7 2.86
    8 2.84
    9 2.82
    10 2.80
    ...
    100 1.00

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Location
    Sydney, Australia
    Posts
    781
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More of a formula question

    Then:

    Code:
    =3-(((A1)/100)*2)
    in B1 and copy down to B101.

    Cheers

    pvr928
    Work: Excel 2010 64 bit
    Home: Excel 2016 64 bit

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,398
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: More of a formula question

    and what I posted in post number 2 still stands.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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