# Subtotal functiion, cross checking if dataset is complete

1. ## 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?  Register To Reply

2. ## 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.  Register To Reply

3. ## 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?  Register To Reply

4. ## 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.  Register To Reply

5. ## 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%))  Register To Reply

6. ## Re: Subtotal functiion, cross checking if dataset is complete

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

7. ## 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?  Register To Reply