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

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

Jul 26, 2021
08:24 PM

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

Jul 26, 2021
08:24 PM

Using WRITEEXCEL to export a function

Hello

I'm new to Mathcad and have just taken over a file from someone at work. It runs fine but I am stuck manually copy pasting the results into an excel sheet. I want to automate the process and have tried using the WRITEEXCEL function but I can't seem to get it to work. See attached photo.

I believe it has something to do with the function qp(z) not being a matrix or array but I'm not sure. I've also tried defining M:=qp(z) and using M in the WRITEEXCEL function but that gave me a "variable is not defined error".

Solved! Go to Solution.

Labels:

1 ACCEPTED SOLUTION

Accepted Solutions

Jul 27, 2021
04:12 PM

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

Jul 27, 2021
04:12 PM

It's not possible to divide a unit out of a range variable:

Note that range variables are (should be) used only for:

- creating a range for plotting a function

- indexing an array (vector or matrix).

- iterations (as part of a 'for' loop in a program, or using the range on worksheet level).

Nothing else.

We'll use the last option here.

Create a range variable i, and use that to (iteratively) create a vector result of your function:

Note that I (deliberately) created the vector as a literally subscripted variable Fp (I typed

F.p

to get that), then added an index to it by appending:

[ORIGIN

This to show you once more the difference between a literal subscript and an index...

Anyway, now you have a vector result, but it still has a unit, and you cannot export units to Excel, so you'll have to divide that out. With an array that is possible, and it can be done as:

Success!

Luc

6 REPLIES 6

Jul 26, 2021
09:15 PM

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

Jul 26, 2021
09:15 PM

As Excel does not understand units, you can only write dimensionsless data to an Excel file (the error message also tells you, that the data must be unitless). So use q.p(z)*ft^2/lb (instead of just q.p(z) ) as argument in the WRITEEXCEL function to get rid of the units. Furthermore I think that the filename must include the extension ".xlsx". Specifying "A1" as starting cell should not be necessary, you can do without.

BTW, its always better to include the worksheet rather than just a picture. I can only guess that z is a range variable!? If thats the case, the export will not work even when using the correct syntax and unitless data. This is because q.p(z) is NOT a vector if z is a range. That may also be the reason why M:=q.p(z) does not work OK. The argument of WRITEEXCEL must be a vector!

It would be necessary to see the sheet for further help, though.

Keep in mind that range variables should be used only for

1) __indexing__ the elements of a vector or matrix

2) creating a loop in a Mathcad program

3) the abscissa values in a 2D-plot

For other purposes you'll have to use vectors or you'll run into problems.

And yes, it can be quite confusing to distinguish between ranges and vectors, especially for the novice.

Jul 27, 2021
01:20 PM

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

Jul 27, 2021
01:20 PM

I've attached the Mathcad file and the excel sheets it reads from. As you said, I think the issue is that q.p(z) is a range variable, not a vector.

Is there any way to convert a range variable to a vector? I've tried defining a matrix: Q.z=(q.p(z)*ft^2/lb) but that doesn't seem to work. I tried to follow this:

Jul 27, 2021
01:49 PM

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

Jul 27, 2021
01:49 PM

There's a VERY simple test to see if a certain variable is a vector. Consider the variable F, and type:

F[ORIGIN=

If that results in an value, then F is a vector. In another case F may be a matrix, in which case

F[ORIGIN,ORIGIN=

results in a value. Otherwise you will have received an error message that F is not a vector.

Apart from that, it seems that in your sheet z has a unit (ft). That is not allowed for an index. The index (the pointer to an element of a vector or a matrix) must be integer (a whole number) AND unitless.

Try to see if using i as an index works.

Finally you should know that there is a huge difference between an indexed variable and a literal subscript.

You get the variable F indexed by typing [ and then the index (a number or a range variable) after it. E.g. F[1 for the element with index 1.

You get a literal subscript by typing F.1, that gives just a variable F1. (where the 1 is in the subscript position)

Success!

Luc

Jul 27, 2021
02:14 PM

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

Jul 27, 2021
02:14 PM

I apologize as I'm probably trying to jump several steps without really ever having done any training or tutuorials with this software.

I used your method and found that z and qp(z) are not vectors. That would probably mean z is an index and qp is a range variable correct?

z had units of ft so I tried to define a variable zunitless :=z/ft but got the below error message. I believe this is because I need to define zunitless as an index?

Jul 27, 2021
04:12 PM

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

Jul 27, 2021
04:12 PM

It's not possible to divide a unit out of a range variable:

Note that range variables are (should be) used only for:

- creating a range for plotting a function

- indexing an array (vector or matrix).

- iterations (as part of a 'for' loop in a program, or using the range on worksheet level).

Nothing else.

We'll use the last option here.

Create a range variable i, and use that to (iteratively) create a vector result of your function:

Note that I (deliberately) created the vector as a literally subscripted variable Fp (I typed

F.p

to get that), then added an index to it by appending:

[ORIGIN

This to show you once more the difference between a literal subscript and an index...

Anyway, now you have a vector result, but it still has a unit, and you cannot export units to Excel, so you'll have to divide that out. With an array that is possible, and it can be done as:

Success!

Luc

Jul 28, 2021
12:37 PM

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

Jul 28, 2021
12:37 PM

It worked! I had to tweak a few things.

Thanks a ton!