Search code examples
rexcelxlsx

write.xlsx in R giving incorrect NA in cell

I have a zoo object which contains some NA values within it and I am writing it to an Excel file using the write.xlsx command from the xlsx package. However instead of giving NA in the Excel file where required it provides #N/A which Excel has trouble working with. Is this normal behaviour? If so, is there anyway around this?

Here is an example

y <- zoo(c(1:40), as.Date(1:40))
y[20] <- NA
write.xlsx(y, file = "test.xlsx")

Many thanks

Solution

  • This can be done either by using R or Excel

    Using the xlsx package, you can leave NA values as blank cells

     write.xlsx(y, file = "test.xlsx", showNA=FALSE)

    Using excel you can ignore the NA values. Remember to press ctrl +shift+enter

    {=SUM(IF(ISNA(A3:D3),0,A3:D3))}