Avg numbers but not #VALUE
I have a column of calculated values (time). In that column, there are a few
#VALUEs due to unavailable data for that particular cell. How do I average the column, but not include cells with #VALUES in the average? I also need zero included in the average. eg, my column, B1:B4 consists of: 0:02 0:04 #VALUE 0:00 I would like the average to return a value of 0:02 ((0:02 + 0:04 + 0:00)/3) Thanx |
Avg numbers but not #VALUE
Try to use the following arrary function (end with Ctrl+Shift+Enter)
=AVERAGE(IF(NOT(ISERROR(Sheet1!$A$1:$A$4)),Sheet1! $A$1:$A$4)) -- Best regards, --- Yongjun CHEN ================================= XLDataSoft - Data Analysis Expert, Excel/VBA Specialist - - - - www.XLDataSoft.com - - - - Free Excel-Based Data Processing Tool is Available for Download Free Excel / VBA Training Materials is Available for Download ================================= "Robin" wrote in message ... I have a column of calculated values (time). In that column, there are a few #VALUEs due to unavailable data for that particular cell. How do I average the column, but not include cells with #VALUES in the average? I also need zero included in the average. eg, my column, B1:B4 consists of: 0:02 0:04 #VALUE 0:00 I would like the average to return a value of 0:02 ((0:02 + 0:04 + 0:00)/3) Thanx |
Avg numbers but not #VALUE
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5)) A better way would likely be to trap your calculated values so that they didn't return an error - it's bad design to make errors "normal". So, for an example, instead of =A1 + B1 which gives a #VALUE! error if either A1 or B1 are text (including space characters, which some users use to "blank" a cell), you could use =IF(COUNT(A1:B1)=2,A1+B1,"") which will return "" instead of #VALUE! if A1 or B1 is text. Since AVERAGE() ignores text, you can then just use =AVERAGE(A1:A5) In article , Robin wrote: I have a column of calculated values (time). In that column, there are a few #VALUEs due to unavailable data for that particular cell. How do I average the column, but not include cells with #VALUES in the average? I also need zero included in the average. eg, my column, B1:B4 consists of: 0:02 0:04 #VALUE 0:00 I would like the average to return a value of 0:02 ((0:02 + 0:04 + 0:00)/3) Thanx |
Avg numbers but not #VALUE
Try something like this:
For values in A1:A10 B1: =SUMIF(A1:A10,"<#VALUE!",A1:A10)/COUNT(A1:A10) or...perhaps.... B1: =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) Note: For that array formula, hold down [Ctrl] and [Shift] when you press [Enter]. Does that help? Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Robin" wrote: I have a column of calculated values (time). In that column, there are a few #VALUEs due to unavailable data for that particular cell. How do I average the column, but not include cells with #VALUES in the average? I also need zero included in the average. eg, my column, B1:B4 consists of: 0:02 0:04 #VALUE 0:00 I would like the average to return a value of 0:02 ((0:02 + 0:04 + 0:00)/3) Thanx |
Avg numbers but not #VALUE
Thanx Ron, yes both of those worked and I can work with each.
"Ron Coderre" wrote: Try something like this: For values in A1:A10 B1: =SUMIF(A1:A10,"<#VALUE!",A1:A10)/COUNT(A1:A10) or...perhaps.... B1: =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) Note: For that array formula, hold down [Ctrl] and [Shift] when you press [Enter]. Does that help? Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Robin" wrote: I have a column of calculated values (time). In that column, there are a few #VALUEs due to unavailable data for that particular cell. How do I average the column, but not include cells with #VALUES in the average? I also need zero included in the average. eg, my column, B1:B4 consists of: 0:02 0:04 #VALUE 0:00 I would like the average to return a value of 0:02 ((0:02 + 0:04 + 0:00)/3) Thanx |
Avg numbers but not #VALUE
Thanx for the lesson; I will read up on this concept as I certainly
appreciate wanting to avoid bad design! "JE McGimpsey" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF(ISNUMBER(A1:A5),A1:A5)) A better way would likely be to trap your calculated values so that they didn't return an error - it's bad design to make errors "normal". So, for an example, instead of =A1 + B1 which gives a #VALUE! error if either A1 or B1 are text (including space characters, which some users use to "blank" a cell), you could use =IF(COUNT(A1:B1)=2,A1+B1,"") which will return "" instead of #VALUE! if A1 or B1 is text. Since AVERAGE() ignores text, you can then just use =AVERAGE(A1:A5) In article , Robin wrote: I have a column of calculated values (time). In that column, there are a few #VALUEs due to unavailable data for that particular cell. How do I average the column, but not include cells with #VALUES in the average? I also need zero included in the average. eg, my column, B1:B4 consists of: 0:02 0:04 #VALUE 0:00 I would like the average to return a value of 0:02 ((0:02 + 0:04 + 0:00)/3) Thanx |
All times are GMT +1. The time now is 08:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com