Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
Hi all,
I need help on a formula. I have a cell Z3 which is YTD. It calcuates the average from cells A3:Y3 (months of the year). So far so good. The cells A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is calcuating all of the monthly averages which is working ok. The problem is if I have not entered any weekly data for the future months I am getting the #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the help solutions. But it looks like I can't use their solution because I am using a range of cells. This is what I attempted: =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error. Any ideas?? Any information is greatly appreciated!!!!! -- Bruce DiPaola |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
Hi Bruce,
=IF(ISERROR(YourFormula),"",YourFormula) -- Kind regards, Niek Otten "Bruce D." wrote in message ... Hi all, I need help on a formula. I have a cell Z3 which is YTD. It calcuates the average from cells A3:Y3 (months of the year). So far so good. The cells A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is calcuating all of the monthly averages which is working ok. The problem is if I have not entered any weekly data for the future months I am getting the #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the help solutions. But it looks like I can't use their solution because I am using a range of cells. This is what I attempted: =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error. Any ideas?? Any information is greatly appreciated!!!!! -- Bruce DiPaola |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
This solution may work for you- since you're using a simple average you
can change the formula to SUMIF(range, "<0", range) / COUNTIF(range,"<0") This sums non-zero values and divides by the number of non-zero values. Use this ONLY on a continuous range of numbers, because COUNTIF(range,"<0") will count blank cells. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
Hi Niek,
I entered =IF(ISERROR(AVERAGE(C3:Y3),"",AVERAGE(C3:Y3) and the cursor moves to the "" quotes. -- Bruce DiPaola "Niek Otten" wrote: Hi Bruce, =IF(ISERROR(YourFormula),"",YourFormula) -- Kind regards, Niek Otten "Bruce D." wrote in message ... Hi all, I need help on a formula. I have a cell Z3 which is YTD. It calcuates the average from cells A3:Y3 (months of the year). So far so good. The cells A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is calcuating all of the monthly averages which is working ok. The problem is if I have not entered any weekly data for the future months I am getting the #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the help solutions. But it looks like I can't use their solution because I am using a range of cells. This is what I attempted: =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error. Any ideas?? Any information is greatly appreciated!!!!! -- Bruce DiPaola |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
I think the iserror formula will be simpler if you already have the spreadsheet set up. Just make sure that A3 - Y3 have =if(iserror(4 week average formula)," ",4 week average formula). Dave O Wrote: This solution may work for you- since you're using a simple average you can change the formula to SUMIF(range, "<0", range) / COUNTIF(range,"<0") This sums non-zero values and divides by the number of non-zero values. Use this ONLY on a continuous range of numbers, because COUNTIF(range,"<0") will count blank cells. -- Randy Davis ------------------------------------------------------------------------ Randy Davis's Profile: http://www.excelforum.com/member.php...o&userid=29973 View this thread: http://www.excelforum.com/showthread...hreadid=496654 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
Hi,
In A3 enter the formula, =IF(ISERROR(AVERAGE(4-week range for A3)),"",AVERAGE(4-week range for A3)) where "4-week range for A3" is the range of cells containing tthe data for calculating the average in cell A3. Drag the formula across to Y3. The formula in Z3 is =AVERAGE(A3:Y3). and it should behave properly. Regards, B. R. Ramachandran In fact "Bruce D." wrote: Hi all, I need help on a formula. I have a cell Z3 which is YTD. It calcuates the average from cells A3:Y3 (months of the year). So far so good. The cells A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is calcuating all of the monthly averages which is working ok. The problem is if I have not entered any weekly data for the future months I am getting the #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the help solutions. But it looks like I can't use their solution because I am using a range of cells. This is what I attempted: =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error. Any ideas?? Any information is greatly appreciated!!!!! -- Bruce DiPaola |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
It should be " ". a.k.a. "[space]" -- Randy Davis ------------------------------------------------------------------------ Randy Davis's Profile: http://www.excelforum.com/member.php...o&userid=29973 View this thread: http://www.excelforum.com/showthread...hreadid=496654 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
Hi B.R.
I tried your solution and it works perfectly. You are the man... Many thanks.... -- Bruce DiPaola "B. R.Ramachandran" wrote: Hi, In A3 enter the formula, =IF(ISERROR(AVERAGE(4-week range for A3)),"",AVERAGE(4-week range for A3)) where "4-week range for A3" is the range of cells containing tthe data for calculating the average in cell A3. Drag the formula across to Y3. The formula in Z3 is =AVERAGE(A3:Y3). and it should behave properly. Regards, B. R. Ramachandran In fact "Bruce D." wrote: Hi all, I need help on a formula. I have a cell Z3 which is YTD. It calcuates the average from cells A3:Y3 (months of the year). So far so good. The cells A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is calcuating all of the monthly averages which is working ok. The problem is if I have not entered any weekly data for the future months I am getting the #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the help solutions. But it looks like I can't use their solution because I am using a range of cells. This is what I attempted: =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error. Any ideas?? Any information is greatly appreciated!!!!! -- Bruce DiPaola |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
DIV/0 error
Sorry, I missed the final quote
-- Kind regards, Niek Otten "Bruce D." wrote in message ... Hi Niek, I entered =IF(ISERROR(AVERAGE(C3:Y3),"",AVERAGE(C3:Y3) and the cursor moves to the "" quotes. -- Bruce DiPaola "Niek Otten" wrote: Hi Bruce, =IF(ISERROR(YourFormula),"",YourFormula) -- Kind regards, Niek Otten "Bruce D." wrote in message ... Hi all, I need help on a formula. I have a cell Z3 which is YTD. It calcuates the average from cells A3:Y3 (months of the year). So far so good. The cells A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is calcuating all of the monthly averages which is working ok. The problem is if I have not entered any weekly data for the future months I am getting the #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the help solutions. But it looks like I can't use their solution because I am using a range of cells. This is what I attempted: =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error. Any ideas?? Any information is greatly appreciated!!!!! -- Bruce DiPaola |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I prevent a #DIV/0! error from plotting on a chart | Charts and Charting in Excel | |||
DIV/0! error in SumProduct formula with no division | Excel Worksheet Functions | |||
replace "#DIV/0!" error with blanks | Excel Worksheet Functions | |||
# DIV/0! error in Excel | Excel Discussion (Misc queries) | |||
"Average" with error DIV/0 | Excel Discussion (Misc queries) |