Microsoft Excel Formula Help

[Guest]

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?

 

[Guest]

Regardingr 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

 

[Guest]

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 are:
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(C6>16,"team d",IF(C6>8,"team c",IF(C6>3,"team
b",IF(C6>2,"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).

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Ron,

Worked a treat many thanks!!!

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

 

[Guest]

Thanks for the feedback.....I'm glad I could help.


***********
Regards,
Ron

XL2002, WinXP