Have an Averages Problem that seems to be a Riddle I cant solve.

I have a large task list that is measuring % complete here are my Parameters:

- A list I am making has 23 different categories

- Each Category has 20 spots "from 1 to 20 " to Consecutively input items and then a box on each of the 20 indicate your % Complete.

- Each Category has a Totals Columns or Summary for the 20 Qty's and etc...Including the % Complete for each of the 20 Items in the Category.

- For Example My Summary will calculate the total averages of my Percent complete using this formula: =AVERAGEIF(F39:F59,"<>0")

- I did this so that my Averages are not Skewed as a result of not counting the "0" in my averages.


I have no problems with my Totals with the exception of getting a #Div/0! in my Totals.

So to remedy that issue, I tried to change my Formula to =AVERAGEIF(F39:F59,"<>#DIV/0!")

I am building this for Several groups of Tasks that have 20 Columns Each for Example: 4 Groups of 20 Columns and then a Master that totals the 4 Groups Combined as a Total Build Summary of Percent Complete.

in my Groups Summary I am using the formula: =SUM(F9,F38,F67,F90,)/SUMPRODUCT(--(LARGE((F9,F38,F67,F90,),ROW(INDIRECT("1:"&COUNT(F9,F38,F67,F90,))))<>0))

I did this so that I can choose Selected Cells as the =AVERAGEIF(F39:F59,"<>#DIV/0!") does not count specific selected cells.

SO after that very long explanation, Here is my Problem.

- When using the formula =AVERAGEIF(F39:F59,"<>#DIV/0!") - My total Provides a "0" and if I make no entry for the category it goes to "#DIV/0!", or if I my employees accidentally enter more than one "0" it will begin to average it
- When using the formula =AVERAGEIF(F39:F59,"<>0") - My total provides a "#DIV/0!" , So if there are no items entered into that Category we have to delete the formula in it's entirety to show no entry to remove the #DIV/0!

- My Group Summary can only accept a Percentage or Zero, but it will go directly to a "#DIV/0!" if there is a "#DIV/0!" anywhere under the category totals.

So seeing I have many employees, I want to avoid the manual deletion of any formulas in my list.

MY QUESTION:

- Can someone help me create a formula for Averages, so that if there are no entries in the category or if the categories % Complete is at zero "0" to have the Total SUM Cell either show a "0" or keep the cell blank?

as a #DIV/0! is messing up my Master Total...

Please and thank you!!!

Hello,
Please attach an example file with your data structure

Work in progress.

I would (probably) have done this all the way to the bottom IF the blocks were the same size. Since they differ, it's a bit manual... so you can do it.

I deleted all the values in G down from row 85 to get rid of the #DIV/0 errors (see point 2 & 3)

1. Change H5 to:
=V5+SUM(INDEX(W5:PU5,,SEQUENCE((COLUMNS(W5:PU5)+1)/2,,1,2)))
and copy down

2. Change G4 to:
=IFERROR(AVERAGE(G5:G32),0)
then you'll get zeros, not errors.

3. Change G3 to:
=SUMPRODUCT((--ISNUMBER(SEARCH("Division",B4:B433)))*G4:G433)/SUMPRODUCT((--ISNUMBER(SEARCH("Division",B4:B433)))*(G4:G433>0))

4. All you need to do is to copy this:
=IFERROR(AVERAGE(G5:G32),0)

down the range in the right cells, adjusting the ranges (red) accordingly.

Note: hopefully you have SEQUENCE in your Excel version. Since (AFAIK) there is no such thing as Excel 2020... I'm hoping you have a recent version!!

I do have the most recent version, I will give this a try and let you know how it turns out


thank you.

Hi.

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.