|
Zero in Average problem
Excel2003
I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
There is no easy way. You would be better off doing your own average - use
the sum and countif formulas. Create a column that has the difference between each of the pairs. Then do a sum of that column and divide by the countif for that column (COUNTIF {range},"0"). "Gizmo" wrote: Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
Try this:
=AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4)) -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
Iwas in the middle of doing dhstein's suggestion when this reply popped up.
It works da bomb. I'd like to add a wrinkle if I could, I want to return "NA" if ALL the cells are 0. I get the #DIV/0! now. "T. Valko" wrote: Try this: =AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4)) -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
Try this:
=IF(COUNT(1/BD4,1/BE4,1/BH4,1/BI4,1/BL4,1/BM4)=0,"NA",AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4))) Hmmm... You may be better off using intermediate cells and then averaging those. -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Iwas in the middle of doing dhstein's suggestion when this reply popped up. It works da bomb. I'd like to add a wrinkle if I could, I want to return "NA" if ALL the cells are 0. I get the #DIV/0! now. "T. Valko" wrote: Try this: =AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4)) -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
That worked.
I am trying to apply this to another formula but its not working. Could you explain what each part of the formula is doing? ex: Average(IF(OR.... This is the formula i'm trying to adapt. =((MAX(C20,C24,C28)-MIN(C20,C24,C28))/1.693)/AVERAGE(C20,C24,C28)) This is what I changed it to based on this disscussion. =IF(COUNT(1/C20,1/C24,1/C28)=0,"NA",((MAX(IF(OR(C20=0,C24=0,C28=0),{""},(C 20,C24,C28))-MIN(IF(OR(C20=0,C24=0,C28=0),{""},(C20,C24,C28))/1.693/AVERAGE(IF(OR(C20=0),{""},C20),IF(OR(C24=0),{""},C 24),IF(OR(C28=0),{""},C28))))))) I get #VALUE! as a result. "T. Valko" wrote: Try this: =IF(COUNT(1/BD4,1/BE4,1/BH4,1/BI4,1/BL4,1/BM4)=0,"NA",AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4))) Hmmm... You may be better off using intermediate cells and then averaging those. -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Iwas in the middle of doing dhstein's suggestion when this reply popped up. It works da bomb. I'd like to add a wrinkle if I could, I want to return "NA" if ALL the cells are 0. I get the #DIV/0! now. "T. Valko" wrote: Try this: =AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4)) -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
I figured it out. Max and Min ignore blank cells so I don't need to add the
IF(OR( ... stuff. Here's the formula I came up with: =IF(COUNT(1/B20,1/B24,1/B28)=0,"#NA",((MAX(B20,B24,B28)-MIN(B20,B24,B28))/1.693)/B36) I referenced the first formula in B36, instead of writing it all out again. Thanks Valko "Gizmo" wrote: That worked. I am trying to apply this to another formula but its not working. Could you explain what each part of the formula is doing? ex: Average(IF(OR.... This is the formula i'm trying to adapt. =((MAX(C20,C24,C28)-MIN(C20,C24,C28))/1.693)/AVERAGE(C20,C24,C28)) This is what I changed it to based on this disscussion. =IF(COUNT(1/C20,1/C24,1/C28)=0,"NA",((MAX(IF(OR(C20=0,C24=0,C28=0),{""},(C 20,C24,C28))-MIN(IF(OR(C20=0,C24=0,C28=0),{""},(C20,C24,C28))/1.693/AVERAGE(IF(OR(C20=0),{""},C20),IF(OR(C24=0),{""},C 24),IF(OR(C28=0),{""},C28))))))) I get #VALUE! as a result. "T. Valko" wrote: Try this: =IF(COUNT(1/BD4,1/BE4,1/BH4,1/BI4,1/BL4,1/BM4)=0,"NA",AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4))) Hmmm... You may be better off using intermediate cells and then averaging those. -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Iwas in the middle of doing dhstein's suggestion when this reply popped up. It works da bomb. I'd like to add a wrinkle if I could, I want to return "NA" if ALL the cells are 0. I get the #DIV/0! now. "T. Valko" wrote: Try this: =AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4)) -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
Zero in Average problem
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Gizmo" wrote in message ... I figured it out. Max and Min ignore blank cells so I don't need to add the IF(OR( ... stuff. Here's the formula I came up with: =IF(COUNT(1/B20,1/B24,1/B28)=0,"#NA",((MAX(B20,B24,B28)-MIN(B20,B24,B28))/1.693)/B36) I referenced the first formula in B36, instead of writing it all out again. Thanks Valko "Gizmo" wrote: That worked. I am trying to apply this to another formula but its not working. Could you explain what each part of the formula is doing? ex: Average(IF(OR.... This is the formula i'm trying to adapt. =((MAX(C20,C24,C28)-MIN(C20,C24,C28))/1.693)/AVERAGE(C20,C24,C28)) This is what I changed it to based on this disscussion. =IF(COUNT(1/C20,1/C24,1/C28)=0,"NA",((MAX(IF(OR(C20=0,C24=0,C28=0),{""},(C 20,C24,C28))-MIN(IF(OR(C20=0,C24=0,C28=0),{""},(C20,C24,C28))/1.693/AVERAGE(IF(OR(C20=0),{""},C20),IF(OR(C24=0),{""},C 24),IF(OR(C28=0),{""},C28))))))) I get #VALUE! as a result. "T. Valko" wrote: Try this: =IF(COUNT(1/BD4,1/BE4,1/BH4,1/BI4,1/BL4,1/BM4)=0,"NA",AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4))) Hmmm... You may be better off using intermediate cells and then averaging those. -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Iwas in the middle of doing dhstein's suggestion when this reply popped up. It works da bomb. I'd like to add a wrinkle if I could, I want to return "NA" if ALL the cells are 0. I get the #DIV/0! now. "T. Valko" wrote: Try this: =AVERAGE(IF(OR(BD4=0,BE4=0),{""},BD4-BE4),IF(OR(BH4=0,BI4=0),{""},BH4-BI4),IF(OR(BL4=0,BM4=0),{""},BL4-BM4)) -- Biff Microsoft Excel MVP "Gizmo" wrote in message ... Excel2003 I have a page containing this formula in Column GF: =AVERAGE((BD4-BE4),(BH4-BI4),(BL4-BM4)) How can I check each range for 0 before performing the calculation? If any of the cells contain 0, I want to exclude it from the average. ex: BD4 =4,BE4 =2,BH4 =4,BI4 =2,BL4 =4,BM4 =2 Average = 2 If any of these cells = 0, remove it and its accompanying cell from the Average formula. BD4 =4,BE4 =2,BH4 =0,BI4 =2,BL4 =4,BM4 =2 Average = 2 |
| All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com