Omitting blank cells until filled in?
 

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.

Omitting blank cells until filled in?
 

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

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.

Omitting blank cells until filled in?
 

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))}


--
regards


"Carissa" wrote:

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.

Omitting blank cells until filled in?
 

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!

"Mike" wrote:

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

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.