IF Statement Question

bj:
I need help with a logical staement and you seem to be an expert. I am
trying to calculate a forecast accuracy. I need to do more than one piece of
logic with in the formula.
If we focus on 2 columns, i have column A that is a forecast number and
column b that is an actual sales number. I have covered the case of if
column A or B is equal to zero then enter a zero % accuarcy, but I also need
to cover if both column A and B are equal to zero then 100% accuracy. Can
you help??

"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

=IF(H144=FALSE,MIN(1,ABS(1-F144/D144)),IF(C144=0,"no value",H144))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:1140CCCE-0927-428A-86E8-A8C3E53CFDB2@microsoft.com...
> When I add the MIN(1,...) to my calculation it doens't return a value.
Here
> is the formula I am using and I wish to keep the value returned at less
than
> 100%.
>
> =IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
> works correctly for everything I need except if F144 is greater than D144.
>
>
> "Bob Phillips" wrote:
>
> > Simply stated, you need to force a maximum value of 1, so you take the
> > minimum of 1 (100%) or your calculation
> >
> > =IF(H5=??,MIN(1,ABS(1-F5/d5)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > > Ok, there is one final problem that I need to get resolved. When
> > calculating
> > > the percentage accuarcy how do you keep the absolute vallue at no
greater
> > > than 100%.
> > > Example: my current calculation, is created when there are numbers so
> > from
> > > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > > Where/how do I amend the formula so that it iehter take the
ABS(1-F5/d5)
> > or
> > > if the result is greater than 100%, then it returns only 100%.
> > >
> > > Jean
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > > >
> > > > and format as a percentage.
> > > >
> > > > You don't say what to do if neither are 0, so I just added "??"
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > > bj:
> > > > > I need help with a logical staement and you seem to be an expert.
I
> > am
> > > > > trying to calculate a forecast accuracy. I need to do more than
one
> > piece
> > > > of
> > > > > logic with in the formula.
> > > > > If we focus on 2 columns, i have column A that is a forecast
number
> > and
> > > > > column b that is an actual sales number. I have covered the case
of
> > if
> > > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> > also
> > > > need
> > > > > to cover if both column A and B are equal to zero then 100%
accuracy.
> > Can
> > > > > you help??
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try
> > > > > >
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > > >
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > Hey bj,
> > > > > > >
> > > > > > > What I actually need to do is check for both the name and an X
in
> > a
> > > > > > > particular column. The worksheet I am working with has a list
of
> > > > names, and
> > > > > > > four columns that indicate which week a person is attending an
> > event.
> > > > I have
> > > > > > > a summary sheet I am preparing that I want to look-up
instances of
> > the
> > > > > > > person's name and which week they are involved. So I have to
> > check
> > > > for both
> > > > > > > cases, appearance of the name and an x in the week one column
to
> > > > return an
> > > > > > > "OK" in the summary sheet.
> > > > > > >
> > > > > > > I know this is confusing. If I can email you an example let
me
> > know.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > >
> > > > > > > Scott
> > > > > > >
> > > > > > >
> > > > > > > "bj" wrote:
> > > > > > >
> > > > > > > > try If you want to enter the name in Cell C1 and have D1
give
> > the
> > > > result
> > > > > > > > in D1 enter a
> > > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > > and in C1 enter the name
> > > > > > > > "Scott" wrote:
> > > > > > > >
> > > > > > > > > This might be hard to explain...
> > > > > > > > >
> > > > > > > > > I want to check a sheet for a persons name AND if an X is
> > entered
> > > > in an
> > > > > > > > > associated cell. In literal terms the IF statement would
be
> > as
> > > > follows:
> > > > > > > > >
> > > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X
in
> > field
> > > > B1, THEN
> > > > > > > > > "OK")
> > > > > > > > >
> > > > > > > > > The X would appear in the same row as the name in the
range,
> > for
> > > > example:
> > > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > > >
> > > > > > > > > How would I make this work?
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > >
> > > > > > > > > Scott
> > > >
> > > >
> > > >
> >
> >
> >

When I add the MIN(1,...) to my calculation it doens't return a value. Here
is the formula I am using and I wish to keep the value returned at less than
100%.

=IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
works correctly for everything I need except if F144 is greater than D144.


"Bob Phillips" wrote:

> Simply stated, you need to force a maximum value of 1, so you take the
> minimum of 1 (100%) or your calculation
>
> =IF(H5=??,MIN(1,ABS(1-F5/d5)))
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > Ok, there is one final problem that I need to get resolved. When
> calculating
> > the percentage accuarcy how do you keep the absolute vallue at no greater
> > than 100%.
> > Example: my current calculation, is created when there are numbers so
> from
> > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
> or
> > if the result is greater than 100%, then it returns only 100%.
> >
> > Jean
> >
> > "Bob Phillips" wrote:
> >
> > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > >
> > > and format as a percentage.
> > >
> > > You don't say what to do if neither are 0, so I just added "??"
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > bj:
> > > > I need help with a logical staement and you seem to be an expert. I
> am
> > > > trying to calculate a forecast accuracy. I need to do more than one
> piece
> > > of
> > > > logic with in the formula.
> > > > If we focus on 2 columns, i have column A that is a forecast number
> and
> > > > column b that is an actual sales number. I have covered the case of
> if
> > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> also
> > > need
> > > > to cover if both column A and B are equal to zero then 100% accuracy.
> Can
> > > > you help??
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try
> > > > >
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > >
> > > > > "Scott" wrote:
> > > > >
> > > > > > Hey bj,
> > > > > >
> > > > > > What I actually need to do is check for both the name and an X in
> a
> > > > > > particular column. The worksheet I am working with has a list of
> > > names, and
> > > > > > four columns that indicate which week a person is attending an
> event.
> > > I have
> > > > > > a summary sheet I am preparing that I want to look-up instances of
> the
> > > > > > person's name and which week they are involved. So I have to
> check
> > > for both
> > > > > > cases, appearance of the name and an x in the week one column to
> > > return an
> > > > > > "OK" in the summary sheet.
> > > > > >
> > > > > > I know this is confusing. If I can email you an example let me
> know.
> > > > > >
> > > > > > Thanks in advance.
> > > > > >
> > > > > > Scott
> > > > > >
> > > > > >
> > > > > > "bj" wrote:
> > > > > >
> > > > > > > try If you want to enter the name in Cell C1 and have D1 give
> the
> > > result
> > > > > > > in D1 enter a
> > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > and in C1 enter the name
> > > > > > > "Scott" wrote:
> > > > > > >
> > > > > > > > This might be hard to explain...
> > > > > > > >
> > > > > > > > I want to check a sheet for a persons name AND if an X is
> entered
> > > in an
> > > > > > > > associated cell. In literal terms the IF statement would be
> as
> > > follows:
> > > > > > > >
> > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
> field
> > > B1, THEN
> > > > > > > > "OK")
> > > > > > > >
> > > > > > > > The X would appear in the same row as the name in the range,
> for
> > > example:
> > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > >
> > > > > > > > How would I make this work?
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > >
> > > > > > > > Scott
> > >
> > >
> > >
>
>
>

Simply stated, you need to force a maximum value of 1, so you take the
minimum of 1 (100%) or your calculation

=IF(H5=??,MIN(1,ABS(1-F5/d5)))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> Ok, there is one final problem that I need to get resolved. When
calculating
> the percentage accuarcy how do you keep the absolute vallue at no greater
> than 100%.
> Example: my current calculation, is created when there are numbers so
from
> below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
or
> if the result is greater than 100%, then it returns only 100%.
>
> Jean
>
> "Bob Phillips" wrote:
>
> > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> >
> > and format as a percentage.
> >
> > You don't say what to do if neither are 0, so I just added "??"
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > bj:
> > > I need help with a logical staement and you seem to be an expert. I
am
> > > trying to calculate a forecast accuracy. I need to do more than one
piece
> > of
> > > logic with in the formula.
> > > If we focus on 2 columns, i have column A that is a forecast number
and
> > > column b that is an actual sales number. I have covered the case of
if
> > > column A or B is equal to zero then enter a zero % accuarcy, but I
also
> > need
> > > to cover if both column A and B are equal to zero then 100% accuracy.
Can
> > > you help??
> > >
> > > "bj" wrote:
> > >
> > > > try
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > ="X")--(E1:E1000))>0, "ok","nope")
> > > >
> > > > "Scott" wrote:
> > > >
> > > > > Hey bj,
> > > > >
> > > > > What I actually need to do is check for both the name and an X in
a
> > > > > particular column. The worksheet I am working with has a list of
> > names, and
> > > > > four columns that indicate which week a person is attending an
event.
> > I have
> > > > > a summary sheet I am preparing that I want to look-up instances of
the
> > > > > person's name and which week they are involved. So I have to
check
> > for both
> > > > > cases, appearance of the name and an x in the week one column to
> > return an
> > > > > "OK" in the summary sheet.
> > > > >
> > > > > I know this is confusing. If I can email you an example let me
know.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Scott
> > > > >
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try If you want to enter the name in Cell C1 and have D1 give
the
> > result
> > > > > > in D1 enter a
> > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > and in C1 enter the name
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > This might be hard to explain...
> > > > > > >
> > > > > > > I want to check a sheet for a persons name AND if an X is
entered
> > in an
> > > > > > > associated cell. In literal terms the IF statement would be
as
> > follows:
> > > > > > >
> > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
field
> > B1, THEN
> > > > > > > "OK")
> > > > > > >
> > > > > > > The X would appear in the same row as the name in the range,
for
> > example:
> > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > >
> > > > > > > How would I make this work?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > Scott
> >
> >
> >

Ok, there is one final problem that I need to get resolved. When calculating
the percentage accuarcy how do you keep the absolute vallue at no greater
than 100%.
Example: my current calculation, is created when there are numbers so from
below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5) or
if the result is greater than 100%, then it returns only 100%.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

Bob,
Thank you. I am thrilled that there is an expert site like this. Make me
appear a bit more knowledgable in my job....my secret!! Thank you so very
much.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

=IF(ISNUMBER(MATCH("John Doe",A1:A99,0)),IF(INDEX(B1:B99,MATCH("John
Doe",A1:A99,0))="X","Match","No match"))

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6722916F-072B-4F09-BB45-6292F4C6E9BE@microsoft.com...
> Bob,
>
> This is SO close... the last bit might prove to be the most difficult
part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the
range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in
an
> > > associated cell. In literal terms the IF statement would be as
follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for
example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

=IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))

and format as a percentage.

You don't say what to do if neither are 0, so I just added "??"

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> bj:
> I need help with a logical staement and you seem to be an expert. I am
> trying to calculate a forecast accuracy. I need to do more than one piece
of
> logic with in the formula.
> If we focus on 2 columns, i have column A that is a forecast number and
> column b that is an actual sales number. I have covered the case of if
> column A or B is equal to zero then enter a zero % accuarcy, but I also
need
> to cover if both column A and B are equal to zero then 100% accuracy. Can
> you help??
>
> "bj" wrote:
>
> > try
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
="X")--(E1:E1000))>0, "ok","nope")
> >
> > "Scott" wrote:
> >
> > > Hey bj,
> > >
> > > What I actually need to do is check for both the name and an X in a
> > > particular column. The worksheet I am working with has a list of
names, and
> > > four columns that indicate which week a person is attending an event.
I have
> > > a summary sheet I am preparing that I want to look-up instances of the
> > > person's name and which week they are involved. So I have to check
for both
> > > cases, appearance of the name and an x in the week one column to
return an
> > > "OK" in the summary sheet.
> > >
> > > I know this is confusing. If I can email you an example let me know.
> > >
> > > Thanks in advance.
> > >
> > > Scott
> > >
> > >
> > > "bj" wrote:
> > >
> > > > try If you want to enter the name in Cell C1 and have D1 give the
result
> > > > in D1 enter a
> > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > and in C1 enter the name
> > > > "Scott" wrote:
> > > >
> > > > > This might be hard to explain...
> > > > >
> > > > > I want to check a sheet for a persons name AND if an X is entered
in an
> > > > > associated cell. In literal terms the IF statement would be as
follows:
> > > > >
> > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
B1, THEN
> > > > > "OK")
> > > > >
> > > > > The X would appear in the same row as the name in the range, for
example:
> > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > >
> > > > > How would I make this work?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Scott

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!

AWESOME!!!!!!!!!!!



"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!!!!!

AWESOME!!!!!!!



"Scott" wrote:

> Bob,
>
> This is SO close... the last bit might prove to be the most difficult part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

Bob,

This is SO close... the last bit might prove to be the most difficult part.

That "B1" cell is actually going to be the cell in the same row that the
name is found in column B. My check is IF "John Doe" is found in the range
AND "X" is in the corresponding cell in column B THEN "Match", "No Match".

(fingers crossed you know the answer)

Thanks so much!

Scott



"Bob Phillips" wrote:

> =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
>
> --
> HTH
>
> Bob Phillips
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message
> news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott
>
>
>

try If you want to enter the name in Cell C1 and have D1 give the result
in D1 enter a
=if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
and in C1 enter the name
"Scott" wrote:

> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

=IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

Hey bj,

What I actually need to do is check for both the name and an X in a
particular column. The worksheet I am working with has a list of names, and
four columns that indicate which week a person is attending an event. I have
a summary sheet I am preparing that I want to look-up instances of the
person's name and which week they are involved. So I have to check for both
cases, appearance of the name and an x in the week one column to return an
"OK" in the summary sheet.

I know this is confusing. If I can email you an example let me know.

Thanks in advance.

Scott


"bj" wrote:

> try If you want to enter the name in Cell C1 and have D1 give the result
> in D1 enter a
> =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> and in C1 enter the name
> "Scott" wrote:
>
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott

try
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")

"Scott" wrote:

> Hey bj,
>
> What I actually need to do is check for both the name and an X in a
> particular column. The worksheet I am working with has a list of names, and
> four columns that indicate which week a person is attending an event. I have
> a summary sheet I am preparing that I want to look-up instances of the
> person's name and which week they are involved. So I have to check for both
> cases, appearance of the name and an x in the week one column to return an
> "OK" in the summary sheet.
>
> I know this is confusing. If I can email you an example let me know.
>
> Thanks in advance.
>
> Scott
>
>
> "bj" wrote:
>
> > try If you want to enter the name in Cell C1 and have D1 give the result
> > in D1 enter a
> > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > and in C1 enter the name
> > "Scott" wrote:
> >
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott

When I add the MIN(1,...) to my calculation it doens't return a value. Here
is the formula I am using and I wish to keep the value returned at less than
100%.

=IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
works correctly for everything I need except if F144 is greater than D144.


"Bob Phillips" wrote:

> Simply stated, you need to force a maximum value of 1, so you take the
> minimum of 1 (100%) or your calculation
>
> =IF(H5=??,MIN(1,ABS(1-F5/d5)))
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > Ok, there is one final problem that I need to get resolved. When
> calculating
> > the percentage accuarcy how do you keep the absolute vallue at no greater
> > than 100%.
> > Example: my current calculation, is created when there are numbers so
> from
> > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
> or
> > if the result is greater than 100%, then it returns only 100%.
> >
> > Jean
> >
> > "Bob Phillips" wrote:
> >
> > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > >
> > > and format as a percentage.
> > >
> > > You don't say what to do if neither are 0, so I just added "??"
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > bj:
> > > > I need help with a logical staement and you seem to be an expert. I
> am
> > > > trying to calculate a forecast accuracy. I need to do more than one
> piece
> > > of
> > > > logic with in the formula.
> > > > If we focus on 2 columns, i have column A that is a forecast number
> and
> > > > column b that is an actual sales number. I have covered the case of
> if
> > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> also
> > > need
> > > > to cover if both column A and B are equal to zero then 100% accuracy.
> Can
> > > > you help??
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try
> > > > >
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > >
> > > > > "Scott" wrote:
> > > > >
> > > > > > Hey bj,
> > > > > >
> > > > > > What I actually need to do is check for both the name and an X in
> a
> > > > > > particular column. The worksheet I am working with has a list of
> > > names, and
> > > > > > four columns that indicate which week a person is attending an
> event.
> > > I have
> > > > > > a summary sheet I am preparing that I want to look-up instances of
> the
> > > > > > person's name and which week they are involved. So I have to
> check
> > > for both
> > > > > > cases, appearance of the name and an x in the week one column to
> > > return an
> > > > > > "OK" in the summary sheet.
> > > > > >
> > > > > > I know this is confusing. If I can email you an example let me
> know.
> > > > > >
> > > > > > Thanks in advance.
> > > > > >
> > > > > > Scott
> > > > > >
> > > > > >
> > > > > > "bj" wrote:
> > > > > >
> > > > > > > try If you want to enter the name in Cell C1 and have D1 give
> the
> > > result
> > > > > > > in D1 enter a
> > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > and in C1 enter the name
> > > > > > > "Scott" wrote:
> > > > > > >
> > > > > > > > This might be hard to explain...
> > > > > > > >
> > > > > > > > I want to check a sheet for a persons name AND if an X is
> entered
> > > in an
> > > > > > > > associated cell. In literal terms the IF statement would be
> as
> > > follows:
> > > > > > > >
> > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
> field
> > > B1, THEN
> > > > > > > > "OK")
> > > > > > > >
> > > > > > > > The X would appear in the same row as the name in the range,
> for
> > > example:
> > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > >
> > > > > > > > How would I make this work?
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > >
> > > > > > > > Scott
> > >
> > >
> > >
>
>
>

=IF(ISNUMBER(MATCH("John Doe",A1:A99,0)),IF(INDEX(B1:B99,MATCH("John
Doe",A1:A99,0))="X","Match","No match"))

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6722916F-072B-4F09-BB45-6292F4C6E9BE@microsoft.com...
> Bob,
>
> This is SO close... the last bit might prove to be the most difficult
part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the
range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in
an
> > > associated cell. In literal terms the IF statement would be as
follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for
example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

try If you want to enter the name in Cell C1 and have D1 give the result
in D1 enter a
=if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
and in C1 enter the name
"Scott" wrote:

> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

Simply stated, you need to force a maximum value of 1, so you take the
minimum of 1 (100%) or your calculation

=IF(H5=??,MIN(1,ABS(1-F5/d5)))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> Ok, there is one final problem that I need to get resolved. When
calculating
> the percentage accuarcy how do you keep the absolute vallue at no greater
> than 100%.
> Example: my current calculation, is created when there are numbers so
from
> below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
or
> if the result is greater than 100%, then it returns only 100%.
>
> Jean
>
> "Bob Phillips" wrote:
>
> > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> >
> > and format as a percentage.
> >
> > You don't say what to do if neither are 0, so I just added "??"
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > bj:
> > > I need help with a logical staement and you seem to be an expert. I
am
> > > trying to calculate a forecast accuracy. I need to do more than one
piece
> > of
> > > logic with in the formula.
> > > If we focus on 2 columns, i have column A that is a forecast number
and
> > > column b that is an actual sales number. I have covered the case of
if
> > > column A or B is equal to zero then enter a zero % accuarcy, but I
also
> > need
> > > to cover if both column A and B are equal to zero then 100% accuracy.
Can
> > > you help??
> > >
> > > "bj" wrote:
> > >
> > > > try
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > ="X")--(E1:E1000))>0, "ok","nope")
> > > >
> > > > "Scott" wrote:
> > > >
> > > > > Hey bj,
> > > > >
> > > > > What I actually need to do is check for both the name and an X in
a
> > > > > particular column. The worksheet I am working with has a list of
> > names, and
> > > > > four columns that indicate which week a person is attending an
event.
> > I have
> > > > > a summary sheet I am preparing that I want to look-up instances of
the
> > > > > person's name and which week they are involved. So I have to
check
> > for both
> > > > > cases, appearance of the name and an x in the week one column to
> > return an
> > > > > "OK" in the summary sheet.
> > > > >
> > > > > I know this is confusing. If I can email you an example let me
know.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Scott
> > > > >
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try If you want to enter the name in Cell C1 and have D1 give
the
> > result
> > > > > > in D1 enter a
> > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > and in C1 enter the name
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > This might be hard to explain...
> > > > > > >
> > > > > > > I want to check a sheet for a persons name AND if an X is
entered
> > in an
> > > > > > > associated cell. In literal terms the IF statement would be
as
> > follows:
> > > > > > >
> > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
field
> > B1, THEN
> > > > > > > "OK")
> > > > > > >
> > > > > > > The X would appear in the same row as the name in the range,
for
> > example:
> > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > >
> > > > > > > How would I make this work?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > Scott
> >
> >
> >

Hey bj,

What I actually need to do is check for both the name and an X in a
particular column. The worksheet I am working with has a list of names, and
four columns that indicate which week a person is attending an event. I have
a summary sheet I am preparing that I want to look-up instances of the
person's name and which week they are involved. So I have to check for both
cases, appearance of the name and an x in the week one column to return an
"OK" in the summary sheet.

I know this is confusing. If I can email you an example let me know.

Thanks in advance.

Scott


"bj" wrote:

> try If you want to enter the name in Cell C1 and have D1 give the result
> in D1 enter a
> =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> and in C1 enter the name
> "Scott" wrote:
>
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott

try
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")

"Scott" wrote:

> Hey bj,
>
> What I actually need to do is check for both the name and an X in a
> particular column. The worksheet I am working with has a list of names, and
> four columns that indicate which week a person is attending an event. I have
> a summary sheet I am preparing that I want to look-up instances of the
> person's name and which week they are involved. So I have to check for both
> cases, appearance of the name and an x in the week one column to return an
> "OK" in the summary sheet.
>
> I know this is confusing. If I can email you an example let me know.
>
> Thanks in advance.
>
> Scott
>
>
> "bj" wrote:
>
> > try If you want to enter the name in Cell C1 and have D1 give the result
> > in D1 enter a
> > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > and in C1 enter the name
> > "Scott" wrote:
> >
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott

=IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

Bob,

This is SO close... the last bit might prove to be the most difficult part.

That "B1" cell is actually going to be the cell in the same row that the
name is found in column B. My check is IF "John Doe" is found in the range
AND "X" is in the corresponding cell in column B THEN "Match", "No Match".

(fingers crossed you know the answer)

Thanks so much!

Scott



"Bob Phillips" wrote:

> =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
>
> --
> HTH
>
> Bob Phillips
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message
> news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott
>
>
>

Ok, there is one final problem that I need to get resolved. When calculating
the percentage accuarcy how do you keep the absolute vallue at no greater
than 100%.
Example: my current calculation, is created when there are numbers so from
below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5) or
if the result is greater than 100%, then it returns only 100%.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!!!!!

AWESOME!!!!!!!



"Scott" wrote:

> Bob,
>
> This is SO close... the last bit might prove to be the most difficult part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!

AWESOME!!!!!!!!!!!



"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

=IF(H144=FALSE,MIN(1,ABS(1-F144/D144)),IF(C144=0,"no value",H144))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:1140CCCE-0927-428A-86E8-A8C3E53CFDB2@microsoft.com...
> When I add the MIN(1,...) to my calculation it doens't return a value.
Here
> is the formula I am using and I wish to keep the value returned at less
than
> 100%.
>
> =IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
> works correctly for everything I need except if F144 is greater than D144.
>
>
> "Bob Phillips" wrote:
>
> > Simply stated, you need to force a maximum value of 1, so you take the
> > minimum of 1 (100%) or your calculation
> >
> > =IF(H5=??,MIN(1,ABS(1-F5/d5)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > > Ok, there is one final problem that I need to get resolved. When
> > calculating
> > > the percentage accuarcy how do you keep the absolute vallue at no
greater
> > > than 100%.
> > > Example: my current calculation, is created when there are numbers so
> > from
> > > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > > Where/how do I amend the formula so that it iehter take the
ABS(1-F5/d5)
> > or
> > > if the result is greater than 100%, then it returns only 100%.
> > >
> > > Jean
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > > >
> > > > and format as a percentage.
> > > >
> > > > You don't say what to do if neither are 0, so I just added "??"
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > > bj:
> > > > > I need help with a logical staement and you seem to be an expert.
I
> > am
> > > > > trying to calculate a forecast accuracy. I need to do more than
one
> > piece
> > > > of
> > > > > logic with in the formula.
> > > > > If we focus on 2 columns, i have column A that is a forecast
number
> > and
> > > > > column b that is an actual sales number. I have covered the case
of
> > if
> > > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> > also
> > > > need
> > > > > to cover if both column A and B are equal to zero then 100%
accuracy.
> > Can
> > > > > you help??
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try
> > > > > >
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > > >
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > Hey bj,
> > > > > > >
> > > > > > > What I actually need to do is check for both the name and an X
in
> > a
> > > > > > > particular column. The worksheet I am working with has a list
of
> > > > names, and
> > > > > > > four columns that indicate which week a person is attending an
> > event.
> > > > I have
> > > > > > > a summary sheet I am preparing that I want to look-up
instances of
> > the
> > > > > > > person's name and which week they are involved. So I have to
> > check
> > > > for both
> > > > > > > cases, appearance of the name and an x in the week one column
to
> > > > return an
> > > > > > > "OK" in the summary sheet.
> > > > > > >
> > > > > > > I know this is confusing. If I can email you an example let
me
> > know.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > >
> > > > > > > Scott
> > > > > > >
> > > > > > >
> > > > > > > "bj" wrote:
> > > > > > >
> > > > > > > > try If you want to enter the name in Cell C1 and have D1
give
> > the
> > > > result
> > > > > > > > in D1 enter a
> > > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > > and in C1 enter the name
> > > > > > > > "Scott" wrote:
> > > > > > > >
> > > > > > > > > This might be hard to explain...
> > > > > > > > >
> > > > > > > > > I want to check a sheet for a persons name AND if an X is
> > entered
> > > > in an
> > > > > > > > > associated cell. In literal terms the IF statement would
be
> > as
> > > > follows:
> > > > > > > > >
> > > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X
in
> > field
> > > > B1, THEN
> > > > > > > > > "OK")
> > > > > > > > >
> > > > > > > > > The X would appear in the same row as the name in the
range,
> > for
> > > > example:
> > > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > > >
> > > > > > > > > How would I make this work?
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > >
> > > > > > > > > Scott
> > > >
> > > >
> > > >
> >
> >
> >

bj:
I need help with a logical staement and you seem to be an expert. I am
trying to calculate a forecast accuracy. I need to do more than one piece of
logic with in the formula.
If we focus on 2 columns, i have column A that is a forecast number and
column b that is an actual sales number. I have covered the case of if
column A or B is equal to zero then enter a zero % accuarcy, but I also need
to cover if both column A and B are equal to zero then 100% accuracy. Can
you help??

"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

=IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))

and format as a percentage.

You don't say what to do if neither are 0, so I just added "??"

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> bj:
> I need help with a logical staement and you seem to be an expert. I am
> trying to calculate a forecast accuracy. I need to do more than one piece
of
> logic with in the formula.
> If we focus on 2 columns, i have column A that is a forecast number and
> column b that is an actual sales number. I have covered the case of if
> column A or B is equal to zero then enter a zero % accuarcy, but I also
need
> to cover if both column A and B are equal to zero then 100% accuracy. Can
> you help??
>
> "bj" wrote:
>
> > try
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
="X")--(E1:E1000))>0, "ok","nope")
> >
> > "Scott" wrote:
> >
> > > Hey bj,
> > >
> > > What I actually need to do is check for both the name and an X in a
> > > particular column. The worksheet I am working with has a list of
names, and
> > > four columns that indicate which week a person is attending an event.
I have
> > > a summary sheet I am preparing that I want to look-up instances of the
> > > person's name and which week they are involved. So I have to check
for both
> > > cases, appearance of the name and an x in the week one column to
return an
> > > "OK" in the summary sheet.
> > >
> > > I know this is confusing. If I can email you an example let me know.
> > >
> > > Thanks in advance.
> > >
> > > Scott
> > >
> > >
> > > "bj" wrote:
> > >
> > > > try If you want to enter the name in Cell C1 and have D1 give the
result
> > > > in D1 enter a
> > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > and in C1 enter the name
> > > > "Scott" wrote:
> > > >
> > > > > This might be hard to explain...
> > > > >
> > > > > I want to check a sheet for a persons name AND if an X is entered
in an
> > > > > associated cell. In literal terms the IF statement would be as
follows:
> > > > >
> > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
B1, THEN
> > > > > "OK")
> > > > >
> > > > > The X would appear in the same row as the name in the range, for
example:
> > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > >
> > > > > How would I make this work?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Scott

Bob,
Thank you. I am thrilled that there is an expert site like this. Make me
appear a bit more knowledgable in my job....my secret!! Thank you so very
much.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

try If you want to enter the name in Cell C1 and have D1 give the result
in D1 enter a
=if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
and in C1 enter the name
"Scott" wrote:

> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

Hey bj,

What I actually need to do is check for both the name and an X in a
particular column. The worksheet I am working with has a list of names, and
four columns that indicate which week a person is attending an event. I have
a summary sheet I am preparing that I want to look-up instances of the
person's name and which week they are involved. So I have to check for both
cases, appearance of the name and an x in the week one column to return an
"OK" in the summary sheet.

I know this is confusing. If I can email you an example let me know.

Thanks in advance.

Scott


"bj" wrote:

> try If you want to enter the name in Cell C1 and have D1 give the result
> in D1 enter a
> =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> and in C1 enter the name
> "Scott" wrote:
>
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott

try
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")

"Scott" wrote:

> Hey bj,
>
> What I actually need to do is check for both the name and an X in a
> particular column. The worksheet I am working with has a list of names, and
> four columns that indicate which week a person is attending an event. I have
> a summary sheet I am preparing that I want to look-up instances of the
> person's name and which week they are involved. So I have to check for both
> cases, appearance of the name and an x in the week one column to return an
> "OK" in the summary sheet.
>
> I know this is confusing. If I can email you an example let me know.
>
> Thanks in advance.
>
> Scott
>
>
> "bj" wrote:
>
> > try If you want to enter the name in Cell C1 and have D1 give the result
> > in D1 enter a
> > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > and in C1 enter the name
> > "Scott" wrote:
> >
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott

=IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

Bob,

This is SO close... the last bit might prove to be the most difficult part.

That "B1" cell is actually going to be the cell in the same row that the
name is found in column B. My check is IF "John Doe" is found in the range
AND "X" is in the corresponding cell in column B THEN "Match", "No Match".

(fingers crossed you know the answer)

Thanks so much!

Scott



"Bob Phillips" wrote:

> =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
>
> --
> HTH
>
> Bob Phillips
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message
> news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott
>
>
>

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!!!!!

AWESOME!!!!!!!



"Scott" wrote:

> Bob,
>
> This is SO close... the last bit might prove to be the most difficult part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!

AWESOME!!!!!!!!!!!



"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

bj:
I need help with a logical staement and you seem to be an expert. I am
trying to calculate a forecast accuracy. I need to do more than one piece of
logic with in the formula.
If we focus on 2 columns, i have column A that is a forecast number and
column b that is an actual sales number. I have covered the case of if
column A or B is equal to zero then enter a zero % accuarcy, but I also need
to cover if both column A and B are equal to zero then 100% accuracy. Can
you help??

"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

=IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))

and format as a percentage.

You don't say what to do if neither are 0, so I just added "??"

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> bj:
> I need help with a logical staement and you seem to be an expert. I am
> trying to calculate a forecast accuracy. I need to do more than one piece
of
> logic with in the formula.
> If we focus on 2 columns, i have column A that is a forecast number and
> column b that is an actual sales number. I have covered the case of if
> column A or B is equal to zero then enter a zero % accuarcy, but I also
need
> to cover if both column A and B are equal to zero then 100% accuracy. Can
> you help??
>
> "bj" wrote:
>
> > try
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
="X")--(E1:E1000))>0, "ok","nope")
> >
> > "Scott" wrote:
> >
> > > Hey bj,
> > >
> > > What I actually need to do is check for both the name and an X in a
> > > particular column. The worksheet I am working with has a list of
names, and
> > > four columns that indicate which week a person is attending an event.
I have
> > > a summary sheet I am preparing that I want to look-up instances of the
> > > person's name and which week they are involved. So I have to check
for both
> > > cases, appearance of the name and an x in the week one column to
return an
> > > "OK" in the summary sheet.
> > >
> > > I know this is confusing. If I can email you an example let me know.
> > >
> > > Thanks in advance.
> > >
> > > Scott
> > >
> > >
> > > "bj" wrote:
> > >
> > > > try If you want to enter the name in Cell C1 and have D1 give the
result
> > > > in D1 enter a
> > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > and in C1 enter the name
> > > > "Scott" wrote:
> > > >
> > > > > This might be hard to explain...
> > > > >
> > > > > I want to check a sheet for a persons name AND if an X is entered
in an
> > > > > associated cell. In literal terms the IF statement would be as
follows:
> > > > >
> > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
B1, THEN
> > > > > "OK")
> > > > >
> > > > > The X would appear in the same row as the name in the range, for
example:
> > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > >
> > > > > How would I make this work?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Scott

=IF(ISNUMBER(MATCH("John Doe",A1:A99,0)),IF(INDEX(B1:B99,MATCH("John
Doe",A1:A99,0))="X","Match","No match"))

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6722916F-072B-4F09-BB45-6292F4C6E9BE@microsoft.com...
> Bob,
>
> This is SO close... the last bit might prove to be the most difficult
part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the
range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in
an
> > > associated cell. In literal terms the IF statement would be as
follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for
example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

Bob,
Thank you. I am thrilled that there is an expert site like this. Make me
appear a bit more knowledgable in my job....my secret!! Thank you so very
much.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

Ok, there is one final problem that I need to get resolved. When calculating
the percentage accuarcy how do you keep the absolute vallue at no greater
than 100%.
Example: my current calculation, is created when there are numbers so from
below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5) or
if the result is greater than 100%, then it returns only 100%.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

Simply stated, you need to force a maximum value of 1, so you take the
minimum of 1 (100%) or your calculation

=IF(H5=??,MIN(1,ABS(1-F5/d5)))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> Ok, there is one final problem that I need to get resolved. When
calculating
> the percentage accuarcy how do you keep the absolute vallue at no greater
> than 100%.
> Example: my current calculation, is created when there are numbers so
from
> below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
or
> if the result is greater than 100%, then it returns only 100%.
>
> Jean
>
> "Bob Phillips" wrote:
>
> > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> >
> > and format as a percentage.
> >
> > You don't say what to do if neither are 0, so I just added "??"
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > bj:
> > > I need help with a logical staement and you seem to be an expert. I
am
> > > trying to calculate a forecast accuracy. I need to do more than one
piece
> > of
> > > logic with in the formula.
> > > If we focus on 2 columns, i have column A that is a forecast number
and
> > > column b that is an actual sales number. I have covered the case of
if
> > > column A or B is equal to zero then enter a zero % accuarcy, but I
also
> > need
> > > to cover if both column A and B are equal to zero then 100% accuracy.
Can
> > > you help??
> > >
> > > "bj" wrote:
> > >
> > > > try
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > ="X")--(E1:E1000))>0, "ok","nope")
> > > >
> > > > "Scott" wrote:
> > > >
> > > > > Hey bj,
> > > > >
> > > > > What I actually need to do is check for both the name and an X in
a
> > > > > particular column. The worksheet I am working with has a list of
> > names, and
> > > > > four columns that indicate which week a person is attending an
event.
> > I have
> > > > > a summary sheet I am preparing that I want to look-up instances of
the
> > > > > person's name and which week they are involved. So I have to
check
> > for both
> > > > > cases, appearance of the name and an x in the week one column to
> > return an
> > > > > "OK" in the summary sheet.
> > > > >
> > > > > I know this is confusing. If I can email you an example let me
know.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Scott
> > > > >
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try If you want to enter the name in Cell C1 and have D1 give
the
> > result
> > > > > > in D1 enter a
> > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > and in C1 enter the name
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > This might be hard to explain...
> > > > > > >
> > > > > > > I want to check a sheet for a persons name AND if an X is
entered
> > in an
> > > > > > > associated cell. In literal terms the IF statement would be
as
> > follows:
> > > > > > >
> > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
field
> > B1, THEN
> > > > > > > "OK")
> > > > > > >
> > > > > > > The X would appear in the same row as the name in the range,
for
> > example:
> > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > >
> > > > > > > How would I make this work?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > Scott
> >
> >
> >

When I add the MIN(1,...) to my calculation it doens't return a value. Here
is the formula I am using and I wish to keep the value returned at less than
100%.

=IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
works correctly for everything I need except if F144 is greater than D144.


"Bob Phillips" wrote:

> Simply stated, you need to force a maximum value of 1, so you take the
> minimum of 1 (100%) or your calculation
>
> =IF(H5=??,MIN(1,ABS(1-F5/d5)))
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > Ok, there is one final problem that I need to get resolved. When
> calculating
> > the percentage accuarcy how do you keep the absolute vallue at no greater
> > than 100%.
> > Example: my current calculation, is created when there are numbers so
> from
> > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
> or
> > if the result is greater than 100%, then it returns only 100%.
> >
> > Jean
> >
> > "Bob Phillips" wrote:
> >
> > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > >
> > > and format as a percentage.
> > >
> > > You don't say what to do if neither are 0, so I just added "??"
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > bj:
> > > > I need help with a logical staement and you seem to be an expert. I
> am
> > > > trying to calculate a forecast accuracy. I need to do more than one
> piece
> > > of
> > > > logic with in the formula.
> > > > If we focus on 2 columns, i have column A that is a forecast number
> and
> > > > column b that is an actual sales number. I have covered the case of
> if
> > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> also
> > > need
> > > > to cover if both column A and B are equal to zero then 100% accuracy.
> Can
> > > > you help??
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try
> > > > >
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > >
> > > > > "Scott" wrote:
> > > > >
> > > > > > Hey bj,
> > > > > >
> > > > > > What I actually need to do is check for both the name and an X in
> a
> > > > > > particular column. The worksheet I am working with has a list of
> > > names, and
> > > > > > four columns that indicate which week a person is attending an
> event.
> > > I have
> > > > > > a summary sheet I am preparing that I want to look-up instances of
> the
> > > > > > person's name and which week they are involved. So I have to
> check
> > > for both
> > > > > > cases, appearance of the name and an x in the week one column to
> > > return an
> > > > > > "OK" in the summary sheet.
> > > > > >
> > > > > > I know this is confusing. If I can email you an example let me
> know.
> > > > > >
> > > > > > Thanks in advance.
> > > > > >
> > > > > > Scott
> > > > > >
> > > > > >
> > > > > > "bj" wrote:
> > > > > >
> > > > > > > try If you want to enter the name in Cell C1 and have D1 give
> the
> > > result
> > > > > > > in D1 enter a
> > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > and in C1 enter the name
> > > > > > > "Scott" wrote:
> > > > > > >
> > > > > > > > This might be hard to explain...
> > > > > > > >
> > > > > > > > I want to check a sheet for a persons name AND if an X is
> entered
> > > in an
> > > > > > > > associated cell. In literal terms the IF statement would be
> as
> > > follows:
> > > > > > > >
> > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
> field
> > > B1, THEN
> > > > > > > > "OK")
> > > > > > > >
> > > > > > > > The X would appear in the same row as the name in the range,
> for
> > > example:
> > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > >
> > > > > > > > How would I make this work?
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > >
> > > > > > > > Scott
> > >
> > >
> > >
>
>
>

=IF(H144=FALSE,MIN(1,ABS(1-F144/D144)),IF(C144=0,"no value",H144))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:1140CCCE-0927-428A-86E8-A8C3E53CFDB2@microsoft.com...
> When I add the MIN(1,...) to my calculation it doens't return a value.
Here
> is the formula I am using and I wish to keep the value returned at less
than
> 100%.
>
> =IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
> works correctly for everything I need except if F144 is greater than D144.
>
>
> "Bob Phillips" wrote:
>
> > Simply stated, you need to force a maximum value of 1, so you take the
> > minimum of 1 (100%) or your calculation
> >
> > =IF(H5=??,MIN(1,ABS(1-F5/d5)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > > Ok, there is one final problem that I need to get resolved. When
> > calculating
> > > the percentage accuarcy how do you keep the absolute vallue at no
greater
> > > than 100%.
> > > Example: my current calculation, is created when there are numbers so
> > from
> > > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > > Where/how do I amend the formula so that it iehter take the
ABS(1-F5/d5)
> > or
> > > if the result is greater than 100%, then it returns only 100%.
> > >
> > > Jean
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > > >
> > > > and format as a percentage.
> > > >
> > > > You don't say what to do if neither are 0, so I just added "??"
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > > bj:
> > > > > I need help with a logical staement and you seem to be an expert.
I
> > am
> > > > > trying to calculate a forecast accuracy. I need to do more than
one
> > piece
> > > > of
> > > > > logic with in the formula.
> > > > > If we focus on 2 columns, i have column A that is a forecast
number
> > and
> > > > > column b that is an actual sales number. I have covered the case
of
> > if
> > > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> > also
> > > > need
> > > > > to cover if both column A and B are equal to zero then 100%
accuracy.
> > Can
> > > > > you help??
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try
> > > > > >
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > > >
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > Hey bj,
> > > > > > >
> > > > > > > What I actually need to do is check for both the name and an X
in
> > a
> > > > > > > particular column. The worksheet I am working with has a list
of
> > > > names, and
> > > > > > > four columns that indicate which week a person is attending an
> > event.
> > > > I have
> > > > > > > a summary sheet I am preparing that I want to look-up
instances of
> > the
> > > > > > > person's name and which week they are involved. So I have to
> > check
> > > > for both
> > > > > > > cases, appearance of the name and an x in the week one column
to
> > > > return an
> > > > > > > "OK" in the summary sheet.
> > > > > > >
> > > > > > > I know this is confusing. If I can email you an example let
me
> > know.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > >
> > > > > > > Scott
> > > > > > >
> > > > > > >
> > > > > > > "bj" wrote:
> > > > > > >
> > > > > > > > try If you want to enter the name in Cell C1 and have D1
give
> > the
> > > > result
> > > > > > > > in D1 enter a
> > > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > > and in C1 enter the name
> > > > > > > > "Scott" wrote:
> > > > > > > >
> > > > > > > > > This might be hard to explain...
> > > > > > > > >
> > > > > > > > > I want to check a sheet for a persons name AND if an X is
> > entered
> > > > in an
> > > > > > > > > associated cell. In literal terms the IF statement would
be
> > as
> > > > follows:
> > > > > > > > >
> > > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X
in
> > field
> > > > B1, THEN
> > > > > > > > > "OK")
> > > > > > > > >
> > > > > > > > > The X would appear in the same row as the name in the
range,
> > for
> > > > example:
> > > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > > >
> > > > > > > > > How would I make this work?
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > >
> > > > > > > > > Scott
> > > >
> > > >
> > > >
> >
> >
> >

=IF(H144=FALSE,MIN(1,ABS(1-F144/D144)),IF(C144=0,"no value",H144))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:1140CCCE-0927-428A-86E8-A8C3E53CFDB2@microsoft.com...
> When I add the MIN(1,...) to my calculation it doens't return a value.
Here
> is the formula I am using and I wish to keep the value returned at less
than
> 100%.
>
> =IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
> works correctly for everything I need except if F144 is greater than D144.
>
>
> "Bob Phillips" wrote:
>
> > Simply stated, you need to force a maximum value of 1, so you take the
> > minimum of 1 (100%) or your calculation
> >
> > =IF(H5=??,MIN(1,ABS(1-F5/d5)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > > Ok, there is one final problem that I need to get resolved. When
> > calculating
> > > the percentage accuarcy how do you keep the absolute vallue at no
greater
> > > than 100%.
> > > Example: my current calculation, is created when there are numbers so
> > from
> > > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > > Where/how do I amend the formula so that it iehter take the
ABS(1-F5/d5)
> > or
> > > if the result is greater than 100%, then it returns only 100%.
> > >
> > > Jean
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > > >
> > > > and format as a percentage.
> > > >
> > > > You don't say what to do if neither are 0, so I just added "??"
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > > bj:
> > > > > I need help with a logical staement and you seem to be an expert.
I
> > am
> > > > > trying to calculate a forecast accuracy. I need to do more than
one
> > piece
> > > > of
> > > > > logic with in the formula.
> > > > > If we focus on 2 columns, i have column A that is a forecast
number
> > and
> > > > > column b that is an actual sales number. I have covered the case
of
> > if
> > > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> > also
> > > > need
> > > > > to cover if both column A and B are equal to zero then 100%
accuracy.
> > Can
> > > > > you help??
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try
> > > > > >
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > > >
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > Hey bj,
> > > > > > >
> > > > > > > What I actually need to do is check for both the name and an X
in
> > a
> > > > > > > particular column. The worksheet I am working with has a list
of
> > > > names, and
> > > > > > > four columns that indicate which week a person is attending an
> > event.
> > > > I have
> > > > > > > a summary sheet I am preparing that I want to look-up
instances of
> > the
> > > > > > > person's name and which week they are involved. So I have to
> > check
> > > > for both
> > > > > > > cases, appearance of the name and an x in the week one column
to
> > > > return an
> > > > > > > "OK" in the summary sheet.
> > > > > > >
> > > > > > > I know this is confusing. If I can email you an example let
me
> > know.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > >
> > > > > > > Scott
> > > > > > >
> > > > > > >
> > > > > > > "bj" wrote:
> > > > > > >
> > > > > > > > try If you want to enter the name in Cell C1 and have D1
give
> > the
> > > > result
> > > > > > > > in D1 enter a
> > > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > > and in C1 enter the name
> > > > > > > > "Scott" wrote:
> > > > > > > >
> > > > > > > > > This might be hard to explain...
> > > > > > > > >
> > > > > > > > > I want to check a sheet for a persons name AND if an X is
> > entered
> > > > in an
> > > > > > > > > associated cell. In literal terms the IF statement would
be
> > as
> > > > follows:
> > > > > > > > >
> > > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X
in
> > field
> > > > B1, THEN
> > > > > > > > > "OK")
> > > > > > > > >
> > > > > > > > > The X would appear in the same row as the name in the
range,
> > for
> > > > example:
> > > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > > >
> > > > > > > > > How would I make this work?
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > >
> > > > > > > > > Scott
> > > >
> > > >
> > > >
> >
> >
> >

This might be hard to explain...

I want to check a sheet for a persons name AND if an X is entered in an
associated cell. In literal terms the IF statement would be as follows:

IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
"OK")

The X would appear in the same row as the name in the range, for example:
Field A1 contains "John Doe" and field B1 contains "X".

How would I make this work?

Thanks,

Scott

When I add the MIN(1,...) to my calculation it doens't return a value. Here
is the formula I am using and I wish to keep the value returned at less than
100%.

=IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
works correctly for everything I need except if F144 is greater than D144.


"Bob Phillips" wrote:

> Simply stated, you need to force a maximum value of 1, so you take the
> minimum of 1 (100%) or your calculation
>
> =IF(H5=??,MIN(1,ABS(1-F5/d5)))
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> > Ok, there is one final problem that I need to get resolved. When
> calculating
> > the percentage accuarcy how do you keep the absolute vallue at no greater
> > than 100%.
> > Example: my current calculation, is created when there are numbers so
> from
> > below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> > Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
> or
> > if the result is greater than 100%, then it returns only 100%.
> >
> > Jean
> >
> > "Bob Phillips" wrote:
> >
> > > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> > >
> > > and format as a percentage.
> > >
> > > You don't say what to do if neither are 0, so I just added "??"
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > > bj:
> > > > I need help with a logical staement and you seem to be an expert. I
> am
> > > > trying to calculate a forecast accuracy. I need to do more than one
> piece
> > > of
> > > > logic with in the formula.
> > > > If we focus on 2 columns, i have column A that is a forecast number
> and
> > > > column b that is an actual sales number. I have covered the case of
> if
> > > > column A or B is equal to zero then enter a zero % accuarcy, but I
> also
> > > need
> > > > to cover if both column A and B are equal to zero then 100% accuracy.
> Can
> > > > you help??
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try
> > > > >
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > > ="X")--(E1:E1000))>0, "ok","nope")
> > > > >
> > > > > "Scott" wrote:
> > > > >
> > > > > > Hey bj,
> > > > > >
> > > > > > What I actually need to do is check for both the name and an X in
> a
> > > > > > particular column. The worksheet I am working with has a list of
> > > names, and
> > > > > > four columns that indicate which week a person is attending an
> event.
> > > I have
> > > > > > a summary sheet I am preparing that I want to look-up instances of
> the
> > > > > > person's name and which week they are involved. So I have to
> check
> > > for both
> > > > > > cases, appearance of the name and an x in the week one column to
> > > return an
> > > > > > "OK" in the summary sheet.
> > > > > >
> > > > > > I know this is confusing. If I can email you an example let me
> know.
> > > > > >
> > > > > > Thanks in advance.
> > > > > >
> > > > > > Scott
> > > > > >
> > > > > >
> > > > > > "bj" wrote:
> > > > > >
> > > > > > > try If you want to enter the name in Cell C1 and have D1 give
> the
> > > result
> > > > > > > in D1 enter a
> > > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > > and in C1 enter the name
> > > > > > > "Scott" wrote:
> > > > > > >
> > > > > > > > This might be hard to explain...
> > > > > > > >
> > > > > > > > I want to check a sheet for a persons name AND if an X is
> entered
> > > in an
> > > > > > > > associated cell. In literal terms the IF statement would be
> as
> > > follows:
> > > > > > > >
> > > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
> field
> > > B1, THEN
> > > > > > > > "OK")
> > > > > > > >
> > > > > > > > The X would appear in the same row as the name in the range,
> for
> > > example:
> > > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > > >
> > > > > > > > How would I make this work?
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > >
> > > > > > > > Scott
> > >
> > >
> > >
>
>
>

Simply stated, you need to force a maximum value of 1, so you take the
minimum of 1 (100%) or your calculation

=IF(H5=??,MIN(1,ABS(1-F5/d5)))

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:A943D204-278A-438E-AB10-71098A049779@microsoft.com...
> Ok, there is one final problem that I need to get resolved. When
calculating
> the percentage accuarcy how do you keep the absolute vallue at no greater
> than 100%.
> Example: my current calculation, is created when there are numbers so
from
> below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
> Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5)
or
> if the result is greater than 100%, then it returns only 100%.
>
> Jean
>
> "Bob Phillips" wrote:
>
> > =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
> >
> > and format as a percentage.
> >
> > You don't say what to do if neither are 0, so I just added "??"
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jean" <Jean@discussions.microsoft.com> wrote in message
> > news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > > bj:
> > > I need help with a logical staement and you seem to be an expert. I
am
> > > trying to calculate a forecast accuracy. I need to do more than one
piece
> > of
> > > logic with in the formula.
> > > If we focus on 2 columns, i have column A that is a forecast number
and
> > > column b that is an actual sales number. I have covered the case of
if
> > > column A or B is equal to zero then enter a zero % accuarcy, but I
also
> > need
> > > to cover if both column A and B are equal to zero then 100% accuracy.
Can
> > > you help??
> > >
> > > "bj" wrote:
> > >
> > > > try
> > > >
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> > ="X")--(E1:E1000))>0, "ok","nope")
> > > >
> > > > "Scott" wrote:
> > > >
> > > > > Hey bj,
> > > > >
> > > > > What I actually need to do is check for both the name and an X in
a
> > > > > particular column. The worksheet I am working with has a list of
> > names, and
> > > > > four columns that indicate which week a person is attending an
event.
> > I have
> > > > > a summary sheet I am preparing that I want to look-up instances of
the
> > > > > person's name and which week they are involved. So I have to
check
> > for both
> > > > > cases, appearance of the name and an x in the week one column to
> > return an
> > > > > "OK" in the summary sheet.
> > > > >
> > > > > I know this is confusing. If I can email you an example let me
know.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Scott
> > > > >
> > > > >
> > > > > "bj" wrote:
> > > > >
> > > > > > try If you want to enter the name in Cell C1 and have D1 give
the
> > result
> > > > > > in D1 enter a
> > > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > > and in C1 enter the name
> > > > > > "Scott" wrote:
> > > > > >
> > > > > > > This might be hard to explain...
> > > > > > >
> > > > > > > I want to check a sheet for a persons name AND if an X is
entered
> > in an
> > > > > > > associated cell. In literal terms the IF statement would be
as
> > follows:
> > > > > > >
> > > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in
field
> > B1, THEN
> > > > > > > "OK")
> > > > > > >
> > > > > > > The X would appear in the same row as the name in the range,
for
> > example:
> > > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > > >
> > > > > > > How would I make this work?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > Scott
> >
> >
> >

Ok, there is one final problem that I need to get resolved. When calculating
the percentage accuarcy how do you keep the absolute vallue at no greater
than 100%.
Example: my current calculation, is created when there are numbers so from
below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5) or
if the result is greater than 100%, then it returns only 100%.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

Bob,
Thank you. I am thrilled that there is an expert site like this. Make me
appear a bit more knowledgable in my job....my secret!! Thank you so very
much.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>

=IF(ISNUMBER(MATCH("John Doe",A1:A99,0)),IF(INDEX(B1:B99,MATCH("John
Doe",A1:A99,0))="X","Match","No match"))

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6722916F-072B-4F09-BB45-6292F4C6E9BE@microsoft.com...
> Bob,
>
> This is SO close... the last bit might prove to be the most difficult
part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the
range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in
an
> > > associated cell. In literal terms the IF statement would be as
follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for
example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

=IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))

and format as a percentage.

You don't say what to do if neither are 0, so I just added "??"

--
HTH

Bob Phillips

"Jean" <Jean@discussions.microsoft.com> wrote in message
news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> bj:
> I need help with a logical staement and you seem to be an expert. I am
> trying to calculate a forecast accuracy. I need to do more than one piece
of
> logic with in the formula.
> If we focus on 2 columns, i have column A that is a forecast number and
> column b that is an actual sales number. I have covered the case of if
> column A or B is equal to zero then enter a zero % accuarcy, but I also
need
> to cover if both column A and B are equal to zero then 100% accuracy. Can
> you help??
>
> "bj" wrote:
>
> > try
> >
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
="X")--(E1:E1000))>0, "ok","nope")
> >
> > "Scott" wrote:
> >
> > > Hey bj,
> > >
> > > What I actually need to do is check for both the name and an X in a
> > > particular column. The worksheet I am working with has a list of
names, and
> > > four columns that indicate which week a person is attending an event.
I have
> > > a summary sheet I am preparing that I want to look-up instances of the
> > > person's name and which week they are involved. So I have to check
for both
> > > cases, appearance of the name and an x in the week one column to
return an
> > > "OK" in the summary sheet.
> > >
> > > I know this is confusing. If I can email you an example let me know.
> > >
> > > Thanks in advance.
> > >
> > > Scott
> > >
> > >
> > > "bj" wrote:
> > >
> > > > try If you want to enter the name in Cell C1 and have D1 give the
result
> > > > in D1 enter a
> > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > and in C1 enter the name
> > > > "Scott" wrote:
> > > >
> > > > > This might be hard to explain...
> > > > >
> > > > > I want to check a sheet for a persons name AND if an X is entered
in an
> > > > > associated cell. In literal terms the IF statement would be as
follows:
> > > > >
> > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
B1, THEN
> > > > > "OK")
> > > > >
> > > > > The X would appear in the same row as the name in the range, for
example:
> > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > >
> > > > > How would I make this work?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Scott

bj:
I need help with a logical staement and you seem to be an expert. I am
trying to calculate a forecast accuracy. I need to do more than one piece of
logic with in the formula.
If we focus on 2 columns, i have column A that is a forecast number and
column b that is an actual sales number. I have covered the case of if
column A or B is equal to zero then enter a zero % accuarcy, but I also need
to cover if both column A and B are equal to zero then 100% accuracy. Can
you help??

"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!

AWESOME!!!!!!!!!!!



"bj" wrote:

> try
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")
>
> "Scott" wrote:
>
> > Hey bj,
> >
> > What I actually need to do is check for both the name and an X in a
> > particular column. The worksheet I am working with has a list of names, and
> > four columns that indicate which week a person is attending an event. I have
> > a summary sheet I am preparing that I want to look-up instances of the
> > person's name and which week they are involved. So I have to check for both
> > cases, appearance of the name and an x in the week one column to return an
> > "OK" in the summary sheet.
> >
> > I know this is confusing. If I can email you an example let me know.
> >
> > Thanks in advance.
> >
> > Scott
> >
> >
> > "bj" wrote:
> >
> > > try If you want to enter the name in Cell C1 and have D1 give the result
> > > in D1 enter a
> > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > and in C1 enter the name
> > > "Scott" wrote:
> > >
> > > > This might be hard to explain...
> > > >
> > > > I want to check a sheet for a persons name AND if an X is entered in an
> > > > associated cell. In literal terms the IF statement would be as follows:
> > > >
> > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > > "OK")
> > > >
> > > > The X would appear in the same row as the name in the range, for example:
> > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > >
> > > > How would I make this work?
> > > >
> > > > Thanks,
> > > >
> > > > Scott

THAT WORKED!!!!!!!!

THANK YOU SO MUCH!!!!!!!!!!!

AWESOME!!!!!!!



"Scott" wrote:

> Bob,
>
> This is SO close... the last bit might prove to be the most difficult part.
>
> That "B1" cell is actually going to be the cell in the same row that the
> name is found in column B. My check is IF "John Doe" is found in the range
> AND "X" is in the corresponding cell in column B THEN "Match", "No Match".
>
> (fingers crossed you know the answer)
>
> Thanks so much!
>
> Scott
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Scott" <Scott@discussions.microsoft.com> wrote in message
> > news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> > THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

Bob,

This is SO close... the last bit might prove to be the most difficult part.

That "B1" cell is actually going to be the cell in the same row that the
name is found in column B. My check is IF "John Doe" is found in the range
AND "X" is in the corresponding cell in column B THEN "Match", "No Match".

(fingers crossed you know the answer)

Thanks so much!

Scott



"Bob Phillips" wrote:

> =IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")
>
> --
> HTH
>
> Bob Phillips
>
> "Scott" <Scott@discussions.microsoft.com> wrote in message
> news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
> THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott
>
>
>

=IF(AND(COUNTIF(A1:A99,"John Doe")>0,B1="X"),"Match","No match")

--
HTH

Bob Phillips

"Scott" <Scott@discussions.microsoft.com> wrote in message
news:6D36DCA0-BD8D-4F4E-BD83-B9A18862CA24@microsoft.com...
> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1,
THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott

try
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000="X")--(E1:E1000))>0, "ok","nope")

"Scott" wrote:

> Hey bj,
>
> What I actually need to do is check for both the name and an X in a
> particular column. The worksheet I am working with has a list of names, and
> four columns that indicate which week a person is attending an event. I have
> a summary sheet I am preparing that I want to look-up instances of the
> person's name and which week they are involved. So I have to check for both
> cases, appearance of the name and an x in the week one column to return an
> "OK" in the summary sheet.
>
> I know this is confusing. If I can email you an example let me know.
>
> Thanks in advance.
>
> Scott
>
>
> "bj" wrote:
>
> > try If you want to enter the name in Cell C1 and have D1 give the result
> > in D1 enter a
> > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > and in C1 enter the name
> > "Scott" wrote:
> >
> > > This might be hard to explain...
> > >
> > > I want to check a sheet for a persons name AND if an X is entered in an
> > > associated cell. In literal terms the IF statement would be as follows:
> > >
> > > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > > "OK")
> > >
> > > The X would appear in the same row as the name in the range, for example:
> > > Field A1 contains "John Doe" and field B1 contains "X".
> > >
> > > How would I make this work?
> > >
> > > Thanks,
> > >
> > > Scott

Hey bj,

What I actually need to do is check for both the name and an X in a
particular column. The worksheet I am working with has a list of names, and
four columns that indicate which week a person is attending an event. I have
a summary sheet I am preparing that I want to look-up instances of the
person's name and which week they are involved. So I have to check for both
cases, appearance of the name and an x in the week one column to return an
"OK" in the summary sheet.

I know this is confusing. If I can email you an example let me know.

Thanks in advance.

Scott


"bj" wrote:

> try If you want to enter the name in Cell C1 and have D1 give the result
> in D1 enter a
> =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> and in C1 enter the name
> "Scott" wrote:
>
> > This might be hard to explain...
> >
> > I want to check a sheet for a persons name AND if an X is entered in an
> > associated cell. In literal terms the IF statement would be as follows:
> >
> > IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> > "OK")
> >
> > The X would appear in the same row as the name in the range, for example:
> > Field A1 contains "John Doe" and field B1 contains "X".
> >
> > How would I make this work?
> >
> > Thanks,
> >
> > Scott

try If you want to enter the name in Cell C1 and have D1 give the result
in D1 enter a
=if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
and in C1 enter the name
"Scott" wrote:

> This might be hard to explain...
>
> I want to check a sheet for a persons name AND if an X is entered in an
> associated cell. In literal terms the IF statement would be as follows:
>
> IF ("John Doe" is found in range A1:A99 AND there is an X in field B1, THEN
> "OK")
>
> The X would appear in the same row as the name in the range, for example:
> Field A1 contains "John Doe" and field B1 contains "X".
>
> How would I make this work?
>
> Thanks,
>
> Scott