How can I report an average of non zero values?
If the values are:
5, 0, 6, 0, 4
I would like the result 5 (15 / 3), not 3 (15 / 5)
Thanks for any help...
5  7360 
Could you base your average on the values returned by a query which selects
only values > 0?
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:06******** *********@newss vr33.news.prodi gy.com... How can I report an average of non zero values?
If the values are:
5, 0, 6, 0, 4
I would like the result 5 (15 / 3), not 3 (15 / 5)
Thanks for any help...
"Squirrel" <wi*****@covad. net> wrote in message
news:7e******** *************** ***@msgid.megan ewsservers.com. .. Could you base your average on the values returned by a query which
selects only values > 0?
The report is based on a query that computes Count and Avg on about 40
different fields. The query needs to return all of the records because it
might have a zero value in one field and an actual value in another.
The data is survey returns. Each field will have a value of 1 to 7, but can
be 0 (did not answer). I need to ignore the zero entries in getting the
counts and averages.
Randy
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:06******** *********@newss vr33.news.prodi gy.com... How can I report an average of non zero values?
If the values are:
5, 0, 6, 0, 4
I would like the result 5 (15 / 3), not 3 (15 / 5)
Thanks for any help...
"Randy Harris" <ra***@SpamFree .com> escreveu na mensagem
news:K2******** *********@newss vr17.news.prodi gy.com...
"Squirrel" <wi*****@covad. net> wrote in message
news:7e******** *************** ***@msgid.megan ewsservers.com. .. Could you base your average on the values returned by a query which selects only values > 0?
The report is based on a query that computes Count and Avg on about 40
different fields. The query needs to return all of the records because it
might have a zero value in one field and an actual value in another.
The data is survey returns. Each field will have a value of 1 to 7, but
can be 0 (did not answer). I need to ignore the zero entries in getting the
counts and averages.
Randy
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:06******** *********@newss vr33.news.prodi gy.com... How can I report an average of non zero values?
If the values are:
5, 0, 6, 0, 4
I would like the result 5 (15 / 3), not 3 (15 / 5)
Thanks for any help...
Hi,
just a thought:
SELECT Sum(field) / -Sum(field <> 0 ) as myAVG
FROM table;
bob
Hi,
The only way I could figure out how to do this was to run 1 query:
SELECT ur_table.*
FROM ur_table
WHERE (((ur_table.ur_ field)<>0));
and then run a second query using the first query as the source:
SELECT Avg(Query1.ur_f ield) AS AvgOfur_field
FROM Query1;
There maybe a better way of doing this which others may point out but thats the best I could come up with :o)
Mark
"Randy Harris" <ra***@SpamFree .com> wrote in message news:06******** *********@newss vr33.news.prodi gy.com...
How can I report an average of non zero values?
If the values are:
5, 0, 6, 0, 4
I would like the result 5 (15 / 3), not 3 (15 / 5)
Thanks for any help...
Randy Harris wrote: How can I report an average of non zero values?
If the values are:
5, 0, 6, 0, 4
I would like the result 5 (15 / 3), not 3 (15 / 5)
Thanks for any help...
(f1+f2+f3+f4+f5 ) / ((f1>0+f2>0+f3> 0+f4>0+f5>0)*-1) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: PeterHardy |
last post by:
I'm trying to work out an average field on a report that i'm writing
and having the problem described below.
e.g. if you had 3 weeks of cash which were 0, 2, 4 then a normal
average
would be ((0+2+4)/3) = 2 but the average that I want is (2+4)/2 = 3.
Now I can do this in SQL by using AVG(cash) with a WHERE cash>0. But
the
trouble comes when all cash values are zero for all all columns on the
|
by: Stephen Miller |
last post by:
Hi,
I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI).
Given the sample data:
|
by: Wayne Aprato |
last post by:
I've read most, if not all, of the posts on moving average and still
can't find a simple solution to my problem (if a simple solution
exists!) I have a table with 2 fields: Hours and Injuries. I have a
query based on this table which shows these 2 fields and calculates a
third field: Frequency Rate, based on a formula which uses the Hours
and Injuries fields.
Is there a simple way of A: using yet another calculated field in the
query...
|
by: CSDunn |
last post by:
Hello,
I have 14 fields on a report that hold integer values. The field names
use the following naming convention: T1Number, T2Number ....T14Number.
I need to get a 'sub total' of all fields as follows:
=Sum() ... =Sum()
Then I need to get an average of all fields as follows:
|
by: David Peach |
last post by:
Hello, hope somebody here can help me... I have a query that lists
defects recorded in a user defined date range. That query is then used
as the source for a Cross Tab query that cross-tabs count of defect
type by calendar month. Defect types are stored in one table, defect
transactions in another along with date etc. When I cross-tab the
results, defect types that have no defects recorded against them
appear as a blank (null) value. That...
| | |
by: Gary Carson |
last post by:
Can anyone tell why the query below would throw a divide-by-zero
error? The only reason I can see for the error happening would be if
SUM() came out to be zero, but this never happens with the
data I'm using. SOME of the values in the EXP_NET column are zero, but
the sum itself is always non-zero.
The query is:
TRANSFORM SUM(-)/SUM()*100 AS DATA SELECT
TBL.EQUIPMENT AS EQUIPMENT,
|
by: OutdoorGuy |
last post by:
Greetings,
I am attempting to compile the code below, but I am receiving an error
message when I do so. The error message is: "CSO161:
'Forloop.CalcAvg(int)': Not all code paths return a value". Any idea as
to what I'm doing wrong? I'm sure it's something simple.
Thanks in advance!
public class ForLoop
|
by: Mark P |
last post by:
I'm implementing an algorithm and the computational flow is a somewhat
deep. That is, fcn A makes many calls to fcn B which makes many calls
to fcn C, and so on. The return value of the outermost fcn is a boolean
and there are certain places within the inner functions where it may
become apparent that the return value is false. In this case I'd like
to halt the computation immediately and return false.
My current approach is to have...
|
by: Toby Gallier |
last post by:
Hello!
I have a form that is calculating averages as follows:
" =(NZ()+Nz()+Nz())/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to base my average on just 2 values
instead of 3 i am currently using in my string. How can i have the
form update the "3" based on the number of values that are populated?
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
