Turn on suggestions

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

Showing results for

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

Showing results for

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

12-05-2019
03:18 AM

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

12-05-2019
03:18 AM

Range Variable in Excel

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.

Labels:

1 ACCEPTED SOLUTION

Accepted Solutions

12-06-2019
03:44 AM

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

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

12-05-2019
05:21 AM

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

12-05-2019
05:21 AM

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

12-05-2019
08:53 AM

- 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
09:03 AM

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

12-05-2019
09:03 AM

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

12-05-2019
01:48 PM

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

12-05-2019
01:48 PM

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

12-06-2019
03:44 AM

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

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

12-06-2019
09:41 AM

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

12-06-2019
09:41 AM

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!

12-06-2019
06:00 PM

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

12-06-2019
06:00 PM

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.

12-11-2019
02:34 AM

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

12-11-2019
02:34 AM

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

12-10-2019
01:52 PM

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

12-10-2019
01:52 PM

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.