How do I fix this formula?


G

Guest

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?
 
Ad

Advertisements

G

Guest

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.
 
T

Tom Ogilvy

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)
 
T

Tom Ogilvy

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)
 
Ad

Advertisements

G

Guest

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:K12>0)),"Please enter your remarks...","")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top