[Solved] Decimate and harmonizate data range interval

[M6mmi]

Hi

Let's say i have in table 1 data with interval 0.28, values going from 190 to 2500.
In another table i have data with interval 0.1, values going from 400 to 1000.

How can i harmonizate on of the table to look like another table in common range?
How can i display table 1 values with 0.1 interval in the range of 400 to 1000?

[jrkrideau]

Hi

Let's say i have in table 1 data with interval 0.28, values going from 190 to 2500.
In another table i have data with interval 0.1, values going from 400 to 1000.

How can i harmonizate on of the table to look like another table in common range?
How can i display table 1 values with 0.1 interval in the range of 400 to 1000?

I don't understand what you want here. As far as I can see there are no values in the first table that are equal to values in the second table so how are you going to "harmonizate" . By the way harmonizate is not a word in English so maybe it is just a translation problem but what you want is not clear.

Also decimate, strictly speaking, means to kill 1/10th of the members of the group. Did you really mean that you wanted to remove some values from one of the tables?

[M6mmi]

It's hard to explain without example.
Here's the example http://www.upload.ee/files/3188818/Example.ods.html

[Villeroy]

First of all you should convert your numeric text into numbers (or import numbers in the first place).
Then pivot table (aka data pilot) can generate the table you want.
http://wiki.services.openoffice.org/wik ... /DataPilot

[M6mmi]

I only have the data wavelength with start and end point.

How can i generate missing numbers (wavelengths) when i know:
1) Start point
2) End point
3) Quantity of values / intervals / quantity of empty fields.

[acknak]

Here is my re-organization of the data in the sample file, with the values converted and the wavelengths filled in.

But that's just a start and I'm not at all sure what is the correct way to proceed. As I understand what you want to do is calculate (or simulate) the spectral properties of a mixture of two substances, given the spectra of the pure substances.

As I see it there are two problems:

First, you need to interpolate to estimate the y-values from the measured spectra for the arbitrary x-values in the simulated set. This could be as simple as taking a y-value from the nearest x-value, as the y-values don't change a lot over the desired simulation range. Next better method would be to assume the spectrum is linear between the two closest points and interpolate from that.

Second, I think you need some sort of model for how the spectra behave when you change from measuring the pure substances to what you calculate for the mixture. I won't try to guess what that might be, except to say that it may not be correct to simply average the two interpolated y-values you get from the first step.

[M6mmi]

Yes the average of mixture is not physically correct.
I did compare different values at same wavelength for the Al http://refractiveindex.info/?group=META ... =Aluminium
and Cu http://refractiveindex.info/?group=META ... ial=Copper
and finally AlCu http://refractiveindex.info/?group=METALS&material=AlCu
For the Al, Cu and AlCu, i can't see direct connection between n and k.

It would be at least interesting to see how such "dirty job" of mine visually looks.
Like with paint, some red and white = lighter red => some Cu and Al, will it be lighter reddish?

Thank you acknak, i will study the file.

[M6mmi]

Seems like "INTERPOLATE" is missing from function list (all)

"GROWTH" and "TREND" will show me incorrect results.

=TREND(B3:B103;A3:A103;I3:I20)

Code: Select all

λ	refractive index
720	1,6752179447
700	1,6359400049
680	1,5966620651
660	1,5573841253
640	1,5181061855
620	1,4788282456
600	1,4395503058
580	1,400272366
560	1,3609944262
540	1,3217164864
520	1,2824385466
500	1,2431606068
480	1,203882667
460	1,1646047272
440	1,1253267874
420	1,0860488476
400	1,0467709077
380	1,0074929679

=GROWTH(B3:B103;A3:A103;I3:I20)

Code: Select all

λ	refractive index
720	1,5605407349
700	1,5284058537
680	1,4969326986
660	1,4661076432
640	1,4359173419
620	1,4063487237
600	1,3773889867
580	1,3490255928
560	1,3212462621
540	1,2940389674
520	1,2673919292
500	1,2412936107
480	1,2157327125
460	1,1906981681
440	1,1661791387
420	1,1421650087
400	1,1186453811
380	1,0956100731

Original data points

Code: Select all

723,5	1,3
713,17	1,27
702,84	1,24
692,51	1,205
682,18	1,17
671,85	1,13875
661,52	1,11
651,19	1,0825
640,86	1,06
630,53	1,046875
620,2	1,04
609,87	1,038125
599,54	1,04
589,21	1,041875
578,88	1,05
568,55	1,07375
558,22	1,1
547,89	1,116875
537,56	1,13
527,23	1,140625
516,9	1,15
506,57	1,160625
496,24	1,17
485,91	1,174375
475,58	1,18
465,25	1,195
454,92	1,21
444,59	1,215625
434,26	1,22
423,93	1,23
413,6	1,24
403,27	1,245625
392,94	1,25
382,61	1,25
372,28	1,26

[Villeroy]

TiNi_spectra2.ods

(34.15 KiB) Downloaded 155 times

Another partial solution with Lambdas grouped by a pivot table.
This is the right topic to point to recommend a dedicated statistics package: http://www.r-project.org/

[M6mmi]

Thank you Villeroy.
Grouping is good idea, i have now average of Ti and Ni side by side and i can work on from there.

Why didn't i consider it sooner, taking the center wavelength of group

[Villeroy]

Thank you Villeroy.
Grouping is good idea, i have now average of Ti and Ni side by side and i can work on from there.

Why didn't i consider it sooner, taking the center wavelength of group

Starting a pivot table is neither self-evident nor intuitive. This feature behaves like a database plug-in generating a table full of output with no spreadsheet formula.

When you are going to draw charts from a pivot table, be aware of the following issues:
-- Charts can not be bound to pivot field data. Charts are always bound to the flat sheet cells and the references do not adjust to a growing or shrinking pivot table. When you restructure or expand a pivot table you will have to adjust chart source ranges to the new dimensions (or rebuild them from scratch). Side note: Excel has pivot charts bound to cached pivot field data which allows to plot values from pivot field "X" rather than the current location of "X" at D5:D14.
-- The grouped Lambda column (191-211) contains text. Use a line chart instead of x/y-scatter. The latter requires a numeric x-scale.
-- Each data series can be adjusted to draw a continuous line between single data points.

[jrkrideau]

TiNi_spectra2.ods

Another partial solution with Lambdas grouped by a pivot table.
This is the right topic to point to recommend a dedicated statistics package: http://www.r-project.org/

Very neat grouping but even after reading your explanation I don't see how to get it to work (easy enough in R though. Can you explain it a bit more.

BTW I agree that M6mmi would be better off with something like http://www.r-project.org especially if he wants to do something like interpolate . A spreadsheet is really not the right tool.

[M6mmi]

Small note, to get more or less accurate average result from group, the data has to be rather high resolution.

My input was very low resolution,
therefor i did:

x1
((x1+x2)/2)
x2

In that fashion many many levels.

Anyways i mixed 70% Copper and 30% of zinc, and the result looks like light-yellowish brass