| View previous topic :: View next topic |
| Author |
Message |
lmw
Newbie
Joined: 01 Sep 2008
Posts: 4
Location: India
|
 Posted: Fri Sep 05, 2008 2:45 am Post subject: Programming task |
 |
|
Dear all,
I am new to forum & user to open office
Myself interested to write simple engineering automated tasks in spread sheet.
I have task which is below in brief.
I have to select input 2 values
Based on the Input combination, in the tabular column which is already in database -corresponding result has to come.THis is my task.
Hope you understand my task
How to write it?Is it possible?
Any suggestions,tips,ref.books,tutorials are welcome
Prakash |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Fri Sep 05, 2008 5:19 am Post subject: Re: Programming task |
|
|
| lmw wrote: | Dear all,
I am new to forum & user to open office
Myself interested to write simple engineering automated tasks in spread sheet.
I have task which is below in brief.
I have to select input 2 values
Based on the Input combination, in the tabular column which is already in database -corresponding result has to come.THis is my task.
Hope you understand my task
How to write it?Is it possible?
Any suggestions,tips,ref.books,tutorials are welcome
Prakash |
I don't think I understand. You say that you have two imput variables and a colunm in a database? Do you mean a real database or just a column of data in the spreadsheet.
When you input the two varialbes does this create a new entry in the column or does it change it?
Can you describe the data and the purpose behind the project. It can make it easier to suggest things.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
lmw
Newbie
Joined: 01 Sep 2008
Posts: 4
Location: India
|
| Posted: Fri Sep 05, 2008 8:13 pm Post subject: Clarification-brief |
|
|
Thanks for your repy
Let me brief clear
I have input varaibles that is selected from list values
Example:Assume List of string values a,b,c,d,e-One variable Another variable list of values f,g,h,i,j
In database a,b,c,d,e is in row wise and f,g,h,i,j is column wise-Based on your selection
say a,f-in data base a value 4 is stored.
If i am selected a,f as input variables, data value 4 has to come as result and this result will be used in subsequent calculations.
Hope you understand my task
Thanks
Prakash |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sat Sep 06, 2008 5:26 am Post subject: |
|
|
Not quite sure, but I think he's wanting to put in the row and column values and get the result from a table of values using those.
It is a question asked before, I think, on how to return a value within a given matrix from coordinates. It would be a useful tool, but I don't recall a direct solution?
David. |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Sat Sep 06, 2008 6:52 am Post subject: |
|
|
| David wrote: | Not quite sure, but I think he's wanting to put in the row and column values and get the result from a table of values using those.
It is a question asked before, I think, on how to return a value within a given matrix from coordinates. It would be a useful tool, but I don't recall a direct solution?
David. |
I was just looking for something similar (1 XN matrix) and I don't see anything either.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sat Sep 06, 2008 10:34 am Post subject: |
|
|
| jrkrideau wrote: | | I was just looking for something similar (1 XN matrix) and I don't see anything either. |
Shouldn't LOOKUP() handle a 1xN matrix [a single row or column vector]?
David |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Sep 06, 2008 12:37 pm Post subject: |
|
|
| Quote: | Example:Assume List of string values a,b,c,d,e-One variable Another variable list of values f,g,h,i,j
In database a,b,c,d,e is in row wise and f,g,h,i,j is column wise-Based on your selection
say a,f-in data base a value 4 is stored. |
Never organize things in cross-tables. It will never work with a database and in a spreadsheet it makes everything very complicated.
You can derive all sorts of results (including cross-tables) when you store your data like this:
| Code: |
Name Category Value
B h 0
G f 2
A d 2
G d 2
A a 3
E e 7
G h 8
C b 5
F d 1
D a 1
|
=SUMPRODUCT(name="G";category="h") counts how many.
=SUMPRODUCT(name="G";category="h";value) returns the sum of values.
You can apply DSUM, DCOUNT, D...
You can filter this list.
You can use menu:Data>Subtotals...
You can use menu:Data>DataPilot...
The file http://www.mediafire.com/?ft321idd1v2 a cross-table demonstrates how to convert a cross table ("Schedule") into a database list ("List") and creates a dynamic data pilot from that list("Pivot_1").
----------------------------------------
A cross table in A1:F6
| Code: |
a b c d e
A
B
C
D
E
|
... with values in the middle.
You can lookup single values with INDEX and MATCH:
| Code: | | =INDEX($B$2:$F$6;MATCH("A";$A$2:$A$6;0);MATCH("a";$B$1:$F$1;0)) |
should return the value from B2 where "A" intersects "a". But there is not much more you can do with cross-tables.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
kgas
General User
Joined: 20 Sep 2008
Posts: 19
Location: Qatar
|
| Posted: Fri Sep 26, 2008 6:57 am Post subject: Programing Task: Fetching value from a table |
|
|
This task can be be done using Database function (DGet). Let me put this as simple as possible, with an example of marks obtained by students in a class
Name Maths Social Chemistry Physics
Andy 95 56 75 85
Syndy 85 65 65 90
Prasant 92 85 70 95
(This table is from A1...E3) Set the criteria
Name Maths Social Chemistry Physics (This is in A8...E8 and leave A9..E9 empty )
Now in A11 Type Name and A12 Type Field and in A13 type Result
Now enter =$B$11 in cell A9
In the B13 enter =DGET($A1:$E5,$B$12,$A$8:$E$9).
Enter the required name in cell B11 and the field (Maths...) name in B13 you get the desired result in B13.
This is bit easier to pickup for the novice and experts should bear with me. Now if you put your variables in B11 and B13 thro' a user interface(dialog) you can get display the desired result. I used this method to get density correction factors. Hope this will be clear.
_________________
Knowledge is Power
OS:Arch Linux |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
