Help with If formula

[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 D67<D16 and a couple of other things but cant get it to work.

 

[joeu2004]

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(D610,"<"&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 "<=".

 

[isabelle]

hi,

=IF(SUMPRODUCT(D610<D16)>0,"","over")


--
isabelle



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

 

[joeu2004]

=IF(COUNTIF(D610,"<"&D16)=0,"","over")

That should be:

=IF(COUNTIF(D610,">="&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 ">".

 

[joeu2004]

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(D610<D16)>0,"","over")

I think you mean:

=IF(SUMPRODUCT(--(D610>=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".

 

[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(D610)>D16,"over","")


Regards
Claus Busch

 

[ss]

Hi,

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


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


Regards
Claus Busch

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

Thank you all.