Using "Avg" function on calculated field

In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works 
fine. In the report footer I tried to get the average of the values in this 
column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for 
the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no 
error is generated either. The output for the average is just blank. I tried 
=Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I missing?
0
Utf
3/13/2007 8:09:16 AM
access.reports 4434 articles. 0 followers. Follow

2 Replies
949 Views

Similar Articles

[PageSpeed] 29

There's a couple of potential issues here.

It sounds like Hrs is a field in the table/query the report is based on, but 
do you have a text box for it on the report? If not, add one. Visible = No 
if you like. Sometimes the report optimizer is just a bit too smart: if it 
finds no control on the report bound to the field, it doesn't bother to 
fetch the field, so expressions involving the field fail. The text box bound 
to the field forces it to fetch the field.

Presumably Total and Hrs are fields of type Number (not Text.) That's 
correct, but Access is not always clear about the data type once it comes 
out of Nz(). You might try:
    = Avg( CDbl( Nz( [Total], 0)) / [Hrs] )
and also set the Format property of the text box to Number.

The expression is a bit unusual. Normally the fields where Total is null do 
not affect the average. Using Nz() forces those cases to zero, which will 
lower the overall average. If that is not what you want, you can drop the 
Nz().

Another option is to move the expression into the report's source query as a 
calculated field:
    TotalOnHrs: CDbl( Nz( [Total], 0)) / [Hrs]
so in the report footer you can simply:
    =Avg([TotalOnHrs])

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Moe" <Moe@discussions.microsoft.com> wrote in message
news:875D6939-62B9-43C0-A344-E8938E5132A0@microsoft.com...
> In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works
> fine. In the report footer I tried to get the average of the values in 
> this
> column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for
> the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no
> error is generated either. The output for the average is just blank. I 
> tried
> =Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I 
> missing? 

0
Allen
3/13/2007 8:25:15 AM
In a reprort, you cannot sum a text box (nor an expression in a text box.) 
You can sum a numeric field.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Moe" <Moe@discussions.microsoft.com> wrote in message
news:93072139-650A-409D-9789-A3186863B055@microsoft.com...
> Thank You Allen
> CDbl did not solve the problem. Some how Report could not find "Hrs" even
> though it could find it in the detail section. (I do have a field name 
> "Hrs"
> in the report.) Then in the Avg function I changed the "Hrs" to its 
> "control
> Source" which is "manHrs" then it worked. Can you explain it to me that 
> why
> in the detail section it works with the field name but not in the Avg
> function? My understanding is that field name should be used every where.
>
> "Allen Browne" wrote:
>
>> There's a couple of potential issues here.
>>
>> It sounds like Hrs is a field in the table/query the report is based on, 
>> but
>> do you have a text box for it on the report? If not, add one. Visible = 
>> No
>> if you like. Sometimes the report optimizer is just a bit too smart: if 
>> it
>> finds no control on the report bound to the field, it doesn't bother to
>> fetch the field, so expressions involving the field fail. The text box 
>> bound
>> to the field forces it to fetch the field.
>>
>> Presumably Total and Hrs are fields of type Number (not Text.) That's
>> correct, but Access is not always clear about the data type once it comes
>> out of Nz(). You might try:
>>     = Avg( CDbl( Nz( [Total], 0)) / [Hrs] )
>> and also set the Format property of the text box to Number.
>>
>> The expression is a bit unusual. Normally the fields where Total is null 
>> do
>> not affect the average. Using Nz() forces those cases to zero, which will
>> lower the overall average. If that is not what you want, you can drop the
>> Nz().
>>
>> Another option is to move the expression into the report's source query 
>> as a
>> calculated field:
>>     TotalOnHrs: CDbl( Nz( [Total], 0)) / [Hrs]
>> so in the report footer you can simply:
>>     =Avg([TotalOnHrs])
>>
>> "Moe" <Moe@discussions.microsoft.com> wrote in message
>> news:875D6939-62B9-43C0-A344-E8938E5132A0@microsoft.com...
>> > In a report I have a calculated field as "=Nz([Total],0)/Hrs" which 
>> > works
>> > fine. In the report footer I tried to get the average of the values in
>> > this
>> > column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me 
>> > for
>> > the value of "Hrs". Neither "hrs" nor "total" are calculated fields and 
>> > no
>> > error is generated either. The output for the average is just blank. I
>> > tried
>> > =Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I
>> > missing? 

0
Allen
3/14/2007 12:12:19 AM
Reply:

Similar Artilces:

AVG Virus software
Hi, I use the free version of AVG. When I looked at my VIRUS VAULT I saw about 10 entries, some described as virus, but I am not sure what to do to get rid of these things. Do I select EMPTY VAULT or else select each item and click on DELETE ? Thanks, Peter Since it's not part of Windows XP, you would ask here: http://forums.avg.com/us-en/avg-free-forum "Peter Buttuls" <ye025@victoria.tc.ca> wrote in message news:%23dhtl4%23hKHA.4912@TK2MSFTNGP02.phx.gbl... : Hi, : I use the free version of AVG. When I looked at my VIRUS VAULT I saw about : 10 entr...

removal of AVG add in from Outlook
How do I remove the "AVG Exchange Extension" - an AVG add-in from Outlook (PROGR 1\AVG\AVG9\avgxch32.dll ). Many thanks Kathie Start Microsoft Outlook and then select Tools | Options | Other tab | Advanced Options button | Addin manager and deselect AVG. If you have uninstalled AVG, then you just need to find and delete (or rename) the file named extend.dat. Since you don't mention what version of Outlook or your operating system, the best I can tell you it is a hidden file under your OS user profile. For example, you might find it at: C:\Documen...

Exclude zeros 0s from avg function in Reporting Services 2005
Hi all, I have what I think is quite a good question - ie, difficult to answer! :) Supposing I have 10 rows each containg a fee value but only one row actually has a figure in it of, lets say, 10. All the other rows by default, instead of being empty will show zeros. If I wanted to display the average for all the rows, I simply add a group, add a footer and add the expression "=AVG(Fields!Fee.Value)" This would return the value 1 (10 divided by 10 rows) However, I want to only average where the value is NOT zero. I tried the following but just got "#Error" ...

Avg. Difference between dates
Say I have the following dates, where the left column is the date the item was opened and the right column is the date it was closed (blank if not yet closed): Open Closed 3/8/07 5/2/07 4/16/07 -- 1/30/07 3/4/07 3/20/07 4/15/07 2/12/07 -- I would like to have a function that would give the average number of days between open and close dates. However: if the item is still open, the number of days should be assumed to be today's date minus the open date. But: if the today's date minus the open date is less than 30 days, it should not be counted toward the average at all. Any ...

AVG has disappeared!
I am running a Vista 64-bit platform with a VMware guest, using Vista 32-bit. Although I have Windows Defender running in the guest system, I have not had any anti-virus software (I use McAfee on the host system). Last night, I downloaded and installed the AVG-free anti-virus software for the guest system. It was working as intended when I shut down the computer. This morning, in booting to the guest system, there is no AVG software in sight. The downloadable program from AVG is not even found in the Downloads folder! Windows Defender is still operable, and telling me that I...

Avg. All #'s on sheet
Hi there...Anyone know how I could write a formula that will average al #'s on my sheet...The range will have text in it that I want it t ignore. Thank -- Message posted from http://www.ExcelForum.com nevermind....should have just tried it first. thank -- Message posted from http://www.ExcelForum.com ...

Avg. Mailbox size for Mid-size company
I am tasked to find the average mailbox size of a general Exchange end-user in a mid-size company that is equivalent to one that I work for: 700 to 800 employees with approx. 550 to 600 email accounts. We are trying to come up with a policy to “encourage” people start cleaning out and deleting unimportant items and archiving the important ones from their inbox and sent items box. Since implementing Exchange 2000 (which was migrated from Groupwise), our storage has grown exponentially in the last two years! We have people that have not cleaned out their inbox in the last five years, ...

AVG (zero shall not included)
i want to average the below data. July - 98 August -85 Sept- 0 October- 80 November -75 How could i calculate it, i would like not to affect the result by zero. The formula should turn like this. avg(98+85+80+75)3 thanks. -- Message posted via http://www.accessmonster.com Avg ignores nulls. So a function like avg(iif([yourFieldName]=0, null, ([yourFieldName]) should do what you want. "mroks via AccessMonster.com" <u35438@uwe> wrote in message news:815e73f5bab21@uwe... >i want to average the below data. > > July - 98 > August -85 > Sept- 0 > Octo...

How do I select the last 5 non zero values in a row & get an avg?
In excel I want to get the last 5 non zero values from a row (or column) and get an average. On Wed, 24 Aug 2005 11:27:01 -0700, "Larry L" <Larry L@discussions.microsoft.com> wrote: >In excel I want to get the last 5 non zero values from a row (or column) and >get an average. For Rows: =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1))) For Columns: =AVERAGE(TRANSPOSE(OFFSET(A1,LARGE((A1:A65535>0)*(ROW(A1:A65535)),{1,2,3,4,5})-1,0))) These are array formulas so after typing them in, hold down <ctrl><shift> while hitting...

calculation method?? Avg. cost
I don't get how money calculates average cost. Check this out: I purchased 3000 shares of ABCD for 0.34, then purchased 3000 more shares for 0.335. Each purchase is 29.00 commission (Canada). Money says my average share price is 0.322. My math says that my average cost should be .347 I'd appreciate any input on this matter. Cheers, CBall In microsoft.public.money, Footman wrote: >I don't get how money calculates average cost. Check this out: > >I purchased 3000 shares of ABCD for 0.34, then purchased 3000 more shares >for 0.335. Each purchase is 29.00 commi...

Conditional AVG in report footing
I am trying to do a conditional avg in my report footing. I would like to avg values that are on the same row as an item between 7000 and 9000. for instance 6000 2 7500 3 8000 4 8500 5 9500 6 the answer would be 4 I have tried both of the following to no avail =IIf(([RPM]>7000) And ([RPM]<9000),Avg([rpm]),0) and SUM(iif(([RPM]>7000) And ([RPM]<9000),[RPM],0))/SUM(iif(([RPM]>7000) And ([RPM]<9000),1,0)) neither of which have worked. any ideas would be appreciated. What is the name of the second field? You did not...

running avg formula that ignores blanks
I am attempting to build a formula that averages a column of data (that gets added to every day), but ignores, or does not include blank rows. Any hints? One way: =AVERAGE(A:A) In article <#GyhuiDsHHA.884@TK2MSFTNGP02.phx.gbl>, "Happy" <happy@trial.ca> wrote: > I am attempting to build a formula that averages a column of data (that gets > added to every day), but ignores, or does not include blank rows. > Any hints? I'm not sure I understand this........ possible to give an example? "JE McGimpsey" <jemcgimpsey@mvps.org> wrote i...

AVG (zero shall not included)
i want to average the below data. July - 98 August -85 Sept- 0 October- 80 November -75 How could i calculate it, i would like not to affect the result by zero. The formula should turn like this. avg(98+85+80+75) thanks. -- Message posted via http://www.accessmonster.com ...

AVG(COUNT(timestamp))...this will not work, what am I doing wrong?
Please help me. I want to find the average of the number of timestamps that are in several tables. When i run the following, it will not work because I can only use avg with a number datatype. (at least, that's what i understand) I am just learning the syntax rules. the following works as long as i don't include avg. Please help me. Select avg( count(timestamp),area), datepart(hh,timestatmp), datepart(wd, timestamp) From lotsoftables where timestamp between 05/01/2007 and 06/20/2007 Group by timestamp,datepart(hh,timestatmp), datepart(wd,timestamp),area Thanks in advance. -Misty --...

AVG (zero shall not included)
i want to average the below data. July - 98 August -85 Sept- 0 October- 80 November -75 How could i calculate it, i would like not to affect the result by zero. The formula should turn like this. avg(98+85+80+75) thanks. -- Message posted via http://www.accessmonster.com On Wed, 19 Mar 2008 03:01:46 GMT, "mroks via AccessMonster.com" <u35438@uwe> wrote: >i want to average the below data. > >July - 98 >August -85 >Sept- 0 >October- 80 >November -75 > >How could i calculate it, i would like not to affect the result by zero. > >The for...

Avg days to pay
In collections management, there's a 'STATS' window. In that window are 2 fields, avg days to pay year and life. Can anyone tell me how those are populated. We have some customers that have an amount listed and others with balances that don't show anything. Thanks in advance. Peter, The average days to pay is updated every time a sales invoice is fully applied. So if you have customers with balances but no fully applied invoices, you will not see these statistics for them. As a side note, sometimes these stats can be a little misleading if you do a lot of credit ...

Avg Costing and PO Returns
Inventory Valuation is Average perpeptual. We purchase items from many vendors. Receipt 1 - from Vendor A, qty = 5 and cost = $2 Receipt 2 - from Vendor B qty = 5 and cost = $3 Average Cost is $2.50 (no problem) The problem comes with the inabilty to select the proper receipt for a Purchase Order Return. Let's say we consume 6 of these through sales - that leaves a balance of 4. Let's say the 4 that are physically left are from Vendor A and not Vendor B. This is common for us - we don't lot track and we don't faithfully rotate stock perfectly. We discover the 4 rema...

Using "Avg" function on calculated field
In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works fine. In the report footer I tried to get the average of the values in this column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no error is generated either. The output for the average is just blank. I tried =Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I missing? There's a couple of potential issues here. It sounds like Hrs is a field in the table/quer...

avg formula and blank cells
What is the proper way when using the AVG function with cells formatted as numbers, to fill a blank cell so it won't make the avg lower, but rather be ignored in the AVG function? Thanks! --Randy Starkey __________ Information from ESET NOD32 Antivirus, version of virus signature database 4080 (20090515) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Excel ignores blanks cells when averaging. If you mean ignore cells uncertain a certain value. In Excel 2007, use AverageIf. In earlier versions you need to construct the formula using SumIf ...

posting avg. cost changes to the GL
When I use the Change Average Cost Utility, the report that prints out (which states that adjustments are not automatically made to the GL) shows the inventory account for both DR and CR, leaving a $0 balance. It does not show the inventory adjustment account at all. I don't understand this. The Tech.Knowledge article I read said the report would display Inventory account AND the variance account. I know that the items all have a variance account .. . . though I did just notice that they do not have accounts listed for unrealized purchase price variance. Is this why it is comin...

Storing Avg of three measurements
All, I would like to have an input form that has several measurements typed into it, but I want to store only the final average of these measurements into a single field. We work in a laboratory setting where several thickness are measured to obtain a final average thickness used for density calculations. I will never need the individual measurements stored.......just the final average. Any help would be greatly appreciated ! Thanks! You will need a form with 3 unbound text boxes (for entering the 3 original values you don't need stored), and one text box b...

Physical Disck avg read/write/sec >50ms
We use Exchange 2003 SP2 and use RAID 10 and searate spindles for transaction logs, queues etc, we also use MOM 2005 and it sometimes alerts me and says: (these alerts might happen once every other day), what can cause them or how can I find out who it is? Name: Disk Write Latencies > 50 msec (H: Drive) Description: High Disk Write Latencies for the past 10 minutes PhysicalDisk: Avg. Disk sec/Write: 1 H: value = 5.21892528697291E-02. The average over last 10 samples is 0.0521893. or Source: PhysicalDisk: Avg. Disk sec/Read: 3 J: Name: Disk Read Latencies > 50 msec (J: Driv...

Excluding Cells From Avg Cals Due to Info in different col
I have an investment spreadsheet that shows the qunaity I bought or sold, buy date, buy price, sell date, and sell price. I am trying to calcualte the average buy price for the investments. Unfortunatley when you sell it includes a buy price as well. I need to exclude these cells from the average. Below is the info. Col C Col D Col E Col F Col G Quantity Buy Date Buy Price Sell Date Sell Price 18.755 5/21/2008 36.835 0.000 0.009 5/21/2008 36.835 12/31/2008 24.846 0.010 5/21/2008 36.835 3/31/2009 24.329 0.592 7/3/2008 33...

Prior 3 Mo Avg Function
I am trying to find easier way to calculate prior 3 mo average. Currently, I am doing the manual calculation in Excel after importing from Access. I would like to calculate in Access. In the table, I want to calculate period 12/31/2009 where I need the 3mo average of the prior 3 mo count. I wonder if there is a function that can say for current month what is the prior 2 mo avg (counting the current period) PERIOD COUNT 3-MO AVG 10/31/2009 1000 11/30/2009 2000 12/31/2009 3000 2000 Your help is appreciated! Cru Us...

Capital Gains Report: Mutual Fund
Hi, I'm trying to run a report for my mutual fund, that experienced monthly contributions, to calculate the capital gains for several redemptions i've made in 2008 for my taxes. I changed the default option (Tools->Settings->Investment Settings) and have unticked the "Track all investments as FIFO". When i run the built in "Capital Gains" repot, it appears to be calculating FIFO as it enumerates the gain/loss for each purchase transaction and summarizes it. I don't see an option for the investment/account to change/display the Cost Basis method. ...