formula (IF) question
 

Greetings,

I do tech support (Exchange) for a school district, and I have been
approached with an Excel question by one of the users. I am fairly skilled
with Excel, but formulas are a weakness for me. In a simplified form, here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula that will look at
cells A2:E2, and if all the cells are checked, will return one value, if all
the cells are unchecked, will return a second value, and if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but have not had much
success. Thanks In Advance. -Drew

Hello there,

You could try and combine the And function with the IF
functions:

y = the check mark you choose
"" = Blanks

=IF(AND(A1="y",B1="y",C1="y",D1="y",E="y"),"All values
are checked",IF(AND(A1="",B1="",C1="",D1="",E=""),"All
values are blank","There are some checks"))

The AND function gives a True if all the conditions are
met and a False if one of them is not met. You can have
upto 30 conditions in an AND function.

Hope this helps

Judith

-----Original Message-----
Greetings,

I do tech support (Exchange) for a school district, and
I have been
approached with an Excel question by one of the users. I
am fairly skilled
with Excel, but formulas are a weakness for me. In a
simplified form, here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula
that will look at
cells A2:E2, and if all the cells are checked, will
return one value, if all
the cells are unchecked, will return a second value, and
if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but
have not had much
success. Thanks In Advance. -Drew


.

How about

=IF(COUNTA(A2:E2)=5."Value 1",IF(COUNTA(A2:E2)=0,"Value 2","Value 3"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Drew Halevy" wrote in message
...
Greetings,

I do tech support (Exchange) for a school district, and I have been
approached with an Excel question by one of the users. I am fairly skilled
with Excel, but formulas are a weakness for me. In a simplified form, here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula that will look at
cells A2:E2, and if all the cells are checked, will return one value, if
all
the cells are unchecked, will return a second value, and if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but have not had much
success. Thanks In Advance. -Drew

Thanks to Bob and Judith for your responses! I think this is just what she
is looking for! -Drew


"Bob Phillips" wrote in message
...
How about

=IF(COUNTA(A2:E2)=5."Value 1",IF(COUNTA(A2:E2)=0,"Value 2","Value 3"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Drew Halevy" wrote in message
...
Greetings,

I do tech support (Exchange) for a school district, and I have been
approached with an Excel question by one of the users. I am fairly
skilled
with Excel, but formulas are a weakness for me. In a simplified form,
here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula that will look
at
cells A2:E2, and if all the cells are checked, will return one value, if
all
the cells are unchecked, will return a second value, and if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but have not had
much
success. Thanks In Advance. -Drew

Try:

=CHOOSE(ROUNDUP(COUNTIF(A2:E2,"x")/5/0.9,0)
+1,"v1","v2","v3")

I used "x" rather than a checkmark.

v1 = value when no checkmarks exist
v2 = value when some checkmarks exist
v3 = value when all 5 checkmarks exist

HTH
Jason
Atlanta, GA

-----Original Message-----
Greetings,

I do tech support (Exchange) for a school district, and
I have been
approached with an Excel question by one of the users. I
am fairly skilled
with Excel, but formulas are a weakness for me. In a
simplified form, here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula
that will look at
cells A2:E2, and if all the cells are checked, will
return one value, if all
the cells are unchecked, will return a second value, and
if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but
have not had much
success. Thanks In Advance. -Drew


.

Thanks! I will give it a try! -Drew

"Jason Morin" wrote in message
...
Try:

=CHOOSE(ROUNDUP(COUNTIF(A2:E2,"x")/5/0.9,0)
+1,"v1","v2","v3")

I used "x" rather than a checkmark.

v1 = value when no checkmarks exist
v2 = value when some checkmarks exist
v3 = value when all 5 checkmarks exist

HTH
Jason
Atlanta, GA

-----Original Message-----
Greetings,

I do tech support (Exchange) for a school district, and
I have been
approached with an Excel question by one of the users. I
am fairly skilled
with Excel, but formulas are a weakness for me. In a
simplified form, here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula
that will look at
cells A2:E2, and if all the cells are checked, will
return one value, if all
the cells are unchecked, will return a second value, and
if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but
have not had much
success. Thanks In Advance. -Drew


.

Based on Judith and Bob, I modified that formula, so you can count how many
the box checked.
=IF(COUNTA(A2:E2)=5, "5", IF(COUNTA(A2:E2)=0, "0", COUNTA(A2:E2)))

"Drew Halevy" wrote:

Greetings,

I do tech support (Exchange) for a school district, and I have been
approached with an Excel question by one of the users. I am fairly skilled
with Excel, but formulas are a weakness for me. In a simplified form, here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula that will look at
cells A2:E2, and if all the cells are checked, will return one value, if all
the cells are unchecked, will return a second value, and if only some are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but have not had much
success. Thanks In Advance. -Drew

Why not just

=COUNTA(A2:E2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bel" wrote in message
...
Based on Judith and Bob, I modified that formula, so you can count how
many
the box checked.
=IF(COUNTA(A2:E2)=5, "5", IF(COUNTA(A2:E2)=0, "0", COUNTA(A2:E2)))

"Drew Halevy" wrote:

Greetings,

I do tech support (Exchange) for a school district, and I have been
approached with an Excel question by one of the users. I am fairly
skilled
with Excel, but formulas are a weakness for me. In a simplified form,
here
is the problem.

cells A1:E1 list the names of school

cells A2:E2 list a value (in this case, a checkmark)

that the end user is looking for in cell F2 is a formula that will look
at
cells A2:E2, and if all the cells are checked, will return one value, if
all
the cells are unchecked, will return a second value, and if only some
are
checked, will return a third value.

Any thoughts? I have tried playing with IF formulas, but have not had
much
success. Thanks In Advance. -Drew