Numerical integration and MS Excel "data table" function problem
 

Hello,

I made an excel sheet to simulate Fresnel diffraction patterns. I used
a 2 way data table to generate the "infinitesimal" contributions from
the EM field. After summation of the contributions in the 2D tables,
a have one result cell (that holds |E|) and three input cells
(variables) for x-, y-, and z- coordinate for the observation point.
When I change the coordinates manually, the results seem OK.

The idea was to use another table (one way table) to plot |E| versus
one of the input cells. However, I cannot get the table working. I
tried "scenarios" (filling in each scenario by hand), but Excel stores
the individual worksheets, so I ran out of the EXCEL 2000 internal
memory limit (I think of about 60MB). I also tried the offset
function, in combination with a one-way table. This also didn't work.

It seems that once you use a data table that uses one (or two) of your
variables, you cannot use another table to manipulate a third variable
(parameter).

Does somebody know a solution for this (I do not prefer a VB one)?

Best regards,

Wim
PA3DJS
www.tetech.nl
The PM is valid when you don't forget to take out abc.

Numerical integration and MS Excel "data table" function problem
 

On Fri, 28 Nov 2008 13:28:42 -0800 (PST), Wimpie
wrote:

Does somebody know a solution for this (I do not prefer a VB one)?


Hi Wim,

You might try OpenOffice Calc (part of the entire OpenOffice suite). I
have no direct experience in the matters you are having difficulty
with, however.

73's
Richard Clark, KB7QHC

Numerical integration and MS Excel "data table" function problem
 

On Nov 28, 1:28 pm, Wimpie wrote:
Hello,

I made an excel sheet to simulate Fresnel diffraction patterns. I used
a 2 way data table to generate the "infinitesimal" contributions from
the EM field. After summation of the contributions in the 2D tables,
a have one result cell (that holds |E|) and three input cells
(variables) for x-, y-, and z- coordinate for the observation point.
When I change the coordinates manually, the results seem OK.


Does somebody know a solution for this (I do not prefer a VB one)?


Excel is a notoriously bad thing to use for statistics or numerical
analysis. Aside from the pain the rear nature once you get past
trivial manipulations, there are some serious calculation issues to
worry about (since Excel isn't rigorously tested for its
implementation, these also change as versions change)

http://www.burns-stat.com/pages/Tuto...addiction.html
covers a lot of the problems.

You're better off biting the bullet and using something like Matlab
(academic license $100) or Octave (free, but not quite as fancy as
Matlab, but mostly compatible) or similar products.

Numerical integration and MS Excel "data table" function problem
 

On 30 nov, 01:23, wrote:
On Nov 28, 1:28 pm, Wimpie wrote:

Hello,

I made an excel sheet to simulate Fresnel diffraction patterns. I used
a 2 way data table to generate the "infinitesimal" contributions from
the EM field. After summation of the contributions in the 2D tables,
a have one result cell (that holds |E|) and three input cells
(variables) for x-, y-, and z- coordinate for the observation point.
When I change the coordinates manually, the results seem OK.

Does somebody know a solution for this (I do not prefer a VB one)?

Excel is a notoriously bad thing to use for statistics or numerical
analysis. Aside from the pain the rear nature once you get past
trivial manipulations, there are some serious calculation issues to
worry about (since Excel isn't rigorously tested for its
implementation, these also change as versions change)

http://www.burns-stat.com/pages/Tuto...addiction.html
covers a lot of the problems.

You're better off biting the bullet and using something like Matlab
(academic license $100) or Octave (free, but not quite as fancy as
Matlab, but mostly compatible) or similar products.

Hello,

Thanks for replying Richard and Jim. I hoped somebody had a similar
problem and knew a work-around. I know Excel is not the perfect tool
to do this, but I did similar things with reasonable results. I hoped
using data tables would speed up the spreadsheet design (until now it
didn't).

I do not have OO.org installed on this machine. A brother in law has
it on his PC, so when I visit him I will try to load the worksheet
into OO calc (or set it up in OO calc).

When this (OO calc) doesn't give the desired result, I have to change
to something that supports programming. I am not a student so Matlab
might not be option. I looked at "octave", "python" and "R". These
require some study also. Other option is to use an old general purpose
SW development package (C or Pascal based).

I think I have to spend some time to decide what to use...

Best regards,

Wim
PA3DJS
www.tetech.nl
removing abc results in valid PM

Numerical integration and MS Excel "data table" function problem
 

On Sun, 30 Nov 2008 14:24:28 -0800 (PST), Wimpie
wrote:

I know Excel is not the perfect tool
to do this, but I did similar things with reasonable results.

Hi Wim,

After following Jim's link, and reading it to considerable depth, I
wouldn't trust Excel to do anything more than 3rd grade arithmetic.
Even then, there are too many examples to suggest I still could get
burned. Take heed of their cautions that "reasonable results" are not
always accurate results.

Could the financial meltdown be attributable to rounding errors in
spreadsheets?

Make the investment and go with R.

73's
Richard Clark, KB7QHC

Numerical integration and MS Excel "data table" function problem
 

Wimpie writes:

Hello,

Thanks for replying Richard and Jim. I hoped somebody had a similar
problem and knew a work-around. I know Excel is not the perfect tool
to do this, but I did similar things with reasonable results. I hoped
using data tables would speed up the spreadsheet design (until now it
didn't).

I do not have OO.org installed on this machine. A brother in law has
it on his PC, so when I visit him I will try to load the worksheet
into OO calc (or set it up in OO calc).

When this (OO calc) doesn't give the desired result, I have to change
to something that supports programming. I am not a student so Matlab
might not be option. I looked at "octave", "python" and "R". These
require some study also. Other option is to use an old general purpose
SW development package (C or Pascal based).

I think I have to spend some time to decide what to use...

You can write plug-ins for Gnumeric in Python. However, it's best to
run Gnumeric on Linux (or another Unix of your choice). There is
Windows version, but unfortunately, it is somewhat unstable.

Jon LA4RT

Numerical integration and MS Excel "data table" function problem
 

Hi Wim

I'll admit I am having difficulty visualizing what you are doing with
data tables and was about to suggest using a macro to generate the plot
values until I saw your "no VB" comment.

When you say "data table", is that a pivot table? I have sheets with
calcs based on pivot table outputs that are then used in further pivot
tables. Looks horribly messy but it works. (Noting that the accuracy
problems arent a problem for me due to what I am calculating for)

Cheers Bob W5/VK2YQA

Wimpie wrote:

Does somebody know a solution for this (I do not prefer a VB one)?

Numerical integration and MS Excel "data table" function problem
 

On 1 dic, 13:16, Bob Bob wrote:
Hi Wim

I'll admit I am having difficulty visualizing what you are doing with
data tables and was about to suggest using a macro to generate the plot
values until I saw your "no VB" comment.

When you say "data table", is that a pivot table? I have sheets with
calcs based on pivot table outputs that are then used in further pivot
tables. Looks horribly messy but it works. (Noting that the accuracy
problems arent a problem for me due to what I am calculating for)

Cheers Bob W5/VK2YQA

Wimpie wrote:
Does somebody know a solution for this (I do not prefer a VB one)?

Hello to Bob and the other contributors

I used the table function that enters row or column data into a
formula and plot the result under the row or right to the column. I
use the 2 variable version that results in a square with 400 cells. I
add them with the sum function.

The reason for mentioning "VB not preferred" is because of when I have
to divert to programming, I prefer to abandon Excel (for this
application) and use another environment.

Following this thread and advise from other people, I will abandon
Excel for this Fresnel calculation.

Regarding stability issues, some people also advised me to use the
Unix/Linux program instead of the windows port. So I still have to
do some investigation, as I do not have a machine with Linux on it.

Best regards and thanks for the info,

Wim
PA3DJS
www.tetech.nl
Remove abc and the mail address is OK.

Numerical integration and MS Excel "data table" function problem
 

On Mon, 1 Dec 2008 13:36:22 -0800 (PST), Wimpie
wrote:

Regarding stability issues, some people also advised me to use the
Unix/Linux program instead of the windows port. So I still have to
do some investigation, as I do not have a machine with Linux on it.

Hi Wim,

Unix/Linux is all fine and well, but if the package is OS sensitive,
then that package is suspect in its own right. The OS has (should
have) nothing to do with the libraries that perform the computations.

Your aversion to VB is well founded in shared libraries (I presume,
however, your aversion is for coding, but the visceral response is
true to form).

Going back 25 years ago when I was designing FFTs. I wholly abandoned
MS whose expensive compiler and math libraries (upwards to $600) were
abysmal in comparison to the $50 TurboPascal product.

73's
Richard Clark, KB7QHC

Numerical integration and MS Excel "data table" function problem
 

Richard Clark wrote:
... I wholly abandoned
MS whose expensive compiler and math libraries (upwards to $600) were
abysmal in comparison to the $50 TurboPascal product.

73's
Richard Clark, KB7QHC

Indeed, if someone prefers pascal (hey, any language is OK with me, even
Fortran--wink Roy)

Here is 5.5 ... I would suspect it will get you what you want:

http://dn.codegear.com/article/20803

Grab the zip file.

Regards,
JS