CODEWITHSUNDEEP
vbaexcel
otus
otus

Reputation: 385

Remove "#DIV/0!" for average and standard deviation calculation

I calculate the average value and the standard deviation for values in a range of lines in the same column, but when there is no value entered in the columns for example, I get "#DIV/0!" in the cell where the average or stdev is supposed to appear, so how can I solve this problem so that I get the message "non calculated" for example instead of "#DIV/0!".

Here is how I enter the average formula in a cell : 

newWorkBook.Worksheets("Feuil1").Range(Cells(29, i + 2).Address()).Formula = "=AVERAGE(" + Cells(19, i + 2).Address() + ":" + Cells(28, i + 2).Address() + ")"

EDIT : I have tried this : 

newWorkBook.Worksheets("Feuil1").Range(Cells(29, i + 2).Address()).Formula = "=IFERROR(AVERAGE(" + Cells(19, i + 2).Address() + ":" + Cells(28, i + 2).Address() + ")" + "," + "non calculated" + ")"

And I get "#NOM?" instead of "non calculated" (PS : I'm using a french version of Excel, so in english it might be "#NAME?")

Upvotes: 0

Views: 1470

Answers (1)

Bathsheba
Bathsheba

Reputation: 234635

Include =IFERROR(AVERAGE(...), """" & "non calculated" & """") around your current formula.

To inject a quotation literal into your source code, you need to write """".

Upvotes: 1

Related Questions