Hi,
I need to calculate the standard deviation for 'Duration' data, based on 2 conditions in multiple columns that both need to be TRUE. I had some trouble but ended up using the DSTDEV command. I want to be able to avoid getting an error when there is no data that meets these conditions. I have a cell that olds the count total (AM11). and this formula seems to give me the SD if I type this directly into the spreadsheet using the appropriate cells.
=IF((AM11>1),(DSTDEV($A$1:$AI$500,"Duration",$AK$20:$AL$21)),"Nil")
Range("AL11").Select
ActiveCell.FormulaR1C1 = _
"=IF((AM11>1),(DSTDEV(R1C1:R500C35,""Duration"",R20C37:R21C38)),""Nil"")"
However, when I run this from my macro, it inserts single quotes around AM11 and won't calculate. Below is what is seen in the spreadsheet. I have tried different variations but can't work out how to stop it from inserting the extra single quotes.
=IF('AM11'>1,(DSTDEV($A$1:$AI$500,"Duration",$AQ$20:$AR$21)),"Nil")
Any help would be appreciated.
I need to calculate the standard deviation for 'Duration' data, based on 2 conditions in multiple columns that both need to be TRUE. I had some trouble but ended up using the DSTDEV command. I want to be able to avoid getting an error when there is no data that meets these conditions. I have a cell that olds the count total (AM11). and this formula seems to give me the SD if I type this directly into the spreadsheet using the appropriate cells.
=IF((AM11>1),(DSTDEV($A$1:$AI$500,"Duration",$AK$20:$AL$21)),"Nil")
Range("AL11").Select
ActiveCell.FormulaR1C1 = _
"=IF((AM11>1),(DSTDEV(R1C1:R500C35,""Duration"",R20C37:R21C38)),""Nil"")"
However, when I run this from my macro, it inserts single quotes around AM11 and won't calculate. Below is what is seen in the spreadsheet. I have tried different variations but can't work out how to stop it from inserting the extra single quotes.
=IF('AM11'>1,(DSTDEV($A$1:$AI$500,"Duration",$AQ$20:$AR$21)),"Nil")
Any help would be appreciated.
