Calculating percentages-how to ignore blank cells?

**lemoineer** · 03-21-2008, 07:27 PM

I'm trying to calculate percentages with Excel, but I want it to ignore any blank cells. For example, I have 5 cells for data entry-each of the cells will contain a number up to 60. Therefore, the cell that is calculating the percentage of the 5 cells is based on a total of 300. However, if one of the cells is left blank, I want the the total percentage to be based on 240; if two of the cells are left blank, I want the total percentage to be based on 180, etc. I also want an entered zero to be included in the calculation (not changing the total the percentage is based on.)

I've used Excel quite a bit, but mostly for non-technical purposes. Any help would be greatly appreciated!!

**darkyam** · 03-21-2008, 08:05 PM

Are the cells in question all right next to each other or are there spaces between them? If they are all next to each other, you could do =SUM(A1:A5)/(COUNT(A1:A5)*60)

**Portuga** · 03-21-2008, 08:06 PM

Hi. You can use the "average" formula. It disregards blanks (excel 2003) but takes into account zeros. Ex:

=average(a1:a5)

**darkyam** · 03-21-2008, 08:11 PM

That doesn't take the 60 points per item into account to get the percentage necessary, though.

**Portuga** · 03-21-2008, 08:19 PM

I stand corrected lemoineer. I didnt really read what you were looking for. (not averages, percentages!)

Thanks for the correction darkyam

In case the cells are not all next to each other. You could add an extra column called "count" for example purposes in column "E"
Here you could do something like =If(A1="";0;1) and drag down. This would count only cells that have data (Including with zeros. A then modify darkyam´s formula to:

=SUM(A1:A5)/(SUM(E1:E5)*60)