Forum Discussion
Averaging only Subtotals in a column
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.
Sergei,
I apologize for the delay. The formula you created worked perfectly. I have to change much of my spreadsheet, but the end results will be worth the reconfigurations.
Thank you so much!
James,
You are welcome. Since your worksheet is outlines and if you want to add some text only to packages names - just collapse your data to see only packages with their subtotals.
- if all your names are in column A copy it to any empty column, let say J
- in another empty column (K) in first cell add formula like ="Total for " & J1 where in J1 is your first package name;
- copy this cell (Ctrl+C)
- select entire range in K with empty cells below this cell (K2:..)
- press F5 (that's GoTo), at the bottom click Special and select Visible cells only
- press insert (Ctrl+V)
- select entire column K and copy it back as values
- goto J1 and ener formula =K1 here
- copy J1 and select entire range below, F5, Special, visible cells only, Ctrl+V
- copy column J as values back into the column A
Only packages names will be changed.
It's worth to expand your rows in between above steps to check if everything is correct (and collapse back before next step).