Share via

MIN(IF) array formula not quite working

Anonymous
2021-05-17T15:27:45+00:00

I have a formula that works most of the time but not completely and I can't work out why. It's in a workbook that incorporates queries from other workbooks. 

It's an array formula, and it's working in Table 2:

{=MIN(IF(Table1[Rig]=[@RigName],IF(Table1[Next Inspection]>0,Table1[Next Inspection])))}

It is meant to pick the min value from the column [Next Inspection] in Table1 where the value in [Rig] column equals the value in [RigName] column from Table2. It does pick dates that meet the criteria, but not necessarily the lowest value.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-17T16:44:23+00:00

    pls can you check the text HTFC08 against 22/05/2021, 

    Press F2 and check or do a = comparison with the same text in table2

    if there is a space after it, filter wont pick it.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-05-17T16:01:41+00:00

    Hi,

    I'm Gustavo, an independent advisor. I'll be glad to help you.

    Could you send screenshots of your spreadsheets? It would be easier to find out the reason for the error.

    Best regards.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-05-17T15:58:38+00:00

    it would help if you could share a screenshot of the data and the formula, indicating where the formula is not working... or alternatively upload a sample file to onedrive etc and share a link

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-05-17T15:53:00+00:00

    Thanks, I tried what you suggest but that doesn't seem to be it. Also, if I go to Table1 and put =SUBTOTAL(5,Table1[Next Inspection]) with a filtered table I get the correct result, which shouldn't happen if they were text.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-05-17T15:40:20+00:00

    the formula, seems to be correct ... my guess would be the dates in the "Next Inspection" column are entered as text

    you can try changing the formula to

    =MIN(IF(Table1[Rig]=[@RigName],IF(Table1[Next Inspection]>0,Table1[Next Inspection]+0)))

    or alternatively,

    to quickly convert the dates entered as text to dates,

    • type 0 in any cell,
    • format it as date and
    • copy > paste special > add to the "Next Inspection" column

    Was this answer helpful?

    0 comments