Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
I need a formula that will do this:
Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
Regarding:
Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below<<< Can you post some sample contents that contain multiple values? Are the multiple values all in one cell? Or in 1 to 5 cells? If yes, are they in contiguous row or col cells? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: I need a formula that will do this: Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
Ron Coderre has asked some valid questions in case you have specific
situation in mind that is not covered by a "one formula" solution. But I worked out a one-formula, special case, solution: Presume your values for conditions a False = 1 Team A = 2 Team B = 4 Team C = 8 Team D = 16 Assume that your total to test is in Cell C6, it's sum of False + team values involved (or zero if nothing entered yet) =IF(MOD(C6,2),IF(C616,"team d",IF(C68,"team c",IF(C63,"team b",IF(C62,"team a","")))),"") The MOD(C6,2) only returns true if value is odd, and only way to be odd is if the False value of 1 is part of the total. The rest of the IFs check from highest value to lowest to see which team to display. Even if all teams are involved, and False is included, then value is 31 which is 16, which would cause Team D to be displayed. By using powers of 2 for other values, we eliminate a problem might have had we used scalar values as 1,2,3,4, 5 (where 2+3 = 5 and 1+4=5). "Munchkin 76" wrote: I need a formula that will do this: Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
Hi Ron,
Ok lets just say column a has a list of formulas in it that will return values either False or Team A, Team B, Team C or Team D Then based on what those say, I need a value to appear in cell C2 So if column A looks like this: FALSE FALSE FALSE TEAM A FALSE TEAM B Then I need C2 to say TEAM B But if it had a TEAM C in there too I need C2 to say TEAM C If it had a TEAM D in there I need it to say TEAM D So the multiple values are all in separate cells in the same column. "Ron Coderre" wrote: Regarding: Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below<<< Can you post some sample contents that contain multiple values? Are the multiple values all in one cell? Or in 1 to 5 cells? If yes, are they in contiguous row or col cells? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: I need a formula that will do this: Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
OK....I think I understand correctly....
Try something like this: With each cell in A1:A5 containing either TEAM A, TEAM B, TEAM C, TEAM D, FALSE, blank, or any value. C2: =IF(COUNTIF(A1:A5,"FALSE"),CHOOSE(MAX((COUNTIF(A1: A5,{"*A","*B","*C","*D"})0)*{1,2,3,4})+1,"NONE"," TEAM A","TEAM B","TEAM C","TEAM D"),"NONE") Note: watch out for text wrap when copying that formula. If ALL are FALSE or there are no FALSE value...returns "NONE" Otherwise, returns the max team name. Examples: FALSE, RONC, TEAM D, TEAM B, TEAM A Returns: TEAM D TEAM C, TEAM C, TEAM D, TEAM B, TEAM A Returns: NONE (there are no FALSE values) HOWEVER... If FALSE can be ignored and you are only interested in team names.... Try this: C2: =CHOOSE(MAX((COUNTIF(A1:A5,{"*A","*B","*C","*D"}) 0)*{1,2,3,4})+1,"NONE","TEAM A","TEAM B","TEAM C","TEAM D") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: Hi Ron, Ok lets just say column a has a list of formulas in it that will return values either False or Team A, Team B, Team C or Team D Then based on what those say, I need a value to appear in cell C2 So if column A looks like this: FALSE FALSE FALSE TEAM A FALSE TEAM B Then I need C2 to say TEAM B But if it had a TEAM C in there too I need C2 to say TEAM C If it had a TEAM D in there I need it to say TEAM D So the multiple values are all in separate cells in the same column. "Ron Coderre" wrote: Regarding: Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below<<< Can you post some sample contents that contain multiple values? Are the multiple values all in one cell? Or in 1 to 5 cells? If yes, are they in contiguous row or col cells? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: I need a formula that will do this: Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
Ron,
Worked a treat many thanks!!! "Ron Coderre" wrote: OK....I think I understand correctly.... Try something like this: With each cell in A1:A5 containing either TEAM A, TEAM B, TEAM C, TEAM D, FALSE, blank, or any value. C2: =IF(COUNTIF(A1:A5,"FALSE"),CHOOSE(MAX((COUNTIF(A1: A5,{"*A","*B","*C","*D"})0)*{1,2,3,4})+1,"NONE"," TEAM A","TEAM B","TEAM C","TEAM D"),"NONE") Note: watch out for text wrap when copying that formula. If ALL are FALSE or there are no FALSE value...returns "NONE" Otherwise, returns the max team name. Examples: FALSE, RONC, TEAM D, TEAM B, TEAM A Returns: TEAM D TEAM C, TEAM C, TEAM D, TEAM B, TEAM A Returns: NONE (there are no FALSE values) HOWEVER... If FALSE can be ignored and you are only interested in team names.... Try this: C2: =CHOOSE(MAX((COUNTIF(A1:A5,{"*A","*B","*C","*D"}) 0)*{1,2,3,4})+1,"NONE","TEAM A","TEAM B","TEAM C","TEAM D") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: Hi Ron, Ok lets just say column a has a list of formulas in it that will return values either False or Team A, Team B, Team C or Team D Then based on what those say, I need a value to appear in cell C2 So if column A looks like this: FALSE FALSE FALSE TEAM A FALSE TEAM B Then I need C2 to say TEAM B But if it had a TEAM C in there too I need C2 to say TEAM C If it had a TEAM D in there I need it to say TEAM D So the multiple values are all in separate cells in the same column. "Ron Coderre" wrote: Regarding: Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below<<< Can you post some sample contents that contain multiple values? Are the multiple values all in one cell? Or in 1 to 5 cells? If yes, are they in contiguous row or col cells? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: I need a formula that will do this: Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula Help
Thanks for the feedback.....I'm glad I could help.
*********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: Ron, Worked a treat many thanks!!! "Ron Coderre" wrote: OK....I think I understand correctly.... Try something like this: With each cell in A1:A5 containing either TEAM A, TEAM B, TEAM C, TEAM D, FALSE, blank, or any value. C2: =IF(COUNTIF(A1:A5,"FALSE"),CHOOSE(MAX((COUNTIF(A1: A5,{"*A","*B","*C","*D"})0)*{1,2,3,4})+1,"NONE"," TEAM A","TEAM B","TEAM C","TEAM D"),"NONE") Note: watch out for text wrap when copying that formula. If ALL are FALSE or there are no FALSE value...returns "NONE" Otherwise, returns the max team name. Examples: FALSE, RONC, TEAM D, TEAM B, TEAM A Returns: TEAM D TEAM C, TEAM C, TEAM D, TEAM B, TEAM A Returns: NONE (there are no FALSE values) HOWEVER... If FALSE can be ignored and you are only interested in team names.... Try this: C2: =CHOOSE(MAX((COUNTIF(A1:A5,{"*A","*B","*C","*D"}) 0)*{1,2,3,4})+1,"NONE","TEAM A","TEAM B","TEAM C","TEAM D") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: Hi Ron, Ok lets just say column a has a list of formulas in it that will return values either False or Team A, Team B, Team C or Team D Then based on what those say, I need a value to appear in cell C2 So if column A looks like this: FALSE FALSE FALSE TEAM A FALSE TEAM B Then I need C2 to say TEAM B But if it had a TEAM C in there too I need C2 to say TEAM C If it had a TEAM D in there I need it to say TEAM D So the multiple values are all in separate cells in the same column. "Ron Coderre" wrote: Regarding: Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below<<< Can you post some sample contents that contain multiple values? Are the multiple values all in one cell? Or in 1 to 5 cells? If yes, are they in contiguous row or col cells? *********** Regards, Ron XL2002, WinXP "Munchkin 76" wrote: I need a formula that will do this: Lets just say there are five values FALSE Team A Team B Team C Team D Here are the value properties False - nothing Team A - higher than false but lower than B C or D Team B - higher than false and A but lower than C or D Team C - higher than false, A or B but lower than D Team D - higher than all other teams Other formulas have created a list that could contain one of these 5 values or a multiple of them and it needs to return a value based on below: If it contains false plus team a, then it returns team a If it contains false plus team a and team b, then it returns team b If it contains false plus team b and team c, then it returns team c If it contains false plus team c and team d, then it returns team d if it contains false plus team a and team b and team c and team d, it returns team d How do you do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
Using Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
EDIT FORMULA BAR in excel 2003? why not? where is it? | Excel Worksheet Functions | |||
Excel instance used with "Export to Microsoft Excel" option in Internet Explorer | Excel Discussion (Misc queries) |