Range Variable in Excel

[jonesn3]

I'm trying to reproduce some of my Mathcad sheets in MS Excel format. One of the biggest hurdles is a finding a simple way to define a range (or column in the case of a spreadsheet) of dependent values that are defined by a start, interval, and end value similar to Mathcad's range variable. I'm guessing I'm going to need to implement VBA, but my skills with that are pretty minimal. 

Does anybody have suggestions or examples?

[LucMeekes]

No programming required.

The range definition

x:=1,1.1...2

Translates to Excel as entering the first to items af the range definition in two subsequent cells and select them:

Then you place your cursor this very tiny square:

Press the left-mouse button and move downwards to cells below until the value in a cell reaches 2.

The result is:

The same can be done for a row of cells...or for rows of cells

Success!
Luc

[ttokoro]

[LucMeekes]

Nice,

But it requires the list in column C. How did you get that?

Otherwise formulated: I see you need a range to construct a range...

Luc

[Fred_Kohlhepp]

The "range" does not require column C; and there are myriad formulas to create the proper sequence.

[JBlackhole · Accepted solution]

use the Fill / Series option ? or a simple macro (using the capability)

dstep = 0.1: dend = 1.0
ActiveCell.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=dstep, Stop:=dend, Trend:=False

[Fred_Kohlhepp]

We had an co-op, a very bright student from an ivy league school--very capable.  We tasked him with monitoring and analyzing amplifier drift--one of the "bookkeeping" jobs in experimenting.  He was to keep the data in EXCEL on a shared drive so anyone who needed it had access.

Several days in I asked him why the averages and standard deviations weren't shifting from the first day's values.  He reached past me and typed a key combination (which ran a macro) and the values updated to the new (correct) values.  I asked him why he hadn't used the EXCEL formulas (=average( cell range)), and was astounded that he didn't realize he could--he only knew to write macros and used the sheet as a display.  (On the other hand, I only used sheet equations and dreaded having to develop a macro.)  

You're comfortable with what you're used to!

[jonesn3]

I'm looking for a solution along the lines of the macro that JXB suggested, although I would like to make "dstep", and "dend" variables defined by manual inputs into the spreadsheet before running the macro. 

Apologies, I completely understand this isn't a spreadsheet community. 

[JBlackhole]

As pointed out it's not an excel forum but I have to ...

Can (easily) make the "dstep" and "dend" an input to the function/sub (or picked up by the macro)

Indeed using the built-in functions is always the best way to start. I sometime  try to push the boundary but I don't like having very long formula in 1 cell (a couple of ifs and that's it) otherwise it's a pain to check - some people relish such stuff. In the case of the fill option (dragging cells) it can be a pain as if one wants to reduce the step then one has to drag a lot (downward) and remember to update some ranges, etc (unless use "dynamic" range).  A macro (need not to be very complicated) can update the whole thing - If the work is a 1-off then probably overkill, if part of a repeat process then work investigating

[jonesn3]

Thanks! This is actually closest to what I was looking for. I recorded a macro to capture the "Fill - Series" built-in function and then modified the macro code to automatically populate a column of constant interval down to a specific value. 

Previously I was unaware of the "Fill - Series" button in Excel and that works great. The macro just makes it convenient based on user input cell values for start, step, and start.