cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X

Using WRITEEXCEL to export a function

JB_9974851
4-Participant

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".

Writeexcel test.png

1 ACCEPTED SOLUTION

Accepted Solutions
LucMeekes
23-Emerald III
(To:JB_9974851)

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

LucMeekes_0-1627415852552.png

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:

LucMeekes_0-1627416952868.png

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:

 

LucMeekes_2-1627416702933.png

 

Success!
Luc

 

 

View solution in original post

6 REPLIES 6

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.

 

JB_9974851
4-Participant
(To:Werner_E)

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:

 

https://community.ptc.com/t5/PTC-Mathcad/Converting-range-variable-into-a-vector-ultimately-to-find-root/td-p/55610

LucMeekes
23-Emerald III
(To:JB_9974851)

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

JB_9974851
4-Participant
(To:LucMeekes)

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?

 

JB_9974851_2-1627409425944.png

 

 

LucMeekes
23-Emerald III
(To:JB_9974851)

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

LucMeekes_0-1627415852552.png

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:

LucMeekes_0-1627416952868.png

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:

 

LucMeekes_2-1627416702933.png

 

Success!
Luc

 

 

JB_9974851
4-Participant
(To:LucMeekes)

It worked! I had to tweak a few things.

 

Thanks a ton!

Writeexcel Success.png

Top Tags