Get Help

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- PTC Mathcad
- :
- PTC Mathcad
- :
- Range Variable in Excel

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-05-2019
03:18 AM

12-05-2019
03:18 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-06-2019
03:44 AM

12-06-2019
03:44 AM

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
```

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-05-2019
05:21 AM

12-05-2019
05:21 AM

Re: Range Variable in Excel

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-05-2019
08:53 AM

12-05-2019
08:53 AM

Re: Range Variable in Excel

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-05-2019
09:03 AM

12-05-2019
09:03 AM

Re: Range Variable in Excel

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-05-2019
01:48 PM

12-05-2019
01:48 PM

Re: Range Variable in Excel

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-06-2019
03:44 AM

12-06-2019
03:44 AM

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
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-06-2019
09:41 AM

12-06-2019
09:41 AM

Re: Range Variable in Excel

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-06-2019
06:00 PM

12-06-2019
06:00 PM

Re: Range Variable in Excel

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-11-2019
02:34 AM

12-11-2019
02:34 AM

Re: Range Variable in Excel

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-10-2019
01:52 PM

12-10-2019
01:52 PM

Re: Range Variable in Excel

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.