Omitting blank cells until filled in?

[Carissa]

Hello,

I am creating a spreadsheet using percentages and getting their averages.
Right now, I am using the regulare AVERAGE function, however, if cells are
blank I get #DIV/0!. I understand it's because I have no values, however I
will soon and I need something more presentable than that error.

For instance, I have 2 rows with 6 colums, 7 including the average at the
end. I need the total of the 2 rows average and I only have data in the first
2 colums on the first row.

Any ways, my question is there a way I can omit the blank cells or skip over
them until there is data placed inside? Or a way to get around it? Any help
would be much appreciated. Thank you in advance.

 

[Mike]

Try this paste this into cell B1 then fill in cells A1 thru A1
=IF(ISERROR(SUM(A1:A10)/COUNTIF(A1:A10,">0")),0,SUM(A1:A10)/COUNTIF(A1:A10,">0"))

 

[driller]

Carissa,

try something with a C+S+E formula on col G. as the row average
where the true blank cells are ignored.

you can copy the table below and paste to your excel sht.

A B C D E F G
40% 40% 40% 0% 30% {=IF(A1:F1="","",AVERAGE(A1:F1))}
30% 30% 30% 30% 0% 50% 28% {=IF(A2:F2="","",AVERAGE(A2:F2))}
{=IF(A3:F3="","",AVERAGE(A3:F3))}

 

[Carissa]

Thanks Mike that worked perfectly...I just changed the A1:A10 to the columns
I needed calculated and it took away my error.

I have a favor to ask, can you explain how you came up with that? I would
like to know how to do it or create a formula like that in case I run into
another problem and maybe I will be able to fix it myself. Thanks so much!