Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    The problem with blanks (2000sr1a)

    This is driving me mad.

    I want to perform some simple aggregate functions on data in columns (one at a time) that contain some blanks and error values. I've consolidated all these to #N/A! or blank. I thought I'd cracked it using subtotal and a user defined function to filter.

    I used SUBTOTAL(1,NAfilter("Sheet1", 25)) and NAfilter is:

    Function NAfilter(strWshtName As String, colNumber As Byte)

    If Worksheets(strWshtName).AutoFilterMode = True Then Worksheets(strWshtName).ShowAllData
    Worksheets(strWshtName).Cells.AutoFilter Field:=colNumber, Criteria1:="<>#N/A",_ Operator:=xlAnd, Criteria2:="<>"
    Set NAfilter = Range(Worksheets(strWshtName).Cells(2, colNumber), Worksheets(strWshtName).Cells(2, colNumber).End(xlDown)).SpecialCells(xlCellTypeVis ible)

    End Function

    It turns out this only "works" if the filter defined in NAfilter happens to already be applied to sheet1(!), otherwise, although the function executes fine (established by stepping through it), Subtotal returns #N/A!.

    1) Why doesn't my function work.

    2) does anyone have any idea of how I can do this succesfully?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The problem with blanks (2000sr1a)

    You should know, that functions can only return values to the cell(s) they are called from. They cannot alter other cells. I would be *very* surprised if your autofilter trick would work at all, called from a function that is designed to return a value to a cell..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: The problem with blanks (2000sr1a)

    I suspected this, thus was surprised that it didn't return an error all the time - it seems that when the filters in the filter function are already applied, it works (for some unknown reason).

    So that's the answer to question 1! What about question 2? It still leaves me with the problem of what to do about this, and it must be a common problem. Named ranges were a possibility, but then they have such a low limit on their complexity that they are unusable in this situation, and it's just not feasible to manually apply the autofilters. There must be a way!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The problem with blanks (2000sr1a)

    Could you describe what the subtotal function has to do here?

    I assume you want to average the visible cells, ignoring errors, right?

    One way would be to use an array formula that incorporates the criteria you have set in the Autofilter. It would require you to put the Autofilter criteria into a range of cells however, since there is no formula way to get those.

    Another way could be to add a column that contains a boolean value that states whether or not a row is hidden and use that column in the array formula.
    To do that, use this defined name:

    Name: RowIsVisible
    Refersto:=(GET.CELL(17,INDIRECT("rc",FALSE))+0*NOW ())>0
    Now add a column into the autofiltered area and fill it with this formula:

    =RowIsVisible

    NOTE: NEVER COPY ANY CELL FROM THIS COLUMN TO ANOTHER SHEET, UNLESS YOU ARE USING EXCEL XP, OLDER VERSIONS WILL CRASH!!!

    Now control-shift-enter this array formula anywhere to calculate the average of the visible cells only (assuming the "visible" column is column [img]/forums/images/smilies/cool.gif[/img]:

    =AVERAGE(IF(NOT(ISNA(A2:A12))*(B2:B12),A2:A12,""))

    Remember: press control-shift-enter each time you edit this formula.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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