How do I fix this formula?
 

How do I fix this formula
"=IF(OR(B12<=3,C12<=3,D12<=3,E12<=3,F12<=3,G12<=3, H12<=3,I12<=3,J12<=3,K12<=3),"Please
enter your remarks for 3's and less","")" so if the cell is either blank or
"0", then neither of the remarks will appear?

How do I fix this formula?
 

You could do it by adding a couple ANDs along with more ORs but YIKES!
Use an array formula (i.e. use shift-ctrl-enter after you type it):
=IF(SUM((B12:K12<=3)+(B12:K12=0)+ISBLANK(B12:K12)) 0,"Please enter your
remarks...","")
Explanation:
This makes three arrays for your 3 conditions: any one that is true gives a
1 in that position (otherwise 0, numeric equivalents of true/false). Add
them together element by element to combine the results and if none are true
you have all zeros - anything else means at least one condition violated.
Now add the elements together and get the overall result: again, if any
condition was not true you will get something other than zero.
--
- K Dales


"Darren" wrote:

How do I fix this formula
"=IF(OR(B12<=3,C12<=3,D12<=3,E12<=3,F12<=3,G12<=3, H12<=3,I12<=3,J12<=3,K12<=3),"Please
enter your remarks for 3's and less","")" so if the cell is either blank or
"0", then neither of the remarks will appear?

How do I fix this formula?
 

So if which cell is either blank or zero. You are working with 10 cells.

If the cells will either have a number (including zero) or have a blank,
then
Possibly if you mean none of the cells have anything other than blanks or
zero, then

=if(Or(Count(B12:K12)=0,Sum(B12:K12)=0),"",current formula)



--
Regards,
Tom Ogilvy

"Darren" wrote in message
...
How do I fix this formula

"=IF(OR(B12<=3,C12<=3,D12<=3,E12<=3,F12<=3,G12<=3, H12<=3,I12<=3,J12<=3,K12<=
3),"Please
enter your remarks for 3's and less","")" so if the cell is either blank
or
"0", then neither of the remarks will appear?

How do I fix this formula?
 

Another possibility is that you don't want to show a message unless all
cells contain a value greater than 0 and are not blank

=If(countif(B12:K12,"0")<10,"",current formula)

--
Regards,
Tom Ogilvy

"Darren" wrote in message
...
How do I fix this formula

"=IF(OR(B12<=3,C12<=3,D12<=3,E12<=3,F12<=3,G12<=3, H12<=3,I12<=3,J12<=3,K12<=
3),"Please
enter your remarks for 3's and less","")" so if the cell is either blank
or
"0", then neither of the remarks will appear?

How do I fix this formula?
 

Re the previous post: I believe the following works - the "*" is an AND
Condition [whereas the + is an OR condition]. Blanks are treated as zeros:

Enter as an array formula

=IF(SUM((B12:K12<=3)*(B12:K120)),"Please enter your remarks...","")

"K Dales" wrote:

You could do it by adding a couple ANDs along with more ORs but YIKES!
Use an array formula (i.e. use shift-ctrl-enter after you type it):
=IF(SUM((B12:K12<=3)+(B12:K12=0)+ISBLANK(B12:K12)) 0,"Please enter your
remarks...","")
Explanation:
This makes three arrays for your 3 conditions: any one that is true gives a
1 in that position (otherwise 0, numeric equivalents of true/false). Add
them together element by element to combine the results and if none are true
you have all zeros - anything else means at least one condition violated.
Now add the elements together and get the overall result: again, if any
condition was not true you will get something other than zero.
--
- K Dales


"Darren" wrote:

How do I fix this formula
"=IF(OR(B12<=3,C12<=3,D12<=3,E12<=3,F12<=3,G12<=3, H12<=3,I12<=3,J12<=3,K12<=3),"Please
enter your remarks for 3's and less","")" so if the cell is either blank or
"0", then neither of the remarks will appear?