Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
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 . |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula Question...PLEASE PLEASE help! | Excel Worksheet Functions | |||
Formula Question IF/Lookup???? | Excel Worksheet Functions | |||
Formula Question.....PLEASE PLEASE help! | Excel Discussion (Misc queries) | |||
Parsing Data w/ a Formula (another question) | Excel Worksheet Functions |