+ Reply to Thread
Results 1 to 6 of 6

Min array formula ignore current month

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007, 2010 & 2013
    Posts
    17

    Question Min array formula ignore current month

    Excel Min If.JPG

    I need help with this formula. It works great except, I don’t want it to include the current months numbers, in this case Jan 15 as it is a dynamic number. I would like the formula to step through all the cells looking for the MIN, ignoring the #NA and any numbers in the current month.
    I suspect it would be something along the lines of {=MIN(IF(DATE(TODAY(MM)-1(ISNA(B3:Y3))),B3:Y3))}
    And entered as an array {}
    I’ve tried some seriously whacked combinations and can’t quite get it to ignore the current month and actually work.

    TIA
    -Ray

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Min array formula ignore current month

    You can adapt this formula for your needs.

    =MIN(IF(MONTH(A3:C3)<>MONTH(TODAY()),A4:C4))


    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

    Row\Col
    A
    B
    C
    D
    E
    3
    1/15/2015
    12/31/2014
    10/12/2014
    6
    4
    5
    6
    10
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    09-11-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007, 2010 & 2013
    Posts
    17

    Re: Min array formula ignore current month

    Not sure where I am going wrong, I've tried various combinations and ended up with this, Excel forced me to use the * in the formula, but then generated a #N/A in the cell.

    Tried: =MIN(IF(MONTH(B2:Y2)<>MONTH(TODAY())*NOT(ISNA(B3:Y3)),B3:Y3))
    (Entered as an array)

    Am I using the Month argument in the wrong place?

    TIA
    -Ray

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Min array formula ignore current month

    It worked fine for me as you can see from the posted example.

    another thing just came to mind is that you also need to include YEAR in this formula

    =MIN(IF((MONTH(B2:Y2)<>MONTH(TODAY()))*(YEAR(B2:Y2)<>YEAR(TODAY())),B3:Y3))
    Last edited by AlKey; 01-15-2015 at 05:36 PM.

  5. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Kansas
    MS-Off Ver
    Excel 2007, 2010 & 2013
    Posts
    17

    Re: Min array formula ignore current month

    I think my use of the "NOT(ISNA(B3:Y3))" portion may be preventing it from working?

    Trying to find the minimum, excluding the current months data and any #N/A's in cells B3:Y3

    {=MIN(IF(MONTH(B2:Y2)<>MONTH(TODAY())*(YEAR(B2:Y2)<>YEAR(TODAY()))NOT(ISNA(B3:Y3)),B3:Y3))}

    Excel Min If 2.JPG
    Last edited by RPM509; 01-15-2015 at 05:50 PM.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Min array formula ignore current month

    Can you upload your spreadsheet?

+ 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. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  2. [SOLVED] Formula for amount on previous month based on current month
    By Yu Marquez in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2014, 12:42 AM
  3. [SOLVED] Pick a cell containing current month actulas based what the current month is.
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2014, 01:17 AM
  4. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  5. Replies: 6
    Last Post: 08-30-2011, 08:47 AM

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