If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|