# Thread: Counting Non-Zero Values (with a twist!) (Excel 2000 S

1. ## Counting Non-Zero Values (with a twist!) (Excel 2000 S

I'm sure this is a pretty easy question for the experts, but it has me stumped. The attached sheet shows what I'm trying to do - basically within a series of data, count the number of weeks from the first non-zero value to the last non-zero value.

Let me introduce the twist - the data series may have zero values within (ie between the first non-zero value and the last non-zero value), but these intermediary ones must be counted - it's only the outliers that must not be counted.

I think the attached sheet explains it better than I can describe it here. If anyone has any ideas on how I can get the desired count for the number of weeks, I would be most grateful.

Thanks.

2. ## Re: Counting Non-Zero Values (with a twist!) (Excel 20

I might be going blind with old age, but I can't see any attachments on your post!! <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

3. ## Re: Counting Non-Zero Values (with a twist!) (Excel 20

No, Mike - I think it is I that is being challenged with old age!! Sorry about that folks...

Hopefully this time the attachment will work...

4. ## Re: Counting Non-Zero Values (with a twist!) (Excel 20

Here is some code you can try.
The assumptions are that each series will have a name, iow B3 to N3 will be named Series1, B4 to N4 will be Series2 etc.

Sub Calc_Weeks()
Dim NumWeeks As Integer
Dim myCell As Range
Dim myRow As String
Dim FirstCol As Integer
Dim LastCol As Integer
Dim myRangeName As String

myRangeName = InputBox("Enter Series name", "Input")

NumWeeks = 0
FirstCol = 0
LastCol = -1

For Each myCell In Range(myRangeName)
myRow = CStr(myCell.Row)
If myCell.Value <> 0 Then
If FirstCol = 0 Then
FirstCol = myCell.Column
End If

LastCol = myCell.Column
End If

NumWeeks = (LastCol - FirstCol) + 1
Next myCell
Range("O" & myRow) = NumWeeks
End Sub

Give it a try and see if it works for you.

5. ## Re: Counting Non-Zero Values (with a twist!) (Excel 2000 S

Try this in your example:

=MAX((B4:N4>0)*COLUMN(B4:N4))-MIN(IF((B4:N4>0)*COLUMN(B4:N4)=0,"",(B4:N4>0)*COLU MN(B4:N4)))+1

This is an array function and must be entered using Control+Shift+Enter

6. ## Re: Counting Non-Zero Values (with a twist!) (Excel 2000 S

Brilliant. Absolutely brilliant! Once again, Loungers save the day...thank you both very much!

7. ## Re: Counting Non-Zero Values (with a twist!) (Excel 2000 S

Thank you cpod for showing us yet another way to solve a problem.

My code worked, but your formula certainly seems more efficient in a number of ways.

I took your formula apart and learned how it works and it opens new opportunities.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•