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

Topic

New Reply

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.

Reply | Quote

Replies

I might be going blind with old age, but I can’t see any attachments on your post!!

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

Try this in your example:

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote