# Show highest consecutive cells

#### rex759

##### Well-known Member
I want to show 8 weeks of cells consecutively whose sum is the highest in the row.
For example; I have 10 weeks of numbers. I want to show the top 8 weeks that have the highest numbers, consecutively. In the example below, cells C2:J2 would be highlighted.

Book1.xls
ABCDEFGHIJ
1Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10
2\$574,028\$640,860\$680,397\$590,359\$656,498\$734,145\$689,923\$718,944\$864,188\$770,035
3
4
5Sumof1st8weeksSumof2nd8weeksSumof3rd8weeks
6\$5,285,154\$5,575,314\$5,704,489
7
8Highest8weeks
9\$5,704,489
10
11
Sheet1

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### pgc01

##### MrExcel MVP
Hi rex

This solution only uses the values in row 2. You can simplify it using auxilliary cells like the A9 in your post.

I used for the conditional formatting of A2 the formula:

=ABS(COLUMNS(\$A2:A2)-MATCH(MAX(SUBTOTAL(9,OFFSET(\$A2,0,ROW(INDIRECT("1:"&COLUMNS(\$A2:\$J2)-7))-1,1,8))),SUBTOTAL(9,OFFSET(\$A2,0,ROW(INDIRECT("1:"&COLUMNS(\$A2:\$J2)-7))-1,1,8)),0)-3.5)<4

Copy the format to the right

Choose the colour to highlight the cells.

#### rex759

##### Well-known Member
Wow! Thanks for the quick reply. I got it to work on my example, however, the real document has 26 cells to choose from, A2:Z2. Not sure how to alter the code to make it work.

#### pgc01

##### MrExcel MVP
Hi

I'm glad it's working. Just replace \$A2:\$J2 with \$A2:\$Z2 (2 times).

#### rex759

##### Well-known Member
Not pulling the correct results. Since the row is so long, I have the actual example and what the conditional format is pulling below. The conditiona fomat is highlighting Cells V2:AC2, it should be R2:Y2
Book1.xls
EFGHIJKLMN
102/10/0802/17/0802/24/0803/02/0803/09/0803/16/0803/23/0803/30/0804/06/0804/13/08
2\$574,028\$640,860\$680,397\$590,359\$656,498\$734,145\$689,923\$718,944\$864,188\$770,035
Sheet2
Book1.xls
OPQRSTU
104/20/0804/27/0805/04/0805/11/0805/18/0805/25/0806/01/08
2\$956,023\$1,009,944\$993,126\$1,017,237\$1,161,200\$1,182,043\$1,115,684
Sheet2
Book1.xls
106/08/0806/15/0806/22/0806/29/0807/06/0807/13/0807/20/0807/27/0808/03/08
2\$1,090,505\$1,025,095\$1,020,708\$1,015,091\$948,134\$913,968\$878,664\$869,814\$959,059
Sheet2

#### rex759

##### Well-known Member
Never mind! It works perfectly!

Thank you again

#### pgc01

##### MrExcel MVP

Remark: If there are 2 sequences of 8 cells that add up to the same amount (to the cent) then only the first is highlighted.

#### pgc01

##### MrExcel MVP
From PM: What if it was the top 6 weeks

Hi

In that case I'd change the last part of the formula to:

...-2,5)<3

#### rex759

##### Well-known Member
Hi

In that case I'd change the last part of the formula to:

...-2,5)<3

I figured it out. Once I put the formula in a cell and dissected it, I realized I had to change that part of the formula. It took me a bit though. Thanks for your help.

Replies
5
Views
137
Replies
18
Views
446
Replies
0
Views
194
Replies
1
Views
144
Replies
5
Views
244

1,195,743
Messages
6,011,393
Members
441,610
Latest member
Shakeable_Drip

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back