Yes:
=IF(SUMPRODUCT((B1:D1<95)+(B1:D1>110))=3,"Y","N")
or array-enter (commit to the sheet with Ctrl+Shift+Enter) this:
=IF(SUM((B1:D1<95)+(B1:D1>110))=3,"Y","N")
--- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...> wrote:
>
> =if(and(or(b1<95,b1>110),or(c1<95,c1>110),or(d1<95,d1>110)),"Y","N")
>
> You could possibly also use an array formula for it, but this is
> straightforward (although non-intuitive).
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "Barry White" <imtigerwords@...>
> To: <ExcelVBA@yahoogroups.com>
> Sent: Friday, September 30, 2011 5:50 AM
> Subject: Re: [ExcelVBA] formula with IF, AND, and OR all in one
>
>
> Hello all,
>
> I have a row of numbers, say:
>
> B C D E F G H I
> 1 112 83 126 94 98 110 67 89
> 2 start formula in cell D2
>
> In each cell, for three consecutive cells, I wish to test two conditions, so
> that in cell D2, the start of my formula, I am testing each cell B1, C1, and
> D1 for the two conditions that follow:
>
> IF cell B1 is less than 95 OR greater than 110 AND
> cell C1 is also less than 95 OR greater than 110 AND
> cell D1 is also less than 95 OR greater than 110, put "Y" for YES or "N" for
> NO.
>
> So this same logical statement would apply as in E2, however, it would test
> for the two conditions mentioned above (<95 OR >110) on cells C1, D1, and
> E1. Cell F2 would test for the same conditions but on/for cells D1, E1, and
> F1, etc
>
> What is the syntax for this? I know the correct logic in terms of words, but
> I cannot get this syntax to work.
>
> Please help, and Thank you in advance for everyone's contribution.
>
> Eric Lutz
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar