# Excel MIN with nested IF statement — Trying to ID a date closest to 30 days after a date

I have 16K plus records with an Auction Date. I need to find the date and amount that is closest to the Auction Date BUT more than 30 days later.

I have calculated a row that shows the difference between the Auction Date and each subsequent sale.

My approach was to us MIN to find the smallest difference that is more than 30 days after the Auction Date. This is the formula I used:

``````=MIN((IF((B12-A12)>30,(B12-A12),0)),IF((E12-A12)>30,(E12-A12),0),IF((H12-A12)>30,((H12-A12)),0))
``````

It returns the correct result only once in the sample data. (see image - YELLOW = AUCTION DATE, GREEN = CORRECT INFORMATION, RED=INCORRECT) Next I need to eliminate from the results any sale of less than \$1,000. But I need to get his part of the formula right first.

• You use this array Formula to find the closest date to Auction Date (Auction date+30), `{=MIN(IF(A2:A15>A\$1+30,A2:A15))}`. Here `A1` has `Auction Date`. Then select the required range & apply conditional format by using `Cond. Formt.+Highlight Cell Rule+Equals to` & select the `Formula cell`. – Rajesh S Aug 18 '18 at 5:05

## 1 Answer

While I'm not sure about how this information applies to your description, the formula is not working because your `IF` statements are providing a minimum value of `0` every time it resolves to false. This modification would work:

`=MIN((IF((B12-A12)>30,(B12-A12),1000000)),IF((E12-A12)>30,(E12-A12),1000000),IF((H12-A12)>30,((H12-A12)),1000000))`

I substituted `1000000` for `0`, but any number will work as long as it is larger than what you would expect.

NOTE: This will return `1000000` if no dates meet your criteria.

I kept the formula the same so that you can see the change, but since you are already calculating the differences in columns C, F, and I, in other words `C12=B12-A12` etc, it could be simplified as:

`=MIN((IF(C12>30,C12,100000000)),IF(F12>30,F12,1000000),IF(I12>30,I12,1000000))`

And there are other simplifications that could be done, but this should get you going.

• BRILLIANT! You should know that a dozen people looked at this, all experienced Excel types and no one saw the obvious -- very nice catch. Thanks you. – Jim McAvoy Aug 18 '18 at 17:08