473,493 Members | 4,147 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

Conditional STDEV formula

I have an Access app that uses a crystal report and exports to excel. I was
using this function to get the standard and average deviations for a specific
range once the info was in Excel:

Sub CalcDeviations(objXLApp)
Dim m As Long
Dim x As Double
Dim y As Double
Dim myRange As Range
Set myRange = Range("L5", Range("L5").End(xlDown))
x = objXLApp.WorksheetFunction.AveDev(myRange)
y = objXLApp.WorksheetFunction.StDev(myRange)

For m = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & m).Value = "Average Deviation" Then
Range("L" & m).FormulaR1C1 = x & "%"
Range("L" & m).NumberFormat = "##0.00%_)"
EseIf Range("A" & m).Value = "Standard Deviation" Then
Range("L" & m).FormulaR1C1 = y & "%"
Range("L" & m).NumberFormat = "##0.00%_)"
End If
Range("L" & m).Select
With Selection.Font
.Bold = True
.Name = "Times New Roman"
.Size = 8
End With
Next

End Sub

That worked perfect for me. The problem is that the user changed the
information requested. There are now subtotals in the columns along with the
data to calculate the deviations on. my question is how do I exclude those
subtotals? My condition would be:

If column A contains "RD" or "SFC", do not count those rows when
calculating the deviations. This is my first post, so I apologize if I left
anything out. Thanks in advance for any help.
May 23 '07 #1
0 1255

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
14515
by: AMDRIT | last post by:
The stuff below seems to work, when I am applying the logic for suppression. However, it doesn't work when suppressing row data. Any Ideas? Basically, if there is only one row of data, I need to...
2
2933
by: Terry | last post by:
I have a form which displays data from both the Student Details table and the Exam Details table. The Name (Text Box) control is from the Student Details table and the Withdrawn (Check Box)...
2
4234
by: Jon | last post by:
Hi All I am trying to work out the standard deviation for all the columns in a query not just 1 column. Is there a way of selecting the full query and doing a stdev on it? Yours Jon
7
5572
by: (Pete Cresswell) | last post by:
I'm assuming that given the same stream of values, each should return the same result. Anybody disagree? -- PeteCresswell
2
1446
by: JC | last post by:
I am trying to conditionally print a field on a report based on the value of another field in the query that the report is based on. In the Control Source for SerialB I have typed: = IIF(nz(QtyB)...
0
1012
by: adnanahmed714 | last post by:
Hemant & ALL i want to calculate the STDEV of last 20 values added in the databse column.I am using this function but getting the result of all the values present in the...
1
2118
by: adnanahmed714 | last post by:
Hi All i Want to calculate STDEV of last 20 values in the database Column,using VB Want help on the syntax of the statment Thanks in advance
3
1634
by: rdawadiuk | last post by:
hi, i am beginner in access , Just created simple database and trying to figure out correct way to calculate. please help name Amount Action a 1 Deposit b 2 withdraw c 3 ...
33
2518
by: pollyanna | last post by:
Hi there, I hope this is simple for someone. I always figure things out myself, but I thought it would be real nice to see if anyone else out there thought this was super simple and knew the...
0
7119
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
7195
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
6873
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
7367
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
5453
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
1
4889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
0
3088
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
3078
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
285
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

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.