Forum Discussion
Averaging only Subtotals in a column
I am trying to average only subtotals in a column. The column includes the data for the subtotals also. I want to be able to only average the subtotals that have a value greater than zero. I came up with a formula that comes close to what I want, except it will average zeros also.
=IFERROR(SUBTOTAL(9,O160:O195)/(COUNT(O160:O195)-SUBTOTAL(3,O160:O195)),0)
Is there a way to accomplish only averaging subtotals. When writing this, I need to be able to add additional lines and subtotals in the column occasionally and have the formula include those when averaging.
Hi James,
Could you please clarify - that could be zeroes within the ranges where subtotal is not zero? If not when simple =AVERAGEIF(O160:O195,"<>0"), do you have subtotals within or not doesn't matter.
If some zeroes within the ranges for subtotals you'd like to calculate when it's not obvious how to do, at least for me. Have to play with tests.
I appreciate you responding. I have attached a sample of the file. You will see a column of numbers and the subtotal for those numbers. I want to use a formula to average only the subtotal numbers greater than zero. What is the best way to accomplish that?
Hi James,
Okay, i see. Thus you'd like to take average for the packages ignoring the values for the items within the packages.
The problems is what functions like SUBTOTAL and AGGREGATE don't select nested nested subtotals, they ignore them doing calculations on items.
For your case better to identify rows with subtotals and make calculations based on this ID. In your sample file that could be the word "package" in column A. I guess in real life these are some names which could have no common combination of letters which is not appears in items names. If so you may add something like "Total for ..."; or colon at the end of package name like "Package Name:"; or add one more helper columns with IDs for the packages. After that you may make your calculations filtering only the rows with ID which identified that's package row. Plus apply any other filters you need, in your case the packages which have non-zero cost.
For your sample file the formula is
=AVERAGEIFS(B1:B36,$A$1:$A$36,"pack*",B1:B36,">0")
where subtotals are filtered by column A selecting rows where the text starts from "pack". That could be "*:" for the words ending by colon, whatever.
File is attached.
