Results 1 to 6 of 6
  1. #1
    Angela12 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    3

    Looking to average with true zero excluding null cells

    I am new to access and I am looking to do a similar task as shown in an excel formula: =ROUND(AVERAGE(Y2,BG2,BH2,BI2,BK2,BN2,BP2)*10,0)



    About the data - I need to average, multiply, and round multiple cells to obtain my final result. The data has true zeros and blank cells that cannot be counted in the average. For example, 1,2,0,1,blank,1 - I need this to be divided by 5, not 4 or 6.

    I would prefer to have this as a calculated table field but I do not know if this is possible or how to write what I need to happen. If this needs to be done in a different way, query, VBA, etc. any additional support would be appreciated.

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    can you make a query that excludes the nulls, THEN run the avg?

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Do not use a calculated table field. Its unnecessary and wastes space.
    It can also fail at times without you being aware of that.
    See http://allenbrowne.com/casu-14.html for more details

    Use an aggregate (totals) query to calculate averages. It will automatically allow for (exclude) null values.
    To test that, create a totals query using the totals button on the ribbon and include a number field including some bills.
    In the totals row, change it to average and run. Note the result.
    Go back to query design view and in the filter criteria enter 'is not null'.
    Run again. You'll get the same result
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Angela12 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    3
    I need to average across columns (unless I am missing a step or point) and also still be able to multiply and round that result.


    Quote Originally Posted by ridders52 View Post
    Do not use a calculated table field. Its unnecessary and wastes space.
    It can also fail at times without you being aware of that.
    See http://allenbrowne.com/casu-14.html for more details

    Use an aggregate (totals) query to calculate averages. It will automatically allow for (exclude) null values.
    To test that, create a totals query using the totals button on the ribbon and include a number field including some bills.
    In the totals row, change it to average and run. Note the result.
    Go back to query design view and in the filter criteria enter 'is not null'.
    Run again. You'll get the same result

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your first post used Excel terminology and your last post suggests you may have imported your data from Excel into Access retaining a spreadsheet type format.
    You shouldn't be 'averaging across columns' in a database

    That's not how databases are intended to be used & you will have enormous problems if you try & do so.
    Whereas if your data is in normalised tables, such calculations become trivial.

    Suggest you show a screenshot of the table you are referring to.
    If the data is confidential, change it or omit it.
    Its the headings that we need to see and/or a screenshot of the table in design view
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Angela12 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    3
    Yes, you are correct in your observation and I know I'm creating several database violations. I appreciate the help. I know what I want and need to do no one system can do and I was trying to do it in Access since the largest solution I'm looking to solve are relationships.

    Thank you for your feedback!

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

Similar Threads

  1. Excluding null values from a totals in a report
    By Andreshan in forum Reports
    Replies: 4
    Last Post: 02-25-2014, 12:52 PM
  2. Replies: 4
    Last Post: 10-08-2013, 11:39 AM
  3. Replies: 2
    Last Post: 10-29-2012, 11:28 AM
  4. Replies: 8
    Last Post: 08-13-2012, 04:30 AM
  5. Excluding null values on SQL table query
    By DB2010MN26 in forum Queries
    Replies: 1
    Last Post: 09-03-2010, 12:54 PM

Tags for this Thread

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