IF Statement Question

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!!!!!!!!!!!



"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

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(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
>
>
>

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

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

=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
> > > >
> > > >
> > > >
> >
> >
> >

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

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

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

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

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

=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(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

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

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