# Smart Analysis Macro

#### alexaronson

##### Active Member
I have a spread sheet that has item # in column A Customer in columnB starting on row 3 going to row 28,000+. I have data for these Customers that go from Column C to AL (26 rows in all).

What I want to do is develop a macro that will count the number of times I have consecutive zero points repeating for a given range in a row and place the output in AJ of that row.

I need to adjust the evaluation period or range in the macro from 2 to 8 as I test the data.
For example, if the macro range/evaluation period is 2 cells in the below example.
10 20 30 0 0 0 10 0 0 0 0 0 0 0 0 0 8 9 0 12.

Then the number of repeating evaluation periods would be 10.

If I change the evaluation period to 4 then the return value would be 6.

If I change the evaluation period to 8 then the return value would be 2.

To complicate this further, when the macro starts a new row, it needs to only start the evaluation after the first data point has a value. Meaning if I have empty white cells between the Customer and my first value, the macro will not treat those as zero values.

Any suggestions on how I could start this?

I currently have a solution that is tedious and requires a ton of formulas that is a pain to adjust.

Any suggestions on how I could start this?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
With your values in A:T, grouping variable in U. This formula works but does not handle blanks at front end. Will see if I can suss that out...

{=SUM(--(SUBTOTAL(9,OFFSET(A1,0,ROW(INDIRECT("1:"&COUNT(\$A1:\$T1)-\$U1+1))-1,1,U1))=0))}

HTH

Greg,

Thanks for working on this. I see that you are using an array. I know when I copy the formula i have to hit a sequence of keys before I can use it. Can you remind me what that sequence is?

Thanks

Ctrl+Shift+Enter

OK - those blanks at the beginning added a 2nd level of fun to this question. But this appears to work for me (same test bed as above).

{=SUM(--(SUBTOTAL(9,OFFSET(A1,0,ROW(INDIRECT("1:"&COUNT(OFFSET(A1:T1,0,(COLUMN(T1)-COLUMN(A1)+1)-COUNT(A1:T1),1,COUNT(A1:T1)))-U1+1))-1+(COLUMN(T1)-COLUMN(A1)+1)-COUNT(A1:T1),1,U1))=0))}

Hmmm,

I can get the formula to work. I click on the cell, enter the formula in the formula bar, hit Ctrl+Shift+Enter. The output I get is the formula.

Any suggestions?

Thanks for your help so far.

I assume you meant to type can't instead of can.

Is the cell formatted as text?

Edit -- you do not copy the braces. I include those to indicate that the formula is an array formula.

The cell format is General.

See my edit - are you including the braces?

I removed the brackets and changed the cell reference from row 1 to row 3 where the data is. However, I get #REF error.

Replies
14
Views
270
Replies
1
Views
699
Replies
1
Views
186
Replies
0
Views
180
Replies
5
Views
172

1,196,295
Messages
6,014,519
Members
441,826
Latest member
roudarreza

### 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