how to average multiple averages that can contain zeros

**BJB_88** · 01-15-2014, 11:54 PM

HI I have attached image illustrating what I am trying to achieve. I have a list of data in column E, if I take the average of [E4:E9] I get 11.67. However I require the averages to first be calculated in pairs as displayed in column G, I then need the averagers in column G to be averaged giving a final average of 13.75.

It all works fine until I get both points in a pair with missing data thus returning "#DIV/0!"

I have huge amounts of data I require this to be done for, so I was hoping there there would be a function that I could insert at the bottom of column E that would give the average of 13.75 so i can then drag it across all the data.

Thanks in advance

**:) Sixthsense :)** · 01-15-2014, 11:58 PM

Instead of picture please Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**BJB_88** · 01-16-2014, 01:19 AM

Hi sorry about that I have attached an example.

I would like to get the desired average (in green) from a fromula entered at the bottom of column E, which selects the entire range of data in column. I have tried AVERAGEIF and other combination, but i always get "#DIV/0!" or "11.67" which is not the correct average for what i require.

I am trying to take the avereage of pairs of data in column E and then average all those averages again in one cell as i have a huge amount of data.

Hope that makes sense.

average.xlsx

Thanks

**:) Sixthsense :)** · 01-16-2014, 01:41 AM

We keep the column-G helper column as it is

use the below formula in F4 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=AVERAGE(IF(ISNUMBER(G4:G9),G4:G9))

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

**BJB_88** · 01-16-2014, 02:03 AM

thanks sixsense, In my original sheet I only have column E (column G was for illustration purpose) and was wanting to try to avoid inserting rows like column G into my data sheet as it would be very time consuming. Is there a way to get the average of 13.75 directly from column E in a single formula?

Thanks

**FDibbins** · 01-16-2014, 02:06 AM

I will also take a look at your file, but I want to point out that taking an average of averages is mathematically incorrect. To be accurate, an average needs to be based on the original values

**FDibbins** · 01-16-2014, 02:12 AM

OK your file just proves my point. You have (effectively) 3 numbers, giving you an average of 11.67.

Then you combine those same 3 values in different combinations, average those combos into yet more combos, and then average that 2nd set of combos to give 13.75

Just because you combine values into different "groups" and work out averages of those groups, the overall average remains the average of the initial set of values

The correct answer here must still be 11.67

**BJB_88** · 01-16-2014, 02:28 AM

HI FDibbons, the reason for grouping them into pairs is each pair represents data from the same location it is just taken with different instruments to reduce the error. so every 2 data points represents a specific location within a designated zone. If one location in the zone has both instruments fail then it will effect the overall average of the zone.
hopefully that make sense

**:) Sixthsense :)** · 01-16-2014, 02:31 AM

That is why we are asking you to keep the column-G as helper as you shown in the sample file to avoid unnecessary complications

**kvsrinivasamurthy** · 01-16-2014, 07:33 AM

Pl see the attached file with ARRAY formula. Ans=13.75 using only E column

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 01-16-2014, 10:58 AM

Pl see file. One more example with Simple formula.

**BJB_88** · 01-16-2014, 07:06 PM

Thanks kvsrinivasamurthy

how to average multiple averages that can contain zeros

LinkBack

Thread Tools

Rate This Thread

Display

how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Re: how to average multiple averages that can contain zeros

Thread Information

Users Browsing this Thread

Similar Threads

Pivot Table - Multiple totals with averages and rank / Total to average with criteria

[SOLVED] Problem expressing multiple averages as a single average, math issue

[SOLVED] How do I get the average across multiple cells not in a range and not count the zeros/text

[SOLVED] Average for non-consecutive cells excluding zeros (even when they all contain zeros)

Averages excluding zeros

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions