+ Reply to Thread
Results 1 to 8 of 8

Change Formula reference depending on filtered cells

  1. #1
    Registered User
    Join Date
    06-17-2008
    Posts
    5

    Change Formula reference depending on filtered cells

    Hi everyone. I'm new here. Hope you can help ne out.

    Is it possible to change the cell reference of a formula depending on the filtered cells?
    My data has about 1200 rows x 7 columns. Most of the time, I filter my data (say, by date).
    So can I automatically update the formula so it calculates only on the filtered data?
    Thanks!

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi Gouki,

    Welcome to the forum.

    The SUBTOTAL function is what you're after. See here for more info.

    HTH

    Robert

  3. #3
    Registered User
    Join Date
    06-17-2008
    Posts
    5
    Hi Robert,

    Thanks and it worked well, though I have a bit complicated formulae.
    But the key is that SUBTOTAL function.
    Great help indeed!

    Regards,
    Gouki

    Quote Originally Posted by Trebor76
    Hi Gouki,

    Welcome to the forum.

    The SUBTOTAL function is what you're after. See here for more info.

    HTH

    Robert

  4. #4
    Registered User
    Join Date
    06-17-2008
    Posts
    5
    Hi Robert,

    I have a problem with one formula.

    My original formula is

    {=AVERAGE(IF(R4:R1503<>0, R4:R1503,""))} , an array formula

    which returns the average of the non-zero values (Zero values must not be included). How can I use the subgroup formula for this one. I have tried but an error value comes out. I'm still working on this now if I can solve it.

    Other simpler formulae are already working with the subroup function.

    Any help is highly appreciated from any member. Thanks.

    Regards,
    Gouki



    Quote Originally Posted by Trebor76
    Hi Gouki,

    Welcome to the forum.

    The SUBTOTAL function is what you're after. See here for more info.

    HTH

    Robert
    Last edited by Gouki; 06-18-2008 at 03:28 AM.

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi Gouki,

    You could try filtering out zeros (i.e. from the Custom Autofilter dialog box selecting does not equal from the first listbox and then entering 0 in the second listbox and clicking OK) and then using the formula (changing the ranges to suit):

    =SUBTOTAL(9,A2:A100)/SUBTOTAL(3,A2:100)

    Note that as the COUNTA function only counts the number of cells that are not empty it caters for any null entries in the selected range.

    HTH

    Robert

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by Gouki
    My original formula is

    {=AVERAGE(IF(R4:R1503<>0, R4:R1503,""))}
    Try this "array formula"

    =AVERAGE(IF(R4:R1503<>0,IF(SUBTOTAL(2,OFFSET(R4,ROW(R4:R1503)-ROW(R4),0)),R4:R1503)))

  7. #7
    Registered User
    Join Date
    06-17-2008
    Posts
    5
    Hi Robert,

    Thanks again. I am yet to try this one.

    Quote Originally Posted by Trebor76
    Hi Gouki,

    You could try filtering out zeros (i.e. from the Custom Autofilter dialog box selecting does not equal from the first listbox and then entering 0 in the second listbox and clicking OK) and then using the formula (changing the ranges to suit):

    =SUBTOTAL(9,A2:A100)/SUBTOTAL(3,A2:100)

    Note that as the COUNTA function only counts the number of cells that are not empty it caters for any null entries in the selected range.

    HTH

    Robert

  8. #8
    Registered User
    Join Date
    06-17-2008
    Posts
    5
    Hi daddylonglegs,

    Thanks and your suggestion works well!

    Quote Originally Posted by daddylonglegs
    Try this "array formula"

    =AVERAGE(IF(R4:R1503<>0,IF(SUBTOTAL(2,OFFSET(R4,ROW(R4:R1503)-ROW(R4),0)),R4:R1503)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1