# Average Formula of Cells Over Certain Value Excludes Blanks

1. ## Average Formula of Cells Over Certain Value Excludes Blanks

Hi,

I have been trying to figure out a formula in excel to calculate the average of a column but only includes cells that have value over a certain number (eg.14) as well as excluding blanks.

The formula i have so far is:

=SUM(K3:K439) / (COUNT(K3:K439) - COUNTIF(K3:K439,0))

I want to use this formula in a spreadsheet to help calculate the average time it takes to receive goods excluding times where we get them in express.

Thanks  Register To Reply

2. ## Re: Average Formula of Cells Over Certain Value Excludes Blanks

=SUMIF(K3:K439,">14")/COUNTIF(K3:K439,">14")

That will exclude every cell in the range that is less than 15, which would of course exclude 0 as well. Are you looking for a single average, or one average for all cells excluding zeros and one average for all cells with a value of 14 and higher?  Register To Reply

3. ## Re: Average Formula of Cells Over Certain Value Excludes Blanks

Hi Knewbry,

Thanks for that. I am looking to calculate one average for all cells that have values over 14 and excludes blank cells. The range i wish to calculate the average is K3:k439. Within this range, there are numerous cells that are under 14 which i wish to exclude from the calculation as well as blank cells with no update data.  Register To Reply