Earliest Date, ingnoring text #N/A

[crazyengrchic]

Hi,

Please help with formula to calculate earliest date, ignoring cells with data #N/A. I tried a few different formulas and think I'm close.

=IF(AND(I4="#N/A",P4="#N/A",X4="#N/A",AF4="#N/A",AN4="#N/A",AV4="#N/A",BC4="#N/A",BK4="#N/A"),"#N/A",MIN(I4,P4,X4,AF4,AN4,AV4,BC4,BK4))

Thanks!

[benishiryo]

hi crazyengrchic, welcome to the forum. try this:
=MIN(IF(ISNUMBER(I4:BK4),(I4:BK4)))
in the formula itself, hit CTRL + SHIFT + ENTER
instead of just pressing ENTER

let me know if it works

[crazyengrchic]

Hi benishiryo. Thanks for the formula! I'm getting a result of 1/0/1900 for each calculation.
I'm working on a spreadsheet created by others. The #N/A is derived from a subtraction calculation based on another cell using formula =VLOOKUP(B4,'Multi-Category Schedules Co'!B:C,2,FALSE).

[benishiryo]

hmmm is it a confidential file to share? i am not gettin the result u mentioned, based on my created scenario with #N/A & dates. maybe try this:
=MIN(IF(ISNUMBER(I4:BK4),(I4:BK4),100000))

also with CTRL + SHIFT + ENTER

[Pepe Le Mokko]

1/0/1900 is the equivalent of 0 formatted as a date - The suggested formula calculates the min over the entire range I4:BK4, which does not seem to be what you want
Perhaps adding a small example sheet will help?

[Cutter]

Do you need those cells to show #N/A (for chart purposes)? If not, change the formulas so that you suppress the #N/A's and then you can just use your MIN() function:

Use: =IFERROR(VLOOKUP(B4,'Multi-Category Schedules Co'!B:C,2,FALSE),"")

[crazyengrchic]

Equip_Spreadsheet_Print_Screen.docx
Hi benishiryo, =MIN(IF(ISNUMBER(I4:BK4),(I4:BK4),100000)) still resulted in the 1/0/1900 calculated date.
Hi Cutter, the #N/A results for buildings with 0 quantity of equipment.
The spreadsheet is 20M so I'm unable to attach it. There are other worksheets embeded with pivot tables that provide data. The spreadsheet tracks equipment to be installed with quantites per building as well as required dates for cut sheets. I'm trying to figure out the ealiest date that equipment cut sheets will be required (column L).
I've attached a print screen view for reference.
Thanks!

[Cutter]

#N/A's are the result of no matches being found, not 0 results. So, you didn't answer my question. Do you need to see #N/A? If not, use the VLOOKUP() within the IFERROR() to suppress the #N/A.

[crazyengrchic]

Hi Cutter, the cell I'm pulling data from is using an =x-y formula so instead of changing the VLOOKUP(), can you help me with a formula that IF (x-y) = 0, then leave blank (or suppress the #N/A) since the cell I'm calculating from is using that.

[Cutter]

Whatever the formula is in I4 put it within the IFERROR(). And the same with the other row 4 formulas.

So: =IFERROR(whatever your formula is,"")

Once you have done this you can use your =MIN(I4,P4,X4,AF4,AN4,AV4,BC4,BK4) because the MIN() will ignore the cells that return "".

[crazyengrchic]

Hi Cutter,

I’ve tried the following:
=MIN(IF(ISNUMBER(I4:BL4),(I4:BL4),100000)) --> 10/14/2173
=MIN(IF(ISNUMBER(I5:BL5),(I5:BL5))) --> 1/0/1900
=MINIF((I7:BL7),"<>#N/A") --> #NAME?
=MINIF((I8:BL8),"<1E99") --> #NAME?
=MIN(IF(ISNUMBER(N11:BW11),N11:BW11)) --> 1/0/1900
=IFERROR(MIN(IF(ISNUMBER(I14:BL14),(I14:BL14))),"") --> 1/0/1900

Also, my formula is a bit more difficult than I’ve shown above since the cells I need to pull data from are not adjacent to one another) .. and when I select the specifc cells for the formula, I’m getting an error for using too many formulas…

[Cutter]

What is the formula you originally had in I4?

[crazyengrchic]

Also, is there a reason why =IFERROR(VLOOKUP(B4,'Multi-Category Schedules Co'!B:C,2,FALSE),"") would result in =IFERROR(VLOOKUP(B4,'Multi-Category Schedules Co'!B:C,2,FALSE),"")??

[crazyengrchic]

I originally had =IF(AND(I4="#N/A",P4="#N/A",X4="#N/A",AF4="#N/A",AN4="#N/A",AV4="#N/A",BC4="#N/A",BK4="#N/A"),"#N/A",MIN(I4,P4,X4,AF4,AN4,AV4,BC4,BK4))

[Cutter]

You're not reading what I am asking: from post #12: "What is the formula you originally had in I4?"

Also, is there a reason why =IFERROR(VLOOKUP(B4,'Multi-Category Schedules Co'!B:C,2,FALSE),"") would result in =IFERROR(VLOOKUP(B4,'Multi-Category Schedules Co'!B:C,2,FALSE),"")??

Is the cell formatted as Text?

[crazyengrchic]

Hi Cutter, I originally had =IF(AND(I4="#N/A",P4="#N/A",X4="#N/A",AF4="#N/A",AN4="#N/A",AV4="#N/A",BC4="#N/A",BK4="#N/A"),"#N/A",MIN(I4,P4,X4,AF4,AN4,AV4,BC4,BK4))

[crazyengrchic]

Cell is formatted as date

[Cutter]

You can't have that formula in cell I4 because that formula is checking to see if I4 is #N/A and using it in the MIN() portion.
One last time: What formula do you have in I4? (or P4 or X4 or AF4 or AN4 or AV4 or BC4 or BK4)?????

[crazyengrchic]

For I4, I originally had =MIN(I4,P4,X4,AF4,AN4,AV4,BC4,BK4). (I've since added columns for additional data.) and it is formatted as date.

I was able to fix the issue for Post #13 .. you were correct, it was a formatting issue. Thx!!

[Cutter]

Well, again, that formula refers to I4 so that can't be what was in I4 or you would get a Circular Reference error.

[crazyengrchic]

Hi Cutter, after correcting all the cells with the IFERROR statement (which cleared all the #N/A results) and ensuring that formatting is DATE, I'm now getting the correct results using M4 =MIN(O4,V4,AD4,AL4,AT4,BB4,BI4,BQ4). You were correct about my circular reference errors as welll. Thank you for sticking with me through this one!!!! You're so wonderful =)

[Cutter]

Terrific! You're welcome.