Handling non-numbers when reading data from Excel

Hi all,

I would like to compute a simple ratio from two datasets:

(pb - pa) / 2

I have some missing values in each series that are non-values. I would
like to not compute this ratio when pb or pa have "no value"

The series are loaded from xlsx, and I use the following command:

vls = reshape(error(0),9,1);
vls[4] =9999.99; /* To convert all occurrences of #DIV/0! to 9999.99, and
all other empty cells and special types to GAUSS missing values */ 
pa = xlsreadm("C:\\Gauss8.0\\myfiles\\german_pa.xlsx","b1",1,vls); 
pb = xlsreadm("C:\\Gauss8.0\\myfiles\\german_pb.xlsx","b1",1,vls);

But applying this synthax is not good, it compute the ratio because the
9999.99 is obviously a value... I do not find the good synthax

Somebody can help me?

Best,
Olivier

1 Answer

0

I believe that if you do not explicitly set the <tt>vls</tt> parameter, all instances of  DIV/0, Nan, missing values etc will be converted to a GAUSS missing value. Pass in an empty string for vls

vls = "";
pa = xlsreadm("C:\\Gauss8.0\\myfiles\\german_pa.xlsx","b1",1,vls); 
pb = xlsreadm("C:\\Gauss8.0\\myfiles\\german_pb.xlsx","b1",1,vls);

Then the elements with missing values will not compute the ratio, for example if after loading from your Excel sheet:

pa = 10
      5
      .
      8
      3

pb = 2
     3
     9
     .
     1

Then (pa - pb)/2 =:

4
1
.
.
1

If you want to remove the rows with missing values, you can use the packr function.

link

caj1024

110