+ Reply to Thread
Results 1 to 7 of 7

Subtotal functiion, cross checking if dataset is complete

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Subtotal functiion, cross checking if dataset is complete

    I am trying to combine an Average Subtotal function, excluding blanks, with checking whether a dataset is complete

    Columns B to T contains the data. Not every row is populated though (left blank) and within a row those inputs which are zero are also left blank
    Column A is summing up columns B to T

    What i am struggling to create is a Subtotal function for each column which gives me an average, but only for those rows where the data in column A equals to 100%, including the blanks (as zeros) for those rows were column A is 100%

    Is this possible?

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Subtotal functiion, cross checking if dataset is complete

    Assume A1:T10 contains data.

    Try in B11 with CTRL+SHIFT+ENTER

    =AVERAGE(IF($A1:$A10=100%,--(0&B1:B10)))

    Then copy across.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Subtotal functiion, cross checking if dataset is complete

    doesn't work. and this would also not pick up just the filtered data, therefore i thought i have to work with a Subtotal function. not?

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Subtotal functiion, cross checking if dataset is complete

    Can you please post a sample file with desired results? Remove all confidential data if there is,

    On bottom, Go Advanced, then click on Manage Attachments, on the top Add files, browse & upload it.

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Subtotal functiion, cross checking if dataset is complete

    May be this...

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B11)-ROW(B2),)),--($A2:$A11=100%))/SUMPRODUCT(SUBTOTAL(3,OFFSET($A2,ROW($A2:$A11)-ROW($A2),)),--($A2:$A11=100%))

  6. #6
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Subtotal functiion, cross checking if dataset is complete

    Fantastic - this one seems to work fine. sooooo many thanks

  7. #7
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Subtotal functiion, cross checking if dataset is complete

    sorry for bothering again. there seems to be a small issue.

    when i filter the data and have a small sample, the formula works absolutely fine.
    however if the data set is larger, containing lots of lines where row A is below 100% (hence this should be disregarded), the sum of the results from the formula above aross the various columns start to fall below 100%. why is that?

+ 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