| Author |
 Topic  |
|
|
IndiJones
Japan
11 Posts |
 Posted - 04/23/2004 : 03:19:30 AM
|
I have three columns of data A(1..10), B(1..10) & C(1..10) and a (3 X 3) matrix in column D(1..3), E(1..3) & F(1..3). I want to set the following values for each cell of columns G, H & I:
Value of G1 is
{if D1*A1+E1*B1+F1*C1 is less than 0, it will give 0, else the calculated value}
Value of H1 is
{if D2*A1+E2*B1+F*C1 is less than 0, it will give 0, else the calculated value}
Value of I1 is
{if D3*A1+E3*B1+F3*C1 is less than 0, it will give 0, else the calculated value}
Value of G2 is
{if D1*A2+E1*B2+F1*C2 is less than 0, it will give 0, else the calculated value}
Value of H2 is
{if D2*A2+E2*B2+F2*C2 is less than 0, it will give 0, else the calculated value}
......and so on.
In Excel, this could easily be done for cell G1 with the following equation:
=IF($D$1*A1+$E$1*B1+$F$1*C1<0,0,$D$1*A1+$E$1*B1+$F$1)
How can I do this in Origin?
Edited by - IndiJones on 04/23/2004 03:37:09 AM |
|
|
greg
USA
1378 Posts |
Posted - 04/23/2004 : 4:54:23 PM
|
Users who come to Origin from a spreadsheet software often make the mistake of thinking our worksheets behave just like spreadsheets. They do not and they probably never will. In particular, you can not put formulas in a cell that update cell values according to other cell values. You can do that on a column by column basis (using Column : Set Column Values), and the setting can put results only in a defined range, but that wouldn't help you here.
Of course, LabTalk and OriginC programmers know how to programmatically access worksheet cells and could write a program to do what you seek, but that may be beyond your current skill level with Origin.
I recognized from your formula that what you are really doing is matrix multiplication where the second matrix is transposed. Since Excel isn't designed for scientific calculations, there probably isn't a matrix multiplication function and you had to resort to a complicated expression using relative notation and carefully extend that expression over the full range of results. Changing the number of rows and columns would involve more complicated expression.
Origin does offer many 'scientific' math functions including matrix multiplication. Here's how I would do what you seek:
Highlight the range A1 to C10 in your Origin worksheet.
Select Edit : Convert to Matrix : Direct from the menu.
Click the Convert button in the "Direct Conversion to Matrix" dialog.
(This creates the 'first' matrix - should be Matrix1.)
Highlight the range D1 to F3 in your Origin worksheet.
Click the 'Update Selected Range" button in the dialog.
(It's the one with two circular, blue arrows.)
Click the Convert button in the "Direct Conversion to Matrix" dialog.
(This creates the 'second' matrix - should be Matrix2.)
Close the dialog.
Make sure the 'second' matrix is the active window and select Matrix : Transpose from the menu.
Create a third matrix by clicking the 'New Matrix' button on the Standard toolbar. (It' the fifth button.)
Now we need to use some LabTalk.
Open the Script Window. (It's under the Window menu.)
With the third matrix active, we can use this script:
mat.matname$=%H;
mat.multiply(matrix1,matrix2);
(%H)=%H<0?0:%H;
Copy and paste these three lines to the Script Window, then select all three and press enter. The new matrix should have the values you are expecting.
The second and third lines of the script (after transposing the second matrix) are Origin's mathematical equivalent of your expression.
|
 |
|
|
IndiJones
Japan
11 Posts |
Posted - 04/24/2004 : 06:24:43 AM
|
Thanks for the advice! It WORKED NOW!
Edited by - IndiJones on 04/24/2004 07:11:58 AM |
|
|
| |
Topic |
|
|
|
Need help on Worksheet!