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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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