Subtotal functiion, cross checking if dataset is complete

**eastpak** · 02-07-2012, 12:59 PM

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?

**Haseeb A** · 02-07-2012, 01:29 PM

Assume A1:T10 contains data.

Try in B11 with CTRL+SHIFT+ENTER

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

Then copy across.

**eastpak** · 02-07-2012, 01:54 PM

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?

**Haseeb A** · 02-07-2012, 02:16 PM

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.

**Haseeb A** · 02-07-2012, 03:11 PM

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%))

**eastpak** · 02-08-2012, 07:59 AM

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

**eastpak** · 02-10-2012, 06:01 AM

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?