Comparing cells but skipping the ones with zero in Excel

user_4213982 0
Viewed 54

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:

enter image description here

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?

excel arrays
3 Answers

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)

enter image description here

Of course if one has MINIFS:

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

enter image description here

user_48515900

This formula has no limitations on the max or minimum number of 0 instances.

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

If anyone needed 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

enter image description here

user_117323200

Not sure if this is worth adding at this late stage, but you could consider using VAR like this:

=VAR(IF(A1:G1,A1:G1))=0

One slight benefit is that it should work for positive and negative numbers, although this isn't part of the question. It will error if all are zeroes, but this isn't supposed to happen.

enter image description here

user_38949170
Related