0

I have a range of columns (A1,B1,C1,D1). These columns have values from 1 to 20. I have a formula in F1 like this:

Code:

=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))

This formula puts the value of U,X,Y and Z if any of these cells (A1,B1,C1 or D1) is bigger than 7.

but it can't handle if there are two cells bigger than >7.

What should I do, so if there are two columns bigger than 7, the end result will be a pair of U,X,Y or Z (comma separated).

Thanks.

1

This can be done with a formula, but it's rather long, and VBA is probably a better solution. Also, this won't handle the possible case where you have three values > 7. But I'll show you the formula method anyway.

Let's start out with the simpler case where there is only one cell > 7:

enter image description here

The formula in F1 is:

=IF(COUNTIF($A$1:D$1,">7")>1,"Double",IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))

The COUNTIF() portion counts the number of cells in the range A1:D1 that are greater than 7. If that total is greater than 1, the IF() returns "Double", which is just a placeholder for the bigger formula that comes later. If the total is not greater than 1, the formula uses your nested IF() statement to display the code for the cell that is > 7.

For the case where two cells are > 7, a nested IF() won't work. We need to know which columns are > 7. If we have this data,

enter image description here

First, we build an array that lists the columns > 7. This expression (A1:D1>7)*COLUMN(A1:D1) does that. The first part (A1:D1>7) asks which cells are > 7 and evaluates to the array {TRUE,FALSE,TRUE,FALSE}. The second part is the column numbers of A1:D1 or {1,2,3,4}. Multiplying these two arrays causes the logical values TRUE and FALSE to convert to 1 and 0, so the result is the array {1,0,3,0} which are the column numbers that have a value > 7.

Now we can use these numbers to lookup the column codes using INDEX():

INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))

Here LARGE() returns the second largest value in the array of column numbers > 7 (which is 1), and INDEX() uses that to return the first element in the array of letters - U.

Similarly, this expression gets the codes corresponding to the second and first largest column numbers separated by a comma:

INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)

Now we substitute all of that into the first equation above, replacing "Double". This formula in F1 is an array formula and must be entered with CtrlShiftEnter

=IF(COUNTIF($A$1:D$1,">7")>1,INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)),IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))

I told you this formula was going to be "rather long". I hope this helps.

1
  • 1
    Thanks a lot!!, @Bandersnatch for your valuable time and coding.
    – user761065
    Aug 29 '17 at 12:08
0

Here some other options I found alternative to the formulas above:

=IFERROR(LEFT(IF($A1>7,"U, ","")&IF($B1>7,"X, ","")&IF($C1>7,"Y, ","")&IF($D1>7,"Z, ",""),(COUNTIF($A1:$D1,">7")-1)*3+1),"")


{=TEXTJOIN(",",TRUE,IF(A1:D1>7,MID("UXYZ",COLUMN(A1:D1)-COLUMN(A1)+1,1),""))}


=MID(IF($A1>7,", U","")&IF($B1>7,", X","")&IF($C1>7,", Y","")&IF($D1>7,", Z",""),3,99)

If you have formulas returning "" :

=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)

I had to post a second answer because these formulas don't look good in the comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.