Std Dev problem when using extra condition

epaklt

New Member
Joined
Jul 10, 2019
Messages
2
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,281
Office Version
2019, 2016, 2013
Platform
Windows
have you tried writing your AM11 as R C format ?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,480
Messages
5,444,733
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top