Average a number of Cells excluding 0

Stillgate · #1 08-29-2022, 08:43 AM

=AVERAGE(B26,B54,B82,B110,B138)

I'm doing a average of these cells, but some of them have a value of 0, how do I exclude the zeros? I cant just remove the cell because on different spreadsheets, they will have value.

I cant use the AVERAGEIF, i cant use the range because some of the B cells have other data that dont want to use. I only want these 5 cells.

p45cal · #2 08-29-2022, 01:31 PM

maybe:

Code:

=AVERAGE(IF(CHOOSE({1,2,3,4,5},B26,B54,B82,B110,B138)<>0,CHOOSE({1,2,3,4,5},B26,B54,B82,B110,B138)))

or:

Code:

=AVERAGE(IF(INDEX(B:B,{26,54,82,110,138})<>0,INDEX(B:B,{26,54,82,110,138})))

both committed to the sheet with Ctrl+Shift+Enter rather than plain Enter.

or shorter:

Code:

=LET(a,CHOOSE({1,2,3,4,5},B26,B54,B82,B110,B138),AVERAGE(IF(a<>0,a)))

thinking on it…
edit: forget the LET formula, you're using Excel 2010.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate average in a column, but ignore empty cells?	alnonymous	Word	1	08-09-2022 07:46 PM
Average on conditional formatted cells	nadavmaroko	Excel	4	02-10-2019 04:13 AM
count a number of cells based on the beginning of a order number	Kubi	Excel	2	08-06-2017 08:54 PM
average formal if cell contains number and text	Michael Labuschagne	Excel	2	12-08-2016 06:35 AM
how to know the number of same cells ?	rosalind2	Excel	2	05-16-2014 06:16 AM