if all values in a range are the same, return yes

[prochasska]

Hello,

I have a table in Excel where I want to have a formula which if all values every six cells in a column are equal, returns yes for the six cells and to return no if the values are not equal.
Here is an example.

The formula should return yes, all the cells in column N are the same

column N

Los Angeles

Los Angeles

Los Angeles

Los Angeles

Los Angeles

Los Angeles

<tbody>
</tbody>

The formula should return no, as not all values in the six cells are the same

column N

Los Angeles

Los Angeles

Los Angeles

Barcelona

Barcelona

Rome

<tbody>
</tbody>

I know that a countif function should be incorporated in an if function but I cannot get it right.
The formula returns yes only for the first of six cells if the condition is true and not for the six cells of the range.

I hope someone can help me create a formula which works in this case.

 

[prochasska]

Hello again,

I tried to apply both formulas on the real table and sadly, none is working.
I quess some of the logic behind the formulas escapes my understanding. (The only thing I changed is the range of the rows, should I change something else?)

If it is not much of a trouble, please can you explain the logic behind them?
Can't thank you enough.

 

[Special-K99]

Start with a blank sheet.
Copy the data you supplied from the forum into the sheet.
You get 3 columns, numbered rows, the cities and Yes/Nos.
Delete column A
Copy the formula into C1 and copy down.
Column C now reflects column B which is the exact output you supplied, so should work ok.

HOW IT WORKS:

I'm not very good at explaing complicated formulas

=IF(A1="","",IF(SUMPRODUCT((QUOTIENT(ROW(A$1:A$12000)-1,6)=QUOTIENT(ROW()-1,6))*(A$1:A$12000<>"")*(A$1:A$12000=A1))=6,"Yes","No"))

If column A is blank return a blank otherwise calculate the formula (this is because I don't know how many rows of data you have and the formula will return a Yes when there are 6 consecutive blanks in column A

SUMPRODUCT returns a 1 or 0 for each part of the formula. This formula is made up of 3 parts.

(QUOTIENT(ROW(A$1:A$12000)-1,6)=QUOTIENT(ROW()-1,6))
The first quotient assigns an increasing unique number to each group of 6 rows, rows 1-6 return 0, 7-12 return 1, 13-18 return 2 etc
The second quotient returns the group number to which that row beliongs, so row 3 will return 0 which is part of the first group of 6 rows.
If we compare these two results we know we are only ever comparing a row against its own group of 6 since the group number is unique.
This is just in case the row data is repeated further down the spreadsheet (outside of the row's group) which would cause incorrect totals.

(A$1:A$12000<>"")
Only non blanks in column A are considered.

(A$1:A$12000=A1))
Compare the current row against ALL data in the column. If we compare the row against all data we get a total of all matches in column A which would be incorrect.
But the QUOTIENT part of the formula restricts this comparison to only 6 rows.

So

(A$1:A$12000<>"") If there is data in column A and
(A$1:A$12000=A1)) the current row matches anything else in column A and
(QUOTIENT(ROW(A$1:A$12000)-1,6)=QUOTIENT(ROW()-1,6)) the group number for the current row equals the group number to which that row belongs, ie only compare the current row against 5 other rows.
then add 1 to a count

If the final count for that row totals 6 then all 6 rows must be the same

NOTE: This will only work for each group of 6 consecutive rows starting at row 1. If you move the data around, ie you move A3 to outside the group of 6 to say A7 then this formula won't work.
If the data doesn't start at row 1 then this formula won't work.

 

[Eric W]

You didn't say what happened with the formulas, but I suspect the reason my formula didn't work is because you didn't start in row 1, probably added some headers. Which is a good reason to give as much detail as you can in your original question, including the actual range of the data. In any case, it's easy enough to adapt my formula to work on a different range, like this:

	C	D
1	City	Yes/No
2	Los Angeles	Yes
3	Los Angeles	Yes
4	Los Angeles	Yes
5	Los Angeles	Yes
6	Los Angeles	Yes
7	Los Angeles	Yes
8	Los Angeles	No
9	Los Angeles	No
10	Dallas	No
11	Los Angeles	No
12	Los Angeles	No
13	Los Angeles	No
14	San Jose	Yes
15	San Jose	Yes
16	San Jose	Yes
17	San Jose	Yes
18	San Jose	Yes
19	San Jose	Yes
20	Oakland	No
21	Oakland	No
22	Oakland	No
23	Chico	No
24	Oakland	No
25	Oakland	No

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =IF(COUNTIF(OFFSET($C$2,FLOOR(ROW($C2)-ROW($C$2),6),0,6,1),C2)=6,"Yes","No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The way it works is by using OFFSET to figure out what 6-cell range to use. The first parameter in the OFFSET is the starting cell, $C$2, or the top cell in your column. The next parameter in OFFSET is how many rows down to go from that starting point. As you drag the formula down the column, this part of the formula "ROW($C2)-ROW($C$2)" will generate 0,1,2,3,4,5,6,7,8,9, etc. Note carefully the use of the $ signs. The FLOOR function will round down those values to the next lowest multiple of 6, so that list becomes 0,0,0,0,0,0,6,6,6,6,6,6,12, etc. So for the first 6 cells in that range, the row offset given to OFFSET is 0 ($C$2 + 0 rows is $C$2), for the next 6 it's 6 ($C$2+6 rows = $C$8), and so on. The column offset is a fixed 0. The next parameter to OFFSET is the number of rows to include in the range, which will always be 6, and the number of columns is always 1.

Now that we have the range to look at for any cell in the range, we can just use COUNTIF to see if every cell in the range is the same as the current cell.

One comment about OFFSET. OFFSET is a volatile function, meaning that it forces a recalculation of the formula every time anything on the sheet is recalculated. Usually Excel only recalculates a formula if something that the formula refers to has changed. So a volatile function will cause Excel to calculate more often, meaning a potentially slower sheet, meaning some people will tell you to avoid volatile functions at all costs. However, I've heard from Microsoft that OFFSET calculates very quickly, and it's not as bad as some people make it out to be. So try it out, and if it does slow things down, we can look at it again.

Hope this helps.!

 

[prochasska]

@ Special-K99, Eric W
Thank you for your time and thorough explanation.