ExcelBanter - Calculate average with missing values

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Calculate average with missing values (https://www.excelbanter.com/excel-worksheet-functions/216447-calculate-average-missing-values.html)

Arne Hegefors

Calculate average with missing values

Hi! In column A I have values in some cells and "#MISSING!" in some cells.
In column B I want to calculate the average value of e.g. A1 to A3. How can I
do that when some values are missing? Thanks!

Pete_UK

Calculate average with missing values

This will still give you the average of the cells which contain
numbers:

=AVERAGE(A1:A3)

For example, A1 = 1, A2 = "text", A3 = 2 gives B1 = 1.5.

Hope this helps.

Pete

On Jan 14, 9:46*am, Arne Hegefors
wrote:
Hi! In column *A I have values in some cells and "#MISSING!" in some cells.
In column B I want to calculate the average value of e.g. A1 to A3. How can I
do that when some values are missing? Thanks!

JE McGimpsey

Calculate average with missing values

One way:

=AVERAGE(A1:A3)

AVERAGE() ignores text.

If you may not have ANY numeric values in A1:A3, then perhaps:

=IF(COUNT(A1:A3),AVERAGE(A1:A3),"No Values")

In article ,
Arne Hegefors wrote:

Hi! In column A I have values in some cells and "#MISSING!" in some cells.
In column B I want to calculate the average value of e.g. A1 to A3. How can I
do that when some values are missing? Thanks!

All times are GMT +1. The time now is 01:54 PM.