Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Not Working
I have tried two different formulas to Average / Median a column of % here
are the two formulas, =IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50)) or =IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50)) Neither of them worked correctly. The ISERROR is in place to deal with cells that are linked and do not show a % until other information is completed. If the source cell goes unused the column maintains the ERROR, so the cell with the formula has to see the error as 0 or blank to generate the average. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Not Working
Are you saying that within the range M4:M50 there are errors like #N/A,
#VALUE!, #NUM!, #DIV/0! ? If that's the case why don't you just fix those errors? Try these array formulas that will account for errors: =AVERAGE(IF(ISNUMBER(M4:M50),M4:M50)) =MEDIAN(IF(ISNUMBER(M4:M50),M4:M50)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "AccAdmin" wrote in message ... I have tried two different formulas to Average / Median a column of % here are the two formulas, =IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50)) or =IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50)) Neither of them worked correctly. The ISERROR is in place to deal with cells that are linked and do not show a % until other information is completed. If the source cell goes unused the column maintains the ERROR, so the cell with the formula has to see the error as 0 or blank to generate the average. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Not Working
I used your Average formula and it worked perfect! Thank you very much for
your assistance. Reason errors are left intact is due to formulas in each cell are linked to cells awaiting information, once information is enter into source error corrects itself. "T. Valko" wrote: Are you saying that within the range M4:M50 there are errors like #N/A, #VALUE!, #NUM!, #DIV/0! ? If that's the case why don't you just fix those errors? Try these array formulas that will account for errors: =AVERAGE(IF(ISNUMBER(M4:M50),M4:M50)) =MEDIAN(IF(ISNUMBER(M4:M50),M4:M50)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "AccAdmin" wrote in message ... I have tried two different formulas to Average / Median a column of % here are the two formulas, =IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50)) or =IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50)) Neither of them worked correctly. The ISERROR is in place to deal with cells that are linked and do not show a % until other information is completed. If the source cell goes unused the column maintains the ERROR, so the cell with the formula has to see the error as 0 or blank to generate the average. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Not Working
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "AccAdmin" wrote in message ... I used your Average formula and it worked perfect! Thank you very much for your assistance. Reason errors are left intact is due to formulas in each cell are linked to cells awaiting information, once information is enter into source error corrects itself. "T. Valko" wrote: Are you saying that within the range M4:M50 there are errors like #N/A, #VALUE!, #NUM!, #DIV/0! ? If that's the case why don't you just fix those errors? Try these array formulas that will account for errors: =AVERAGE(IF(ISNUMBER(M4:M50),M4:M50)) =MEDIAN(IF(ISNUMBER(M4:M50),M4:M50)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "AccAdmin" wrote in message ... I have tried two different formulas to Average / Median a column of % here are the two formulas, =IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50)) or =IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50)) Neither of them worked correctly. The ISERROR is in place to deal with cells that are linked and do not show a % until other information is completed. If the source cell goes unused the column maintains the ERROR, so the cell with the formula has to see the error as 0 or blank to generate the average. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula not working | New Users to Excel | |||
Again for Formula that isn't working... | Excel Worksheet Functions | |||
IF OR formula not working | Excel Discussion (Misc queries) | |||
Formula is not working | Excel Discussion (Misc queries) | |||
Sum formula not working | Excel Worksheet Functions |