Formula problem

[Tony Miall]

Morning all,

In a worksheet called MICROMETER I have a range I3:I40 in which each cell of that range has a result of either "OK" or "OVERDUE"

In another worksheet (called FRONT PAGE) I want cell B3 to show the result "OK" only if all of the cells I3:I40 in MICROMETER = OK, if one of the cells in the range does not = OK I want cell B3 in FRONT PAGE to = OVERDUE.

I did this with the following formula :

=IF(MICROMETER!$I$3:$I$40="OK", "OK", IF(NOT(MICROMETER!I3:$I$40="OK"),"OVERDUE",""))

this seems to work.

However when I apply the same formula

=IF(VERNIER!$I$3:$I$40="OK", "OK", IF(NOT(VERNIER!I4:$I$40="OK"),"OVERDUE",""))

in cell B4 in FRONT PAGE to look at another worksheet called VERNIER it doesn't work and I can't understand why??

Any help much appreciated as usual.

 

[Tony Miall]

=if(countif(vernier!$i$3:$i$40,"ok") < counta(vernier!$i$3:$i$40),"overdue","ok")

 

[Marcelo Branco]

Maybe i'm missing something, but if you need ALL the values OK in the range, why not the simpler formula?

=if(countif(vernier!$i$3:$i$40,"ok")=38, "ok", "overdue")

HTH

M.

 

[Tony Miall]

I'm doing this in 2010 for a friend and he's using 2003. For some reason the first formula I posted and the one Valco posted worked for me and not him. Apologies, I should have been clearer in the beginning

 

[Marcelo Branco]

Strange...

The formula Valko posted in #4 seems perfect to me - in 2003, 2007, or whatever version...

M.

 

[T. Valko]

Strange...

The formula Valko posted in #4 seems perfect to me - in 2003, 2007, or whatever version...

M.

It seems pretty good to me, too!

Here's my understanding of what was wanted...

The range of cells will contain ONLY "OK" or "Overdue". No empty cells.

If EVERY cell in the range contains OK, return OK otherwise return Overdue.

So, my thinking is that it would be easier to test the range to see if it contains ANY count of Overdue rather than to test to see if EVERY cell contains OK.

Consider this example:

Book1
	A
1	OK
2	OK
3	OK
4	Overdue
5	OK
6	OK
7	OK
8	OK
9	OK
10	OK
Sheet1

There are 10 cells in the range.

You could do this...

=IF(COUNTIF(A1:A10,"ok")=ROWS(A1:A10),"OK","Overdue")

Or, you could do this...

=IF(COUNTIF(A1:A10,"overdue"),"Overdue","OK")

I chose the latter.

You could also do this array formula...

=IF(AND(A1:A10="ok"),"OK","Overdue")

...but I didn't want to "over complicate" a simple task.

 

[Marcelo Branco]

Valko,

Your reasoning is perfect! I suggested the alternative formula
=if(countif(vernier!$i$3:$i$40,"ok")=38, "ok", "overdue")

to prevent that a "smart guy" tries copy + paste a different value in the range breaking the validation-rule.

Just in case....

M.

 

[Tony Miall]

thank you both!

 

[T. Valko]

thank you both!

You're welcome!

 

[Marcelo Branco]

You're welcome!

Same here

M.