| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I average a group of cells and ignore a #div/0! error?
Him You Them Us Me
12 10 8 6 12 11 7 4 11 9 6 9 average 11.6 10 7 6.3 #DIV/0! How can you average the cells in the bottom row and ignore the #div/0! Error to get the answer 8.76? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I average a group of cells and ignore a #div/0! error?
Assuming you have the totals in row5 B5 to F5 try the below formula
Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(ISERROR(B5:F5),0,B5:F5))/COUNTIF(B5:F5,"0") If this post helps click Yes --------------- Jacob Skaria "Scott R" wrote: Him You Them Us Me 12 10 8 6 12 11 7 4 11 9 6 9 average 11.6 10 7 6.3 #DIV/0! How can you average the cells in the bottom row and ignore the #div/0! Error to get the answer 8.76? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I average a group of cells and ignore a #div/0! error?
If you are using 2007 try =AVERAGEIF()
-- If this post helps click Yes --------------- Jacob Skaria "Scott R" wrote: Him You Them Us Me 12 10 8 6 12 11 7 4 11 9 6 9 average 11.6 10 7 6.3 #DIV/0! How can you average the cells in the bottom row and ignore the #div/0! Error to get the answer 8.76? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I average a group of cells and ignore a #div/0! error?
Try this array formula** :
=AVERAGE(IF(ISNUMBER(A5:E5),A5:E5)) ** 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. -- Biff Microsoft Excel MVP "Scott R" <Scott wrote in message ... Him You Them Us Me 12 10 8 6 12 11 7 4 11 9 6 9 average 11.6 10 7 6.3 #DIV/0! How can you average the cells in the bottom row and ignore the #div/0! Error to get the answer 8.76? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I average a group of cells and ignore a #div/0! error?
Average the source data and not the averages. Note that averges of averages
can get a little troublesome depending what you want to do. =average(B2:F4) If you have 100 values of 1 it's average is 1. If you have 1 value of 100 it's average is 100. Average of Averages is (1 + 100) / 2 = 101 / 2 = 50.5 Total average is (1+1+1+... + 100) / 101 = 200/101 = 1.98 Obvioulsly not the same... -- HTH... Jim Thomlinson "Scott R" wrote: Him You Them Us Me 12 10 8 6 12 11 7 4 11 9 6 9 average 11.6 10 7 6.3 #DIV/0! How can you average the cells in the bottom row and ignore the #div/0! Error to get the answer 8.76? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| zero value cells/blank cells causing error in AVERAGE? | Excel Worksheet Functions | |||
| average with out rounding up in a group of cells | Excel Worksheet Functions | |||
| from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
| How do I ignore cells with errors when calculating an average? | Excel Worksheet Functions | |||
| calculate average in percentage coloumn with #DIV/! (ignore error | Excel Discussion (Misc queries) | |||
![Scott R[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
