I'm trying to average 4 columns on one worksheet c1:f1 but sometimes all
four cells have have a 0 %. This gives me a div/error which crosses over
to my summary worksheet . I would like E1 to be an average of these four
cells or a blank.

Can anyone help me?

Thank you
Deb

OOPs.. I meant to say all four cells could on occasion be blank which gives
me a div/error . I would like answer in this case to also be a blank.

"ferde" wrote:

I'm trying to average 4 columns on one worksheet c1:f1 but sometimes all
four cells have have a 0 %. This gives me a div/error which crosses over
to my summary worksheet . I would like E1 to be an average of these four
cells or a blank.

Can anyone help me?

Thank you
Deb

The only way that you should get that Div/0 error is if you don't have any
numbers in those cells. You can have as many 0% as you want--it won't cause
this error.

You can use a formula like this make sure you don't get that error:
=if(count(c1:f1)=0,"",average(c1:f1))

As an aside, averaging percentages may not give you the result you want.

For instance, if a baseball player goes 1 for 2 in the month of April (50%) and
then 1 for 100 in the month of May (1%), his average wouldn't be (50% + 1%)/2
(or 25.5%).

It would be 2 for 102 or about 2%.





ferde wrote:

I'm trying to average 4 columns on one worksheet c1:f1 but sometimes all
four cells have have a 0 %. This gives me a div/error which crosses over
to my summary worksheet . I would like E1 to be an average of these four
cells or a blank.

Can anyone help me?

Thank you
Deb

--

Dave Peterson

Hi Dave

Actually I meant to say I am getting this Div/o error because my cells
occasionally are blank so I tried to use your formula and substitued ""
instead of 0 but now I get a value error. Not sure what I'm doing wrong.

=if(count(c1:f1)="","",average(c1:f1))


Thank you for your help


"Dave Peterson" wrote:

The only way that you should get that Div/0 error is if you don't have any
numbers in those cells. You can have as many 0% as you want--it won't cause
this error.

You can use a formula like this make sure you don't get that error:
=if(count(c1:f1)=0,"",average(c1:f1))

As an aside, averaging percentages may not give you the result you want.

For instance, if a baseball player goes 1 for 2 in the month of April (50%) and
then 1 for 100 in the month of May (1%), his average wouldn't be (50% + 1%)/2
(or 25.5%).

It would be 2 for 102 or about 2%.





ferde wrote:

I'm trying to average 4 columns on one worksheet c1:f1 but sometimes all
four cells have have a 0 %. This gives me a div/error which crosses over
to my summary worksheet . I would like E1 to be an average of these four
cells or a blank.

Can anyone help me?

Thank you
Deb

--

Dave Peterson

You changed the suggested formula. =Count() is going to return a number.

Try:
=if(count(c1:f1)=0,"",average(c1:f1))





ferde wrote:

Hi Dave

Actually I meant to say I am getting this Div/o error because my cells
occasionally are blank so I tried to use your formula and substitued ""
instead of 0 but now I get a value error. Not sure what I'm doing wrong.

=if(count(c1:f1)="","",average(c1:f1))

Thank you for your help

"Dave Peterson" wrote:

The only way that you should get that Div/0 error is if you don't have any
numbers in those cells. You can have as many 0% as you want--it won't cause
this error.

You can use a formula like this make sure you don't get that error:
=if(count(c1:f1)=0,"",average(c1:f1))

As an aside, averaging percentages may not give you the result you want.

For instance, if a baseball player goes 1 for 2 in the month of April (50%) and
then 1 for 100 in the month of May (1%), his average wouldn't be (50% + 1%)/2
(or 25.5%).

It would be 2 for 102 or about 2%.





ferde wrote:

I'm trying to average 4 columns on one worksheet c1:f1 but sometimes all
four cells have have a 0 %. This gives me a div/error which crosses over
to my summary worksheet . I would like E1 to be an average of these four
cells or a blank.

Can anyone help me?

Thank you
Deb

--

Dave Peterson


--

Dave Peterson

Thank you Dave.....now it works PERFECTLY

Best regards
Deb






"Dave Peterson" wrote:

You changed the suggested formula. =Count() is going to return a number.

Try:
=if(count(c1:f1)=0,"",average(c1:f1))





ferde wrote:

Hi Dave

Actually I meant to say I am getting this Div/o error because my cells
occasionally are blank so I tried to use your formula and substitued ""
instead of 0 but now I get a value error. Not sure what I'm doing wrong.

=if(count(c1:f1)="","",average(c1:f1))

Thank you for your help

"Dave Peterson" wrote:

The only way that you should get that Div/0 error is if you don't have any
numbers in those cells. You can have as many 0% as you want--it won't cause
this error.

You can use a formula like this make sure you don't get that error:
=if(count(c1:f1)=0,"",average(c1:f1))

As an aside, averaging percentages may not give you the result you want.

For instance, if a baseball player goes 1 for 2 in the month of April (50%) and
then 1 for 100 in the month of May (1%), his average wouldn't be (50% + 1%)/2
(or 25.5%).

It would be 2 for 102 or about 2%.





ferde wrote:

I'm trying to average 4 columns on one worksheet c1:f1 but sometimes all
four cells have have a 0 %. This gives me a div/error which crosses over
to my summary worksheet . I would like E1 to be an average of these four
cells or a blank.

Can anyone help me?

Thank you
Deb

--

Dave Peterson


--

Dave Peterson