IF Statement Question

S

Scott

This might be hard to explain...

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

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

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

How would I make this work?

Thanks,

Scott
 
B

bj

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
 
S

Scott

Hey bj,

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

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

Thanks in advance.

Scott
 
B

bj

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

Scott

Bob,

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

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

(fingers crossed you know the answer)

Thanks so much!

Scott
 
J

Jean

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

Bob Phillips

=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 said:
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??
Click to expand...
=if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
="X")--(E1:E1000))>0, "ok","nope")
 
B

Bob Phillips

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

Jean

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
 
J

Jean

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
 
B

Bob Phillips

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

Jean

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