+ Reply to Thread
Results 1 to 5 of 5

How can I auto fill a column by a stepped amount until a variable max value is exceeded?

  1. #1
    Registered User
    Join Date
    03-21-2015
    Location
    Milwaukee
    MS-Off Ver
    2010
    Posts
    16

    Question How can I auto fill a column by a stepped amount until a variable max value is exceeded?

    I am trying to automatically fill (or series fill) a finite (but variable) length column with a linear series of numbers that start at zero, increase at a constant step rate, and then automatically stop when the filled value equals or exceeds the value of a selected cell containing a number entered by a non-technical user of the worksheet. This column of numbers is then used as the X-values (domain) for an exponential function based on other parameters entered by the user. That part's all figured out.

    Any thoughts on how to fill in a column of numbers automatically with out using the Auto Fill pulldown or the Series Fill function?

    Those two ways don't appear to solve my problem.

    Is there something like a combination of a simple calculation with a programmer's IF function in Excel?
    I was tolerably acquainted with Visual Basic some years back, but I've forgotten it all.

    I know about 33% of Excel quite well, but the other 67% is a deep, dark mystery.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,261

    Re: How can I auto fill a column by a stepped amount until a variable max value is exceede

    If you wanted the fill to stop when you reach the maximum, then you will need to use VBA. However, you can use a formula that returns blanks (or #N/A so it won't plot on a graph) beyond the maximum, so it can be copied down as far as you think you might need. For example, put the step value in D1 (e.g. 2.4) and the maximum in D2 (e.g. 29) with appropriate labels in C1 and C2, and put 0 in A1 and this formula in A2:

    =IF(A1="","",IF(A1+D$1>D$2,"",A1+D$1))

    Copy this down to, say, A50, and you will have your series. Change the values in D1 and/or D2 to get another series. If you have your exponential function in column B, then you can start it with:

    =IF(A1="","",your_function)

    or:

    =IF(ISNA(A1),NA(),your_function)

    if the other function returns #N/A instead of "".

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-21-2015
    Location
    Milwaukee
    MS-Off Ver
    2010
    Posts
    16

    Re: How can I auto fill a column by a stepped amount until a variable max value is exceede

    I think what you suggested may solve my problem.
    The concept is clear, and with a little diligence I should be able to work out the details in short order.
    Thank you.

  4. #4
    Registered User
    Join Date
    03-21-2015
    Location
    Milwaukee
    MS-Off Ver
    2010
    Posts
    16

    Re: How can I auto fill a column by a stepped amount until a variable max value is exceede

    Your suggestion worked perfectly for my application.
    The nested IF function is quite clever.
    It reminded me of some of the programming I used to do decades ago.
    Thanks again.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,261

    Re: How can I auto fill a column by a stepped amount until a variable max value is exceede

    You're welcome.

    Thanks for marking the thread as SOLVED, and for the rep.

    Pete

+ 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. [SOLVED] Auto Fill Formula to Variable Range.
    By Trevasaurus in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-08-2013, 03:09 PM
  2. Replies: 1
    Last Post: 09-07-2011, 05:57 PM
  3. Another Auto Fill variable length column
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2006, 12:00 PM
  4. [SOLVED] Auto Insert Row When Column Amount Changes
    By Ronald \Tony\ Johnson in forum Excel General
    Replies: 2
    Last Post: 05-04-2005, 12:06 PM
  5. [SOLVED] Restrict input if amount is exceeded
    By Der Musensohn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2005, 07:06 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