Help with If formula

S

ss

This is my formula which works ok.
=IF(D6<D16,"","over")

What I am trying to do is extend this and in the same formula have
D6,D7,D8,D9,D10 included,ie if any are greater than D16 then "over"
but I get to many arguements.

I have tried D6:D7<D16 and a couple of other things but cant get it to work.
 
J

joeu2004

ss said:
This is my formula which works ok.
=IF(D6<D16,"","over")
What I am trying to do is extend this and in the same
formula have D6,D7,D8,D9,D10 included,ie if any are
greater than D16 then "over"

=IF(COUNTIF(D6:D10,"<"&D16)=0,"","over")

You said "greater than" D16, but your original formula return "over" for
"greater than or equal".

If you truly mean just "greater than", change "<" to "<=".
 
I

isabelle

hi,

=IF(SUMPRODUCT(D6:D10<D16)>0,"","over")


--
isabelle



Le 2012-02-01 19:21, ss a écrit :
 
J

joeu2004

Errata.... I said:
=IF(COUNTIF(D6:D10,"<"&D16)=0,"","over")

That should be:

=IF(COUNTIF(D6:D10,">="&D16)>0,"over","")
You said "greater than" D16, but your original formula
return "over" for "greater than or equal".
If you truly mean just "greater than", change "<" to "<=".

That should be: change ">=" to ">".
 
J

joeu2004

isabelle said:
Le 2012-02-01 19:21, ss a écrit :
This is my formula which works ok.
=IF(D6<D16,"","over") [....]
if any are greater than D16 then "over"
but I get to many arguements.

=IF(SUMPRODUCT(D6:D10<D16)>0,"","over")

I think you mean:

=IF(SUMPRODUCT(--(D6:D10>=D16))>0,"over","")

The double-negative is needed to change the logic values TRUE and FALSE to 1
and 0. Otherwise, SUMPRODUCT always returns zero; so your original formula
always returns "over".

With respect to ">=D16" v. ">D16", see my comment about that. The point is:
ss's requirements are ambiguous because his original formula returns "over"
for "greater than or equal", not "greater than".
 
C

Claus Busch

Hi,

Am Thu, 02 Feb 2012 00:21:33 +0000 schrieb ss:
What I am trying to do is extend this and in the same formula have
D6,D7,D8,D9,D10 included,ie if any are greater than D16 then "over"
but I get to many arguements.

try:
=IF(MAX(D6:D10)>D16,"over","")


Regards
Claus Busch
 
S

ss

Hi,

Am Thu, 02 Feb 2012 00:21:33 +0000 schrieb ss:


try:
=IF(MAX(D6:D10)>D16,"over","")


Regards
Claus Busch

Thanks for all the help, I managed to get the result I wanted using ...
=IF(COUNTIF(D6:D10,">="&D16)>0,"over","")

Thank you all.
 

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