+ Reply to Thread
Results 1 to 4 of 4

How to avoid including blank cells in MONTH function for January months?

  1. #1
    Registered User
    Join Date
    10-16-2009
    Location
    Moscow
    MS-Off Ver
    Excel 365
    Posts
    39

    How to avoid including blank cells in MONTH function for January months?

    Hello,

    I'm trying to calculate average January-only returns for my data using ={AVERAGE(IF(MONTH(B3:B40)=1,$C$3:$C$40))}. In order to not manually update the range for my calculations in the future, I have included blank cells in the range of calculation.
    However, adding blank cells incorrectly calculates the Average January return, because Excel assumes that blank each cell is equal to January 1, 1900 and hence assigns a value of 1 for the each blank cell.

    I've tried to add a filter of YEAR > 1900, but that didn't help.

    I would greatly appreciate if someone could help me to resolve this issue (please see attached Excel file).
    Attached Files Attached Files
    Last edited by Salomey; 11-25-2013 at 07:00 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to avoid including blank cells in MONTH function for January months?

    Try

    =AVERAGE(IF((MONTH(B3:B40)=1)*(ISNUMBER(B3:B40)),$C$3:$C$40))

  3. #3
    Registered User
    Join Date
    10-16-2009
    Location
    Moscow
    MS-Off Ver
    Excel 365
    Posts
    39

    Re: How to avoid including blank cells in MONTH function for January months?

    It works, thanks for the help.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to avoid including blank cells in MONTH function for January months?

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. using frequency function not including blank cells in the data array
    By alliugiv in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2013, 08:53 AM
  2. Chart the last 3 Months including the current month
    By ioncila in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2012, 01:22 PM
  3. Replies: 1
    Last Post: 11-22-2011, 08:11 AM
  4. [SOLVED] how can avoid considering of blank cells in IF function
    By Lika in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2006, 05:30 AM
  5. [SOLVED] using date function, month shows as January when i type (12)
    By hsas in forum Excel General
    Replies: 3
    Last Post: 06-17-2005, 03:05 PM

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