Harmonic Mean to EXCLUDE zeros or missing data

[scorpiotail]

Hello again,

I am trying to using harmonic mean on a range that has missing data or zeros. Basically, weekly sales numbers that have yet to be entered for the year that I do on a weekly basis. I have many different formulas set in place already that work great, but I need to find the best way to get a "true" average while taking out the outliers like unusually high sales numbers not typical for that person. That is why I [want to] use HARMEAN. It hates negatives and zeros though...that's the problem.

Here's a few methods I have done so far with no success, producing #NUM, #N/A errors, etc:

{=IF($B$1:$B$52<=0,HARMEAN(IF($B$1:$B$52,"<>0",$B$1:$B$52)))} --arrayed and which produces "FALSE"
{=HARMEAN(IF(B1:B52, "<>0", B1:B52)} --arrayed and produces #NUM error

I have tried a few other methods, but it's driving me nutso.. ..this all started when I thought I could simply figure out how to predict what the next sales week was going to look like for that agent. I'm so close.... Thanks for any insight this community can give!

 

[Momentman]

try
=HARMEAN(IF($B$1:$B$52<>0,$B$1:$B$52))
Enter with CTRL+SHIFT+ENTER

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=HARMEAN(IF(ISNUMBER($B$1:$B$52),IF($B$1:$B$52>0,$B$1:$B$52)))

 

[Momentman]

try
=HARMEAN(IF($B$1:$B$52<>0,$B$1:$B$52))
Enter with CTRL+SHIFT+ENTER

I obviously didn't consider all cases Missing Data ish

@Aladin: Do you think its of any value to perform a mathematical operation on the number in the event its stored as text and modify the formula like say
==HARMEAN(IF(ISNUMBER(0+($B$1:$B$52)),IF($B$1:$B$52>0,$B$1:$B$52)))

 

[Aladin Akyurek]

I obviously didn't consider all cases Missing Data ish

@Aladin: Do you think its of any value to perform a mathematical operation on the number in the event its stored as text and modify the formula like say
==HARMEAN(IF(ISNUMBER(0+($B$1:$B$52)),IF($B$1:$B$52>0,$B$1:$B$52)))

If that is desired, yes, but then we would need:

{=HARMEAN(IF(ISNUMBER(0+$B$1:$B$52),IF((0+$B$1:$B$52)>0,0+$B$1:$B$52)))}

This would also pick up the logical values (e.g., TRUE)...

 

[scorpiotail]

Control+shift+enter, not just enter:

=HARMEAN(IF(ISNUMBER($B$1:$B$52),IF($B$1:$B$52>0,$B$1:$B$52)))

This worked perfectly! Thank you guys for your input! Now that I look at this, things seem to make a lot of sense. I never considered using ISNUMBER or even had an idea of how it would be used.

 

[Aladin Akyurek]

This worked perfectly! Thank you guys for your input! Now that I look at this, things seem to make a lot of sense. I never considered using ISNUMBER or even had an idea of how it would be used.

You are welcome. Thanks for the update.