Averaging results but one has N/A

[avalanche]

Hi,

I just received some great help on one question, and I need to follow up with another. On my Master spreadsheet, I have one tab that looks to the six other employee worsheets and averages out the "% Met" and brings that average into a this one master "Rollup" file.

However, one employee has N/A in the field (rather than 98.0% for ex.).
This then gives me a #VALUE result instead of an average of the 6 employees. Is there a way to write the formula to account for N/A ?

Thanks,
Tom

 

[MrKowz]

Ideally, take care of the N/A errors at the source instead of further down the line. This way you don't have to account for them in every subsequent calculation.

Where you have the formulas that could produce N/A, replace them with:

Excel 2003 or older:
=IF(ISERROR(*your formula*),"",*your formula*)

Excel 2007 or newer:
=IFERROR(*your formula*),"")

 

[avalanche]

I understand what you mean and have used that ISERROR formula before, thank you. But in this case, those percentages are hard coded in by the Sueprvisors. So they'll enter 95%, 85.3% - so there is not an opportunity for an ISERROR formula in this case.

 

[MrKowz]

Understood - try this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">98%</td><td style="text-align: right;;"></td><td style=";">Average</td><td style="text-align: right;;">0.651667</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">87%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">92%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">60%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">20%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Not Available</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">34%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet4</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">A1:A8</font>),A1:A8,""</font>)</font>)/COUNT(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">A1:A8</font>),A1:A8,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[avalanche]

Thanks - but now I'm getting #DIV/0!. Ive enclosed the formula I'm trying to use, maybe I have it written wrong. The names are all the other tabs in the worksheet that I'm going out and grabbing.

{=SUM(IF(ISNUMBER('Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5)," "))/COUNT(IF(ISNUMBER('Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5),'Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5," "))}

 

[avalanche]

This is what Ive tried now and Im back to #VALUE - can anyone help?

{=SUM(IF(ISNUMBER('Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5),'Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5,""))/COUNT(IF(ISNUMBER('Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5),'Jenny Boyd'!M5+'Kimberly Britton'!M5+'Karzett Parham'!M5,""))}

 

[MrKowz]

Since you are working off of different worksheets, this is going to get really nasty. Let me see if I can get a formula together.

 

[MrKowz]

=SUM(IF(ISNUMBER('Jenny Boyd'!M5),'Jenny Boyd'!M5,""),IF(ISNUMBER('Kimberly Britton'!M5),'Kimberly Britton'!M5,""),IF(ISNUMBER('Karzett Parham'!M5),'Karzett Parham'!M5,""))/COUNT(IF(ISNUMBER('Jenny Boyd'!M5),'Jenny Boyd'!M5,""),IF(ISNUMBER('Kimberly Britton'!M5),'Kimberly Britton'!M5,""),IF(ISNUMBER('Karzett Parham'!M5),'Karzett Parham'!M5,""))

Again, be sure to confirm entry with CTRL+SHIFT+ENTER, not just ENTER. The {brackets} you should not enter manually.

 

[avalanche]

Shoot, still getting #VALUE.

I have Jenny as 98.5% and the other two as N/A and no luck. I'll keep trying to mess with it, but had hoped that would work! If you have any other ideas, I'd appreciate it. And thanks a lot for helping.

 

[Aladin Akyurek]

Hi,

I just received some great help on one question, and I need to follow up with another. On my Master spreadsheet, I have one tab that looks to the six other employee worsheets and averages out the "% Met" and brings that average into a this one master "Rollup" file.

However, one employee has N/A in the field (rather than 98.0% for ex.).
This then gives me a #VALUE result instead of an average of the 6 employees. Is there a way to write the formula to account for N/A ?

Thanks,
Tom

=SUMIF(C22:C33,">0")/MAX(1,COUNTIF(C22:C33,">0"))

should not be affected by an #N/A in C22:C33.