Help with excluding a value from an average

[bigscientist]

Hi all,

I need help making a formula that finds the average from a range, but finds and excludes any "0"s specifically from that range and thus average. For example:

1000
900
0
800
900

I want the average to be 900 (3600/4).

1000
900
800
800
900

I want the average to be 880 (44000/5).

Currently my formula is, for example, =IF(cell="","",AVERAGE(RANGE)) and doesn't include the "0" exclusion. Thank you so much!

[CK76]

Which version of Excel do you have? 365, 2016 or some other version?

Try... AVERAGEIFS(Range,Range,"<>0")
https://support.office.com/en-us/art...8-f7c5c3001690

If that function isn't available...
=SUM(Range)/COUNTIF(RANGE,"<>0")

[AliGW]

Do you have the AVERAGEIF function?

If so, this:

=AVERAGEIF(range,">"&0,range)

[bigscientist]

Thanks for your help. Your solution worked. But I'll actually add a bit more complexity to my problem now. I actually need the 0 omitted for a CV (in other words standard deviation, STDEV). Does that make sense?

[CK76]

Try...

Please Login or Register  to view this content.

Confirmed as array (CTRL + SHIFT + ENTER)

[bigscientist]

That didn't work. I tried:
=IF(cell="","",STDEV(IF(range<>0,'SMedix - Whole Blood'!BJ62:BJ67)/AVERAGEIF(range,">"&0,range))) and got a #VALUE

This worked for average
=IF(cell="","",AVERAGEIF(range,">"&0,range))

[CK76]

Can you upload sample workbook(s) showing your issues?

=STDEV.P(IF(Range<>0,Range)) (or STDEV.S()) does work.

See below example using STDEV.S
0.JPG

See attached.

[bigscientist]

Now I'm thoroughly confused. Entering in your exact formula doesn't change the STDEV between excluding 0 and not.1111.jpg

[CK76]

You did not confirm it as array.

I.E. When you confirm the formula, you can't just hit enter. You need to hit CTRL + SHIFT + ENTER.

[bigscientist]

I see, thank you for the clarification. Is there a simpler way to do this? I'm generating a calculation sheet for operators and they need to be able to get the value automatically. I can't tell them to hit CTRL SHIFT ENTER.

[CK76]

Hmm? Once the formula is confirmed. It will automatically update when range values change and there is no need to confirm again with CTRL + SHIFT + ENTER.

If you need to make the range dynamic, just used dynamic named range.

If operators need to enter the formula or alter it in any way. I'd recommend that they learn it.

There may be alternatives to array formula, but formula construct will be much more complex and will likely cause more issue if they need to edit it.

Edit: Unfortunately I can't test it as with recent update to Office 365, you no longer have to use CTRL + SHIFT + ENTER for array formulas. Which made it bit hard to check for backward compatibility.

[bigscientist]

Thank you so much. I wasn't aware that the CTRL SHIFT ENTER essentially saves and doesn't have to be clicked each time you change the range values. I dont think I'll ever understand the depths of Excel.

[FlameRetired]

Piggybacking on CK76's file suppose you use a helper column (column C for example) which can be hidden (or placed on another sheet)
Will not require Ctrl + Shift + Enter

	B	C	D	E	F	G	H
1
2	100	100	In C2: =IF(B2,B2)
3	0	FALSE
4	101	101		STDEV.S	Exclude 0	Average	Exclude 0
5	99	99		50.00667	1	75	100
6					In F5: =STDEV.S(C2:C5)

[AliGW]

Administrative Note:

Bigscientist - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.