Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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...
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •