1

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)

Auction Dates MIN IF

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.

1
  • 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
0

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.

1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.