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

1. ## 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. ## 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. ## 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. ## 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. ## 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

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