write.xlsx in R giving incorrect NA in cell

Question

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

#N/A is what excel uses as NA values. if you type "=ISNA(#N/A)" into a cell it will return TRUE. are there any other details you can provide to make your error reproducible? http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Jonno Bourne, Oct 19 '16 at 08:37
Apologies, then it appears the function was working right and I misunderstood excel. The problem is that once it is in Excel when I try to get the average of a row the #N/A causes errors. Previously, I had a CSV file that contained NA instead of #N/A and when I would take the average of the rows Excel would ignore the NA's in the calculation. In an XLSX file it appears that this is not the case and the result of calling the AVERAGE function is also #N/A if your data range contains any NA's — wrahman, Oct 19 '16 at 08:45
I now see the xlsx files will also ignore NA values in calculations like I had before, but not #N/A values. So I guess I am now trying to determine how I would get write.xlsx to input NA instead of #N/A. Will add a better example in the comments — wrahman, Oct 19 '16 at 08:54

score 2 · Accepted Answer · answered Oct 19 '16 at 09:20

2

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))}

answered Oct 19 '16 at 09:20

Jonno Bourne

1,931
1
22
45

Thanks, I went with the first solution in the end. – wrahman Oct 19 '16 at 09:22
.@JonnoBourne - Is it possible to have `NA` in place of blanks when using `showNA=FALSE`? – Chetan Arvind Patil Aug 15 '19 at 02:25
I am having some Java script problems atm so can't check, but you could replace the NA values with the character string "NA" then save it. – Jonno Bourne Aug 15 '19 at 09:28

score 1 · Answer 2 · edited Oct 29 '21 at 00:33

I have to import data out of Excel and into R and then back again frequently. Our standard for an empty cell in Excel is NA. In order to avoid getting the #N/A, I used a simple workaround so I would not have to change it in Excel (Ctrl+H and replace #N/A with NA on the workbook).

Try this in R before exporting to Excel:

y[is.na(y)] <- "NA"

Excel will not know the difference between an actual NA and a char string "NA".

write.xlsx in R giving incorrect NA in cell

2 Answers2

Linked