How to skip a field if there is a word “FALSE” in it box or 0?

[Dave Smith]

I’m trying to get the Standard Deviations but in order for me to do this I need to skip over months that have a “false” or “zero” in the cell. I’m using a VBA Script to get my Standard Deviation, but if there is a better way, please let me know that also.

The script I’m using takes 6 months into account. Then calculates the Standard Deviation.

I’m hoping someone can help me fix the code below to skip fields that have “false” or a “zero”

VBA Script I’m using:

 Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
   Dim objExcel As Object
   Set objExcel = CreateObject("Excel.Application")
   
     
   Let GetXLStDev = objExcel.StDev(No1, No2, No3, No4, No5, No6)
   
   objExcel.Quit
   Set objExcel = Nothing
End Function

Public Function Pause(PauseSeconds As Double)

Dim Start
Start = Timer
Do While Timer < Start + PauseSeconds
DoEvents
Loop

End Function

Thanks for taking the time to help me out
TCB

[Stewart Ross]

Hi. There is really no simple way to do what you ask. The six arguments you have specified to the Excel StDev function cannot themselves be skipped, even if you change the arguments from double to variant types and make them optional within the GetXLStDev function header.

With just six values the standard deviation function will have a wide error bound (which is to say that the Standard Error calculated on the mean will be very large); to reduce the SD to five or less values is to make the calculation almost meaningless. I would question whether there is any benefit at all in calculating the SD for six values.

Please be aware that for normally-distributed values 68% lie within +/- 1 SD of the mean, and 95% within +/- 2SD. I would guess that +/- 2 SD on your current calculations could take you well outside of the range you actually have, just because of the large standard error which such a small sample entails.

-Stewart

[Dave Smith]

Thank you Stewart for helping me understand more about the limitations of access. Look forward to your help me the future.

Thanks again
TCB

[ADezii]

Thanks Stew for the redirection.