| Author | Message | ||
     Gary Ritchie |
I have an XY data set. I want to perform some analysis on the data using only the even # wavelengths. Essentially I have an Excel template that was designed to only accept spectra every 2nm. The new data I have is every 1nm. So does anyone know how to filter or separate the even # rows from then odd # rows using a function or macro in excel? | ||
| hlmark |
Gary - there's a function in ECXEL that you can put into a formula in a cell, that allows you to CALCULATE the row (and/or column) number of the cell from which to obtain the data. Once you've got that, you can simply multiply the row index by two, to skip the cells. I don't recall the name of the function offhand, but if you can't find it let me know and I'll dig it out. Howard \o/ /_\ | ||
| starkedw |
Gary Before you use Howard's selection approach, I suggest averaging the adjacent data points to improve S/N. There is no reason to throw away data, even if it is redundant. Ed Stark | ||
| michel |
You could also record a macro to delete every second column, then put a "do" in front and a "loop while..." behind | ||
| Art Springsteen (Artspring) |
Michel, How does one write such a macro? I've been looking to do this for quite a while- I often need to 'chop' data from 1nm to some other interval. I used to know how to do it in Lotus, but have forgotten. Cheers! Art Springsteen | ||
| Ron Rubinovitz |
In response to the Excel question.... I have gotten a lot of mileage out of the "indirect" function of excel. I create a "series" column with numbers with the desired series (1,3,5,7, etc.) and "concatenate" the appropriate column letter (lets say "C") to that to create a column list of the "target" data cells (C1,C3,C5...). Then by using the "indirect" function of Excel with this "target data" column to display the original raw data, one can create a column of values of just the desired, regularly spaced values. On the other hand I am sure there are many other ways to do this. | ||
| Tony Davies (Td) |
This has become an interesting discussion! About ten years ago I used a cheap spreadsheet program which had a simple "record macro" function to do very useful jobs. The best example I can remember organised a cross-validation test which took 96 hours of processing time on a PS2 (running during the night)! Now days, I have very few successes when I try to record macros in Excel! I think that visual basic is very similar to BASIC that I was writing ?? years ago but I have never discovered a useful guide. Does the group think that it would be useful to have a library on the website to hold Excel macros for downloading? How many people could/would contribute to it? I use Excel but know I am not good at it. I had not heard of the INDIRECT function and I cannot remember successfully using the CALCULATE function; so I can imagine that a library would be useful. Comments? (On the ideas not my ignorance) Best wishes, Tony | ||
| Bruce H. Campbell (Campclan) |
Tony, and all others, A library of macros would be nice. The idea can be extended further to include algorithms that can be used in other situations also. One simple example would be a function that would take derivates for those who don't have NIR software to do that. The only drawback to including such functions is it should be usable in commonly used software, such as Excel(TM). Another software possibility would be for Matlab users to put functions in that are not part of the Matlab software, etc. Bruce | ||
| Tony Davies (Td) |
Macro Database As a result of this discussion we will be providing space on the NIR Publications website for a "macro/program" database for Excel and Matlab users. The up-loading of programs will have to be done by me (or Ian) but they will be available to all to down-load. Any offers of macros should be sent to [email protected]. Authors of macros/programs will be expected to provide an e-mail for contact and they may also supply a link to their website. I hope it will prove useful. Best wishes, Tony |