Help with MIN function

[monkeydan]

Hi,

I have a spreadsheet I am using to track expiration dates of employee qualifications.

We are tracking the earliest expiration date of all the qualifications, however some expiration dates only need to be returned if another qualification is held.

There is an existing formula using MIN which worked fine:

=IF(M8="",MIN(T8:AA8),MIN(O8:AS8))

If a qualification is not held in Column M, earliest date is returned from the range T:AA

If a qualification IS held in Column M, earliest date is returned from the range O:AS


I have added an additional qualification which is complicating things. I've tried a couple of formulas and I can no longer get the date I want to be returned.

One formula returns a #VALUE error, the other returns an unwanted expiration date...

If Column M is blank and qualification in Column AN is not required, return earliest expiration date from range T:AA and Column AP
If Column M is blank and qualification in Column AN is required, return earliest expiration date from range T:AA, Column AP and Column AS
If Column M is NOT blank (e.g. qualification held) and qualification in Column AN is NOT required, return earliest expiration date from range O:AP
If Column M is NOT blank (e.g. qualification held) and qualification in Column AN is required, return earliest expiration date from all qualifications (range O:AS)


Here are the two formulas I have tried...

=IF(AND(M10="",AQ10="<>REQUIRED"),MIN(T10:AA10,AP10),
IF(AND(M10="",AQ10="REQUIRED"),MIN(T10:AA10,AP10,AS10),
IF(AND(M10="<>",AQ10="<>REQUIRED"),MIN(O10:AP10),
MIN(O10:AS10))))

This returns a date from the range AB:AP even if Column M is blank and Column AQ does not equal REQUIRED. It should only return a date from the range T:AA.


The other formula is similar but returns a #VALUE error and I have no idea why!!

=IF(AND(M10="",AQ10="<>REQUIRED"),MIN(T10:AA10,AP10)),
IF(AND(M10="",AQ10="REQUIRED"),MIN(T10:AA10,AP10,AS10)),
IF(AND(M10="<>",AQ10="<>REQUIRED"),MIN(O10:AP10)),
IF(AND(M10="<>",AQ10="REQUIRED"),MIN(O10:AS10)),


Hope this makes sense...

Thanks for any assistance you could provide.

 

[RoryA]

Rather than:

AQ10="<>REQUIRED"

you need:

AQ10<>"REQUIRED"

 

[monkeydan]

That'll do it... thanks very much!


That fixed the first formula, so I don't need the second any more. However I would still really like to know why it's returning an error, as it's bugging me and I like to know what I'm doing wrong... any ideas without seeing the data itself?

I cannot figure out why there is an error, as the second formula is almost exactly the same as the first formula, I am just removing the [value_if_false] argument (as far as I know!)

=IF(AND(M10="",AQ10<="REQUIRED"),MIN(T10:AA10,AP10)),
IF(AND(M10="",AQ10="REQUIRED"),MIN(T10:AA10,AP10,AS10)),
IF(AND(M10<>"",AQ10<>"REQUIRED"),MIN(O10:AP10)),
IF(AND(M10<>"",AQ10="REQUIRED"),MIN(O10:AS10))

 

[RoryA]

You have closing brackets in the wrong place, which means that your formula is actually 4 separate IF formulas with commas in between. It should be:

=IF(AND(M10="",AQ10<="REQUIRED"),MIN(T10:AA10,AP10),
IF(AND(M10="",AQ10="REQUIRED"),MIN(T10:AA10,AP10,AS10),
IF(AND(M10<>"",AQ10<>"REQUIRED"),MIN(O10:AP10),
IF(AND(M10<>"",AQ10="REQUIRED"),MIN(O10:AS10)))))

Or shorter:

=IF(M10="",IF(AQ10<>"REQUIRED",MIN(T10:AA10,AP10),
MIN(T10:AA10,AP10,AS10)),IF(AQ10<>"REQUIRED",MIN(O10:AP10),
MIN(O10:AS10)))

 

[monkeydan]

Thanks again... much appreciated!