473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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:
Expand|Select|Wrap|Line Numbers
  1.  Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
  2.    Dim objExcel As Object
  3.    Set objExcel = CreateObject("Excel.Application")
  4.  
  5.  
  6.    Let GetXLStDev = objExcel.StDev(No1, No2, No3, No4, No5, No6)
  7.  
  8.    objExcel.Quit
  9.    Set objExcel = Nothing
  10. End Function
  11.  
  12. Public Function Pause(PauseSeconds As Double)
  13.  
  14. Dim Start
  15. Start = Timer
  16. Do While Timer < Start + PauseSeconds
  17. DoEvents
  18. Loop
  19.  
  20. End Function
Thanks for taking the time to help me out
TCB
Jan 26 '11 #1
3 1708
Stewart Ross
2,545 Expert Mod 2GB
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
Jan 29 '11 #2
Thank you Stewart for helping me understand more about the limitations of access. Look forward to your help me the future.

Thanks again
TCB
Feb 2 '11 #3
ADezii
8,834 Expert 8TB
Thanks Stew for the redirection.
Feb 3 '11 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Mike | last post by:
I've been trying for the past week to put a simple code together. I have done a LOT of searching, found scripts showing the functions I would like to use, however when I mix them it all goes wrong,...
1
by: JLuppens | last post by:
Does anyone know if there is a way to track changes in a text field like word does? I am using a SQL2000 database and can use either VB.Net or Access. The field is either ntext or Varchar. I...
3
by: kaplooeymom | last post by:
I'm using Lebans RTF control in Access 2003. I'm looking for a way to get accurate word counts for a field - not including the rich text markup. I'm trying to avoid pasting the field into word,...
4
by: steverossiter | last post by:
Hi and thanks, I have a Journalist database for tracking writers from various magazines and would like a field that could hold the text of their articles as they are published. The Memo field...
0
by: news.zen.co.uk | last post by:
Hi Guys, Am wanting to iterate through a set of Mailmerge Fields within a MS-Word document using VB .NET. Here's a fragment of code that (should) do this! Dim WordDoc As Word.Document =...
13
by: kurtj | last post by:
Hello Gurus: I have a validation script (below) that is somehow messed up. If the Name field is blank, I get the alert message, then the browser window goes to a blank document with the word...
2
by: Patrik Birgersson | last post by:
Hi there! I've been trying for days to find a solution anywhere on the web to this problem and I hope you might be able to sort me out. I want to use PHP and COM to set and read values of MS...
1
by: pdm | last post by:
hoi access, I have a subreport and with the id of inheritance of OOP I like to reuse this subreport in other main reports and hide some fields. First, is this possible and second what is the...
1
by: dittytwo | last post by:
Hi there The below code works if you don't try to do anything to the word document which is great (Not :D) I am having problems with the context command both text (i.e initial document with no...
1
by: Mientje | last post by:
I've made an Access 2007 database to store information about the lessonplans I have to make every schoolyear. I want to export the data form the table "Lesvoorbereiding" (Lessonplans in English) to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.