GNOME.org
 

Re: Solver Question

Jim,

I think I was able to reproduce the problem: Solver wants constraints. Even tried adding a dummy constrained variable. Obviously the solver engine doesn't like that.
Using Solver for this seems an overkill anyway, why don't you try regression functions: =SLOPE(range of y's, range of x's) and =INTERCEPT(range of y's, range of x's)

Good luck,

Mario

On Mon, Mar 23, 2009 at 7:00 AM, <gnumeric-list-request gnome org> wrote:
Send gnumeric-list mailing list submissions to
       gnumeric-list gnome org

To subscribe or unsubscribe via the World Wide Web, visit
       http://mail.gnome.org/mailman/listinfo/gnumeric-list
or, via email, send a message with subject or body 'help' to
       gnumeric-list-request gnome org

You can reach the person managing the list at
       gnumeric-list-owner gnome org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of gnumeric-list digest..."


Today's Topics:

  1. Re: Range operators (Rita & Klaus Kuhnlein)
  2. Solver question (Jim Martin)
  3. Solver question (Jim Martin)
  4. Gnumeric 1.9.5 is released (Morten Welinder)


----------------------------------------------------------------------

Message: 1
Date: Sun, 22 Mar 2009 10:54:50 -0600
From: Rita & Klaus Kuhnlein <rikla wynndel ca>
Subject: Re: Range operators
To: Andreas J. Guelzow <aguelzow pyrshep ca>
Cc: Mailing list <gnumeric-list gnome org>
Message-ID: <90B229E8-89D2-4C92-AA41-F57D43E4FC42 wynndel ca>
Content-Type: text/plain; charset=WINDOWS-1252; format=flowed;
       delsp=yes

Hi Andreas,

sorry, I do not want to extend the discussion endlessly. But when the
specification says that a union is just the sum of the two ranges then
the specification is not good. Why is there an intersection operator
(Blank) which follows exactly the rules of set theory. It works in
Gnumeric and Excel. Consequently there should be the counterpart union
which has in A1:B3 and B2:C3 seven and not eight cells.

Have a nice day.
Klaus

On 21-Mar-09, at 11:16 PM, Andreas J. Guelzow wrote:

> On Sat, 2009-03-21 at 21:58 -0600, Rita & Klaus Kuhnlein wrote:
>> Hi Andreas,
>>
>> that means: there is no range set union operator (comma) at all.
>> Neither in Excel nor in Gnumeric. I understand!?
>
> Technically there is an operator that is called the union operator, in
> Excel and Gnumeric it is a comma, in OpenOffice3 it is denoted by ~.
> According to the OpenFormula draft it is also the concatenation
> operator:
>
> ------------------------------------------------------------------------
> Infix Operator Reference Concatenation ("~") (aka Union)
> Summary: Concatenate two references
>
> Syntax: Reference Left ~ Reference Right
>
> Returns: ReferenceList
>
> Constraints: None
>
> Semantics: Takes two references and computes the "cell union", which
> is
> simply a concatenation of the reference Left followed by the reference
> Right. This is not the same as a union in set theory; duplicate
> references to cells are not removed. The resulting reference will have
> the number of areas, as reported by AREAS, as AREAS(Left)
> +AREAS(Right).
> Note that this is notated as "~" in OpenFormula format, but as a comma
> or ?+? in some user interfaces.
> ------------------------------------------------------------------------
>
> The draft also contains the annotation:
> R
> ------------------------------------------------------------------------
> ationale: OpenOffice.org 1.1.3 and 2.0.2 do not include this operator.
> However, other applications, such as Microsoft Excel and Gnumeric,
> include this operation. In Excel this is represented using the comma
> (",") character, the same symbol used as the parameter separator for
> function calls. This is a very poor choice with a number of
> unfortunate
> ramifications. One problem is that concatetating cells in a function
> parameter requires surrounding the cells with additional parentheses
> in
> Excel display syntax. For example, AREAS(A1:A3,B2:B4) is a function
> call
> with two parameters, while AREAS((A1:A3,B2:B4)) is a function call
> with
> one parameter. Another problem is that the comma interferes with the
> use
> of "," as a decimal separator (as it is used in many locales) when
> using
> traditional entry formats (which do not mark cell addresses with
> "..").
> Gnumeric uses "+" as the cell concatenation operator in its display,
> but
> this has its own problems: it interferes with the use of "+" as a
> matrix
> addition operator. There are many alternatives, e.g., other characters
> (such as "~", "|", and "\"), or requiring a function syntax for this
> purpose. The character "_" would be a poor choice because formula
> variables can also include this character in their name (complicating
> parsing when "[..]" are not used; is B3_B2 a formula variable, or
> are B2
> and B3 concatenated?). Because of these issues, this specification
> uses
> "~" as the cell concatenation symbol.
> ------------------------------------------------------------------------
>
> (I am not sure where the claim that Gnumeric uses + in its display
> comes
> from, perhaps an old version?)
>
> Andreas
>>
> --
> Andreas J. Guelzow <aguelzow pyrshep ca>
>
>



------------------------------

Message: 2
Date: Sun, 22 Mar 2009 16:15:07 -0600
From: Jim Martin <jim martin utah edu>
Subject: Solver question
To: "gnumeric-list gnome org" <gnumeric-list gnome org>
Message-ID:
       <45B3B5A7B6E5564DA230245082FF1722042730F307 C3V2 xds umail utah edu>
Content-Type: text/plain; charset="us-ascii"

Hello All:

I have a question about using Solver in Gnumeric (and it seems the same question may apply to OpenOffice Calc).

Background: I use excel a lot. Probably should have bitten the bullet years ago and learned matlab for the kinds of things I do (biomechanics research) but so far excel has not let me down in terms of being able to get things done. It is probably slower than matlab but I like being able to work in a spreadsheet format rather than command line.  In the spreadsheets that I use, I often use solver to determine regression coefficients and other terms by minimizing the sum of squared error term between the raw data and the modeled value. That is, I write a model equation with coefficients located in a few cells. I then calculate the error term (raw - model values) for each data point. Squaring and summing those terms gives me a sum of squared error term. In excel, I can use solver to determine the coefficients by minimizing the sum or squared error term. This ability is very important to many of my applications and I can't do without it.

I was surprised that I could not seem to accomplish this simple task in OpenOffice or in Gnumeric.  In reading through the online help it seems that I need to add constraints to the model before solver can handle it (although I am not sure this is true). The trouble is that I do not have constraints in my models (can can't imagine how I would add them and yes I did take linear algebra years ago). The solver box in excel also has a box for constraints similar to that in Gnumeric but my technique works fine with no constraints. Besides using the excel spreadsheets I have built in excel, I have also tried this in Gnumeric using a very simple linear equation and cannot get a solution. Can someone either tell me the trick to get this to work in Gnumeric and OpenOffice or explain to my why it can't be done?

The reason for my recently renewed interest in Gnumeric and OpenOffice is that I just wrote a tutorial for the online journal "Sports Science" in which used excel to determine Fourrier coefficients. A downloadable spreadsheet is part of the publication.  I would have liked to have been able to upload a free software version of the spreadsheet but this step kept me from doing so.

Thanks in advance for any help you can give me on this topic.

Sincerely,

Jim

------------------------------

Message: 3
Date: Sun, 22 Mar 2009 16:15:34 -0600
From: Jim Martin <jim martin utah edu>
Subject: Solver question
To: "gnumeric-list gnome org" <gnumeric-list gnome org>
Message-ID:
       <45B3B5A7B6E5564DA230245082FF1722042730F308 C3V2 xds umail utah edu>
Content-Type: text/plain; charset="us-ascii"

Hello All:

I have a question about using Solver in Gnumeric (and it seems the same question may apply to OpenOffice Calc).

Background: I use excel a lot. Probably should have bitten the bullet years ago and learned matlab for the kinds of things I do (biomechanics research) but so far excel has not let me down in terms of being able to get things done. It is probably slower than matlab but I like being able to work in a spreadsheet format rather than command line.  In the spreadsheets that I use, I often use solver to determine regression coefficients and other terms by minimizing the sum of squared error term between the raw data and the modeled value. That is, I write a model equation with coefficients located in a few cells. I then calculate the error term (raw - model values) for each data point. Squaring and summing those terms gives me a sum of squared error term. In excel, I can use solver to determine the coefficients by minimizing the sum or squared error term. This ability is very important to many of my applications and I can't do without it.

I was surprised that I could not seem to accomplish this simple task in OpenOffice or in Gnumeric.  In reading through the online help it seems that I need to add constraints to the model before solver can handle it (although I am not sure this is true). The trouble is that I do not have constraints in my models (can can't imagine how I would add them and yes I did take linear algebra years ago). The solver box in excel also has a box for constraints similar to that in Gnumeric but my technique works fine with no constraints. Besides using the excel spreadsheets I have built in excel, I have also tried this in Gnumeric using a very simple linear equation and cannot get a solution. Can someone either tell me the trick to get this to work in Gnumeric and OpenOffice or explain to my why it can't be done?

The reason for my recently renewed interest in Gnumeric and OpenOffice is that I just wrote a tutorial for the online journal "Sports Science" in which used excel to determine Fourrier coefficients. A downloadable spreadsheet is part of the publication.  I would have liked to have been able to upload a free software version of the spreadsheet but this step kept me from doing so.

Thanks in advance for any help you can give me on this topic.

Sincerely,

Jim

------------------------------

Message: 4
Date: Sun, 22 Mar 2009 19:08:47 -0400
From: Morten Welinder <mortenw gnome org>
Subject: Gnumeric 1.9.5 is released
To: Gnumeric Mailing List <gnumeric-list gnome org>,    Gnome
       Announcement List <gnome-announce-list gnome org>
Message-ID:
       <118833cc0903221608v450f1207vae63d079c83a636b mail gmail com>
Content-Type: text/plain; charset=ISO-8859-1

Free, Fast, Accurate -- Pick Any Three!

The Gnumeric Team is pleased to announce the availability of Gnumeric
version 1.9.5. This release requires the concurrently released goffice
0.7.4. We also recommend libgsf 1.4.11.

This release primarily works around an ABI change in GTK+ 2.16. It
also comes with a collection of bug fixes, see below.

Attention packagers: goffice no longer depends on libgnome,
libgnomeui, and gnome-vfs. (To get the full benefit of this, make sure
libgsf is not compiled to require gnome-vfs and bonobo.)

Attention packagers: please do not ship goffice with equation enabled.

   *  Andreas
         o Fix style critical on latex export [Bug 574125].
         o Always print the whole printarea even if it has no content
or style. [Bug 554116] [Bug 572818].
         o Fix translation of argument names for empty names.
         o Don't switch sheets when widgets are adjusted. [Bug 574734].
         o Fix printing of rotated text (negative angles or
non-default alignment). [Bug 574813].
         o Remove crash potential for various dialogs with multiple
views. [Bug 364291].
         o Fix some strings. [Bug 575360].
   * Jean
         o Make gconf use independent from the with-gnome option. [Bug 574813].
         o Fixed null pointer crash in oo_plot_area(). [Bug 575403].
   * Jody
         o Work around semantic changes in gtk-2.16.
         o rename sr Latn -> sr latin
   * Morten
         o Fix multihead issues with cell comments.
         o Fix multihead issue with sheet reordering.
         o Fix multihead issues with tooltips.
         o Fix PFACTOR to handle huge factors.
         o Fix problem with dialogs not showing up in fullscreen
mode. [Bug 574602].
         o Fix desktop file to always specify that we take URIs.
         o Fix sheet-ordering by dragging. [Bug 574763].
         o Fix potential crash for cell comment dialog with multiple
views. [Part of Bug 364291].
         o Fix lotus importer crash. [Bug 575190].
         o Improve entry of date for locales not using slashes. [Part
of Bug 33229].
         o Make date edit use the date separator of the locale. [Part
of Bug 33229].
         o Fix xls writing crash. [Part of Bug 575318].
         o Fix criticals in xls export for comments without author.
         o Fix crash while loading broken xls. [Bug 575393].
         o Fix string problem with broken xls. [Bug 575452].
         o Improve date entry for locales that end abbreviated month
names with punctuation.
         o Fix ODS crashes. [Bug 575600] [Bug 575843] [Bug 575981].
         o Use goffice magic formats for certain date and time
formats so we can persist them in .gnumeric.
         o Fix setting of radio button objects' text.
   * Sum1
         o Implement OOO probing. [Bug 574381].

...and in goffice...

   *  Jean
         o Add go_gtk_show_url. [Bug 559021].
         o Don't display markers in histogram legend. [Bug 574340].
         o Skip points corresponding to invalid valies in pie charts.
[Bug 574348].
         o Correctly use all points defined in bar/columns plot
series. [Bug 574349].
         o Avoid unused direct shlib deps. [Bug 572910].
         o Set a default family when none is given. [Bug 575318].
   * Morten
         o Fix a pixbuf scaling problem in foocanvas.
         o Improve handling of localized formats.


------------------------------

_______________________________________________
gnumeric-list mailing list
gnumeric-list gnome org
http://mail.gnome.org/mailman/listinfo/gnumeric-list


End of gnumeric-list Digest, Vol 59, Issue 18
*********************************************

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]