Closed Thread
Results 1 to 2 of 2

ignore null data in a sum

  1. #1
    Registered User
    Join Date
    07-09-2009
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    ignore null data in a sum

    It seems like there should be a simple solution for this problem, but I've been tinkering with it for awhile and can't come up with anything.

    I have two sheets in my workbook: one holds data (Sheet2), the other processes the data (Sheet1). Sheet2 has temperature data for each hour of every day during the year 1997 - about 9000 rows. Sheet1 averages the temperatures for each day together, and therefore has only 365 rows.

    However, some of the data is missing - null values are represented by the value -9999. Sheet1 only averages hourly data if there at least 22 non-null values. If there are fewer than 22 non-null values, a message is written into the cell to indicate such. (See day 119 on Sheet1 in the attached file for an example of this)

    I have the formula worked out to this point; however, I'd like to add in one more constraint: if a day has 2 or fewer null values, I still would like to average the data, but omit the null values in the average. (See day 118 on Sheet1 in the attached file for an example of this)

    My formula so far:
    Please Login or Register  to view this content.
    Any suggestions?
    Last edited by ruberg; 07-24-2009 at 02:27 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: ignore null data in a sum

    Duplicate post, thread closed.
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1