Results 1 to 4 of 4
  1. #1
    jre1229 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    30

    Expression to create averages from multiple fields ONLY where field is not 0


    I'm trying to build something to calculate forecast and below is what I have. The problem is when we release a product in the middle of the year, I don't want to average in all of the "0" months. How can I do something similar to the expression below but only average out months with >1 sales?

    Code:
    Average Month: ([Forecast]![Jan]+[Forecast]![Feb]+[Forecast]![Mar]+[Forecast]![Apr]+[Forecast]![May]+[Forecast]![June]+[Forecast]![July]+[Forecast]![Aug]+(([Forecast]![Sept]+[Forecast]![Oct]+[Forecast]![Nov]+[Forecast]![Dec])*1.25))/12

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Similar columns for each month is not normalized structure. If the data were normalized, an aggregate query could do the average and null values will be ignored.

    Do the "0" months actually have zero or are they null?

    ([Jan]+[Feb]+[Mar]+[Apr]+[May]+[June]+[July]+[Aug]+([Sept]+[Oct]+[Nov]+[Dec])*1.25)) /

    (IIf([Jan]>0,1,0)+IIf([Feb]>0,1,0)+IIf([Mar]>0,1,0)+IIf([Apr]>0,1,0)+IIf([May]>0,1)+IIf([June]>0,1,0)+IIf([July]>0,1,0)+IIf([Aug]>0,1,0)+IIf([Sept]>0,1,0)+IIf([Oct]>0,1,0)+IIf([Nov]>0,1,0)+IIf([Dec]>0,1,0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jre1229 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    30
    Most of them have 0, but there are a few nulls. That appears to work, is there anything I should be concerned about with regards to nulls? Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, arithmetic with null results in null. Handle possible null with Nz in the dividend, should not be an issue in the divisor:

    Nz([Jan],0)

    However, if the divisor results in 0, will get an error because can't divide by 0.

    Oh, the pains of non-normalized data!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-03-2013, 06:00 AM
  2. Replies: 20
    Last Post: 09-12-2012, 06:52 PM
  3. One MS Access Query - Multiple Averages?
    By ajcke in forum Queries
    Replies: 9
    Last Post: 05-09-2012, 07:16 AM
  4. Replies: 12
    Last Post: 05-07-2012, 12:41 PM
  5. Replies: 7
    Last Post: 12-30-2009, 11:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums