Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
i am trying to average a range of cells, in different spreadsheets within one
worksheet but it gives me the error #div/0 when one of the cells is blank. Any suggestions, thoughts, comments? |
#2
![]() |
|||
|
|||
![]() Try this =SUM(D7:E11)/COUNT(D7:E11) -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=277004 |
#3
![]() |
|||
|
|||
![]()
Not quite sure what formula you're using - =AVERAGE should discount blank
cells. Could you perhaps copy the formula that's giving you a problem into a post? "ucastores" wrote: i am trying to average a range of cells, in different spreadsheets within one worksheet but it gives me the error #div/0 when one of the cells is blank. Any suggestions, thoughts, comments? |
#4
![]() |
|||
|
|||
![]()
Hi
try =IF(COUNT(A1:A20),AVERAGE(A1:A20),"no cells filled") -- Regards Frank Kabel Frankfurt, Germany "ucastores" schrieb im Newsbeitrag ... i am trying to average a range of cells, in different spreadsheets within one worksheet but it gives me the error #div/0 when one of the cells is blank. Any suggestions, thoughts, comments? |
#5
![]() |
|||
|
|||
![]()
Hi
better IMHO: =AVERAGE(D7:E11) -- Regards Frank Kabel Frankfurt, Germany "anilsolipuram" schrieb im Newsbeitrag ... Try this =SUM(D7:E11)/COUNT(D7:E11) -- anilsolipuram --------------------------------------------------------------------- --- anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=277004 |
#6
![]() |
|||
|
|||
![]()
=AVERAGE(BVB!O41,HMC!O41,VVRMC!O41,VBMC!O41) This is the formula i have set
up. If i run it as it is i get the error, if i remove the HMC one, which is blank, i get an average. "Tom Hayakawa" wrote: Not quite sure what formula you're using - =AVERAGE should discount blank cells. Could you perhaps copy the formula that's giving you a problem into a post? "ucastores" wrote: i am trying to average a range of cells, in different spreadsheets within one worksheet but it gives me the error #div/0 when one of the cells is blank. Any suggestions, thoughts, comments? |
#7
![]() |
|||
|
|||
![]()
Tom,
in the last post i gave you the formula that i am using. I don't know if this is pertinent or not, but the cells i am pulling from all have =average formulas in them. What i am really trying to do is get an average of the averages. those formulas are all the same =average(05:035). that's oh 5 and oh 35, not zeros. "ucastores" wrote: =AVERAGE(BVB!O41,HMC!O41,VVRMC!O41,VBMC!O41) This is the formula i have set up. If i run it as it is i get the error, if i remove the HMC one, which is blank, i get an average. "Tom Hayakawa" wrote: Not quite sure what formula you're using - =AVERAGE should discount blank cells. Could you perhaps copy the formula that's giving you a problem into a post? "ucastores" wrote: i am trying to average a range of cells, in different spreadsheets within one worksheet but it gives me the error #div/0 when one of the cells is blank. Any suggestions, thoughts, comments? |
#8
![]() |
|||
|
|||
![]()
Howdy,
I played around with this a bit and I think your problem is that you have some ranges that are all blank. AVERAGE needs at least 1 non-blank cell to determine a result. That's why your AVERAGE function gives you the #DIV/0 error. You can get around the problem several different ways, including the suggestions others have put in this thread. If you want to keep using an AVERAGE function, though, you'll have to add a conditional to your formulas to take care of the event where all your cells in the range are blank. The reasoning here is to decide whether the range is empty or not before applying the AVERAGE function. One way to do it follows: =IF(COUNT(O5:O35)=0,0,AVERAGE(O5:O35)) Good luck, Tom Hayakawa "ucastores" wrote: Tom, in the last post i gave you the formula that i am using. I don't know if this is pertinent or not, but the cells i am pulling from all have =average formulas in them. What i am really trying to do is get an average of the averages. those formulas are all the same =average(05:035). that's oh 5 and oh 35, not zeros. "ucastores" wrote: =AVERAGE(BVB!O41,HMC!O41,VVRMC!O41,VBMC!O41) This is the formula i have set up. If i run it as it is i get the error, if i remove the HMC one, which is blank, i get an average. "Tom Hayakawa" wrote: Not quite sure what formula you're using - =AVERAGE should discount blank cells. Could you perhaps copy the formula that's giving you a problem into a post? "ucastores" wrote: i am trying to average a range of cells, in different spreadsheets within one worksheet but it gives me the error #div/0 when one of the cells is blank. Any suggestions, thoughts, comments? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Merging Cells but have each cell counted in the range of merged c. | Excel Worksheet Functions | |||
How do you delete one cell from a range of protected cells | Excel Worksheet Functions | |||
Excel-value in a range of cells for two cross references | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |