Comparing cells but skipping the ones with zero in Excel-CodePudding

I have 7 variables which can in principle be between 0 and 10. (Up to) 2 of those are usually 0 and therefore "not interesting" and I would like to skip them when checking if the variables are the same. So, put differently, I would like to check if the non-zero values are the same.

An example, I have these 7 variable: 1 0 1 0 1 1 1, so the non-zero are all 1 and therefore the same.

Second example: 1 0 2 1 0 1 1, the non-zero variables are not the same, therefore False.

I thought I could reduce my 7 variable to just the 5 non-zero ones and then check if they are the same by using =IF(AND(b1=b2,b2=b3,b3=b4,b4=b5),1,0) But I am not sure how to get from the 7 to the 5 variables, even if I know the position of the zero values.

Here what I tried in Excel:

Columns A-G are the 7 variables, the input, in column G and H I figure out where the 0s are located with the idea to skip them BUT I cannot figure out how to get to b1-b5, I had to do it manually in the example.

Any ideas? Maybe an array formula?

CodePudding user response：

Use COUNTIFS to determine if 5 of the product of the sum divided by 5 appear in the range:

=--(COUNTIFS(A11:G11,SUM(A11:G11)/5)=5)

Of course if one has MINIFS:

=--(MAX(A11:G11)=MINIFS(A11:G11,A11:G11,">0")))

CodePudding user response：

This isn't much different than Scott's (part 2). I don't think there's any real advantages between the two other than different functions.

=COUNTIF(A1:G1,">0")*MAX(A1:G1)=SUM(A1:G1)

If you need a non-numeric solution, try something like this:

=LEN(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("",FALSE,A1:G1),"0",""),LEFT(SUBSTITUTE(TEXTJOIN("",FALSE,A1:G1),"0",""),1),""))=0