|
Formula Question for Excel 2003
Below is my original question and an answer from "Mike" on here but I have one more problem. When I use the formula if there is all zero's in the cells being averaged I get the DIV error. What can I do to stop that from happening Thanks! Hi, I'm not sure I fully understand but perhaps this =AVERAGE(IF(A2:B5<0,A2:B5)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "dernspiker" wrote: Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below. The last to rows equal zero. So when I average all the rows of course it sees those cells as zero. I tried using N/A but it stil sees those as a zero. Please help I have been working on this huge spreadsheet for a while now and really need to get it right. A B C 5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3) 5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4) 0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5) 0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6 |
Formula Question for Excel 2003
Try one of these. Still array entered...
=IF(ISERROR(AVERAGE(IF(A2:B5<0,A2:B5))),"",AVERAG E(IF(A2:B5<0,A2:B5))) If the numbers will only be positive numbers: =IF(COUNTIF(A2:B5,"0"),AVERAGE(IF(A2:B5<0,A2:B5) ),"") -- Biff Microsoft Excel MVP "Dana Lee" <Dana wrote in message ... Below is my original question and an answer from "Mike" on here but I have one more problem. When I use the formula if there is all zero's in the cells being averaged I get the DIV error. What can I do to stop that from happening Thanks! Hi, I'm not sure I fully understand but perhaps this =AVERAGE(IF(A2:B5<0,A2:B5)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "dernspiker" wrote: Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below. The last to rows equal zero. So when I average all the rows of course it sees those cells as zero. I tried using N/A but it stil sees those as a zero. Please help I have been working on this huge spreadsheet for a while now and really need to get it right. A B C 5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3) 5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4) 0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5) 0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6 |
Formula Question for Excel 2003
=IF(ISERROR(AVERAGE(IF(A2:B5<0,A2:B5))),"X",AVERA GE(IF(A2:B5<0,A2:B5)))
Again you must enter with CTRL+Shift+Enter best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dana Lee" <Dana wrote in message ... Below is my original question and an answer from "Mike" on here but I have one more problem. When I use the formula if there is all zero's in the cells being averaged I get the DIV error. What can I do to stop that from happening Thanks! Hi, I'm not sure I fully understand but perhaps this =AVERAGE(IF(A2:B5<0,A2:B5)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "dernspiker" wrote: Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below. The last to rows equal zero. So when I average all the rows of course it sees those cells as zero. I tried using N/A but it stil sees those as a zero. Please help I have been working on this huge spreadsheet for a while now and really need to get it right. A B C 5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3) 5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4) 0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5) 0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6 |
Formula Question for Excel 2003
Hi,
I know you are using 2003 but here is one of the reasons to someday consider 2007: your first formula would be a non-array: =AVERAGEIF(B2:B4,"<0") the second formula would also be a non-array but also much simplier: =IFERROR(AVERAGEIF(B2:B4,"<0"),"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Dana Lee" wrote: Below is my original question and an answer from "Mike" on here but I have one more problem. When I use the formula if there is all zero's in the cells being averaged I get the DIV error. What can I do to stop that from happening Thanks! Hi, I'm not sure I fully understand but perhaps this =AVERAGE(IF(A2:B5<0,A2:B5)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "dernspiker" wrote: Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below. The last to rows equal zero. So when I average all the rows of course it sees those cells as zero. I tried using N/A but it stil sees those as a zero. Please help I have been working on this huge spreadsheet for a while now and really need to get it right. A B C 5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3) 5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4) 0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5) 0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6 |
Formula Question for Excel 2003
"Dana Lee" wrote:
Below is my original question and an answer from "Mike" on here but I have one more problem. When I use the formula if there is all zero's in the cells being averaged I get the DIV error. What can I do to stop that from happening Thanks! Hi, I'm not sure I fully understand but perhaps this =AVERAGE(IF(A2:B5<0,A2:B5)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "dernspiker" wrote: Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below. The last to rows equal zero. So when I average all the rows of course it sees those cells as zero. I tried using N/A but it stil sees those as a zero. Please help I have been working on this huge spreadsheet for a while now and really need to get it right. A B C 5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3) 5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4) 0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5) 0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6 If your actual problem is for C2 to have the average of C3:C6, then what you have is fine, use =AVERAGE(C3:C6) and cells C5 and C6 will be ignored. If you want a true average, change the formulas to return a 0 instead of the "0.00%" and you will get the true average (0.5). |
| All times are GMT +1. The time now is 06:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com