Getting formula to ignore blank cells

I have the following formula:
=IF(MIN(C5-D4,1-D4+G4,1-D4+G5,1-D5+G4,1-D5+G5,G5-H4)<0.5,"N","Y")

The problem is that if any of the referenced cells are blank, they are still included when the minimum is worked out, and I end up with the wrong result e.g. if D4 is blank, then the first calculation C5-D4 gives a negative number, and this could be the MIN value.

I either need something adding to the formula to ignore any calculations that include a blank cell, or some way to exclude any negative results for each calculated value.

Thanks for your help.

Catherine

Try:

=IF(MIN(Max(0,C5-D4),Max(0,1-D4+G4),Max(0,1-D4+G5),Max(0,1-D5+G4),Max(0,1-D5+G5),Max(0,G5-H4))<0.5,"N","Y")


Mangesh

Mangesh

Thanks. That looks good, but I've just discovered that not all of the calculations give a negative value when one of the cells is blank, so I think I really need a formula to ignore blank cell, rather than negative values.

Regards

Catherine

The formula I gave earlier will always return you a positive value or a 0, but never a negative. So even if you don't get a negative result, the above formula will work.

But if you want to ignore the blank cells, then

=IF(MIN(if(AND(C5<>"",D4<>""),C5-D4,""),if(AND(G4<>"",D4<>""),1-D4+G4,""),if(AND(G5<>"",D4<>""),1-D4+G5,""),if(AND(G4<>"",D5<>""),1-D5+G4,""),if(AND(G5<>"",D5<>""),1-D5+G5,""),if(AND(G5<>"",H4<>""),G5-H4,""))<0.5,"N","Y")


Mangesh

Don't worry - I've sorted it. The formula is:

=IF(MIN(IF(ISBLANK(C5),10,IF(ISBLANK(D4),10,C5-D4)),IF(ISBLANK(D4),10,IF(ISBLANK(G4),10,1-D4+G4)),IF(ISBLANK(D4),10,IF(ISBLANK(G5),10,1-D4+G5)),IF(ISBLANK(D5),10,IF(ISBLANK(G4),10,1-D5+G4)),IF(ISBLANK(D5),10,IF(ISBLANK(G5),10,1-D5+G5)),IF(ISBLANK(G5),10,IF(ISBLANK(H4),10,G5-H4)))<0.5,"N","Y")

Regards

Catherine