find the minimum value in a range or Fail text

Hi guys,


I have a range of cells which I need to find the lowest value of, pretty simple... however, in this range of cells the result can also be the word Fail.

I have tried a number of IF and MIN function combinations but just can't crack it..


=MINA(IF(AA87:AC92="Fail","Fail"),AA87:AC92)


As always, any help is greatly appreciated

Try

=MIN(AA87:AC92)

I assume you simply want the lowest numerical value ?

Originally Posted by JohnTopley

Try

=MIN(AA87:AC92)

I assume you simply want the lowest numerical value ?

Hi guys,


Thanks for the response... I need the lowest value as a result in cell K96 unless one or more of the of the cells in the range say "fail" then I need to result to be also fail - please see img Test1.PNG

Originally Posted by JohnTopley

Try

=MIN(AA87:AC92)

I assume you simply want the lowest numerical value ?

Hi guys,


Thanks for the response... I need the lowest value as a result in cell K96 unless one or more of the of the cells in the range say "fail" then I need to result to be also fail - please see img Attachment 501164

Very confusing: your original formula appeared to imply checking AA:AC for "fail" but your image suggests "Fail" is in AE.

Post a file (not image) showing expected results.

You could try

=MIN(--(AA87:AC92)*(IF(AE87:AE92<>"Fail",1,99999)))

Enter with Ctrl+Shift+Enter

Originally Posted by JohnTopley

Very confusing: your original formula appeared to imply checking AA:AC for "fail" but your image suggests "Fail" is in AE.

Post a file (not image) showing expected results.

You could try

=MIN(--(AA87:AC92)*(IF(AE87:AE92<>"Fail",1,99999)))

Enter with Ctrl+Shift+Enter

I have attached a duplicate workbook for you to have a play with...

Test workbook.xlsx

Don't merge cells !!!!

AS you unhelpfully did not provide any test results I can only guess at requirement: