This is my R function:
lm_sl_na<-function(x,y,w){
x <- unlist (x);
y <- unlist (y);
w<- c(w)
lm(y~x,,,w,na.omit)$coefficients[2]
}
This is an Excel formula that calls it:
=R.lm_sl_na(A61:A90,B61:B90,C61:C90)
If the ranges referenced in the arguments has a "true blank" (a cell in which you have done "clear all" for instance) the R function returns a correct value excluding that row of input data.
If one of the cells in Excel is #N/A the result that gets back to Excel is #VALUE!
if one of the Excel cells has a formula that returns "", the R result is also #VALUE!
[for example =IF(COUNT(All_dat!B108)=1,All_dat!B108,"")]
Is there a way to make lm work in the circumstance that the input data from Excel contains a #N/A or a "" value? I want lm() to work the same as if the cell is "true blank", simply to exclude that row from the lm calculations.
Thank you!
This is my R function:
lm_sl_na<-function(x,y,w){
x <- unlist (x);
y <- unlist (y);
w<- c(w)
lm(y~x,,,w,na.omit)$coefficients[2]
}
This is an Excel formula that calls it:
=R.lm_sl_na(A61:A90,B61:B90,C61:C90)
If the ranges referenced in the arguments has a "true blank" (a cell in which you have done "clear all" for instance) the R function returns a correct value excluding that row of input data.
If one of the cells in Excel is #N/A the result that gets back to Excel is #VALUE!
if one of the Excel cells has a formula that returns "", the R result is also #VALUE!
[for example =IF(COUNT(All_dat!B108)=1,All_dat!B108,"")]
Is there a way to make lm work in the circumstance that the input data from Excel contains a #N/A or a "" value? I want lm() to work the same as if the cell is "true blank", simply to exclude that row from the lm calculations.
Thank you!