Earlieast date from a range (2)

[paul_aramis]

from here.


the formula has to satisfy another condition. a date should be the return only when a shortage occur. this means that if 'Part!C1 = 0 (no date, meaning that inspection did not occur yet), 'Part!B1 = MIN('Job!B:B) IF('Part!A1 = 'Job!A:A) and IF('Job!F:F<0)

I use Peter's formula on 'Part!B:B, with the above condition for inspection and add the correct results on 'Part!E:E.

the tables has other columns, which I eliminate (those columns contain informations that are not useful for the formula)

[wfm007]

Use this one

Please Login or Register  to view this content.

With CTRL + SHIFT + ENTER

[paul_aramis]

thanks wfm007. almost there!

used your formula, got the right date but not only. the rest of the dates are returned too, as 01/00/00, for all parts. used your formula to adapt peter's formula, no result returned for parts from 'Part!A:A which are not on 'Job!A:A but yet dates (as 01/00/00) returned for those on 'Part!A:A and 'Job!A:A with no shortage. excel file attached.

[wfm007]

In that case use this one:

Please Login or Register  to view this content.

[Tony Valko]

In that case use this one:

=IF(MIN(IF((Job!A:A=Part!A2)*(Job!F:F<0),(Job!B:B),""))=0,"",MIN(IF((Job!A:A=Part!A2)*(Job!F:F<0),(Job!B:B),"")))

You should avoid using entire columns as range references in array formulas. Use a smaller specific range.

Array entered**:

=IFERROR(1/(1/MIN(IF(Job!A2:A100=Part!A2,IF(Job!F2:F100<0,Job!B2:B100)))),"")

Nested IFs are slightly more efficient than array multiplication especially on large ranges.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[paul_aramis]

thanks wfm007, this time worked perfect. and I learned something new. so * is the way to add more condition to a if function...

tony, the issue with using a certain range is that I never know how many rows the data source will have. I'm getting the source by exporting it from a management software (so actually is a different excel file, not on a different spreadsheet on the same file as in my example) by saving over the old file (export is made daily). the columns are always the same but row numbers can vary depending on how many orders are placed and how many sub-assemblies and components are used (either for the finish product or for sub-assemblies). it that bad to use an A:A instead of A2:Axxx? what is the risk?

[Tony Valko]

Then try to use a range size that is "reasonable" but that you know you will never exceed.

For example, if you know that you typically will have 200-400 rows of data and will never have more than 500 rows then set the range sizes to row 500.

An array formula calculates EVERY cell referenced. If you're using Excel 2007 or later and use entire columns as range references then the array formula is calculating 1,048,576 cells for each column being referenced.

If your data only extends down to row 500 then you're wasting valuable system resources by calculating all those empty cells.

Another method is to use dynamic ranges.

http://www.contextures.com/xlNames01.html#Dynamic

[paul_aramis]

thanks tony, that worked too. and I learned something new. so * is the way to add more condition to a if function...

[Tony Valko]

You're welcome!