Prev: Excel stops responding; then starts again
Next: Get column headings to float when scrolling down?
From: Malcolm on 28 May 2010 09:53
On my worksheet I have a column of12 cells that I need to average.
Unfortunately cell B6 contains text (see below). As shown the dreaded #DIV/0!
displays in cell B13 until any rates are inputted into the column. After
inputting the first rate the formula works great. I'm using the formula;
=AVERAGE(B1:B12). Is there another formula I could use that will not display
the #DIV/0! in cell B13 ? I'm using Excel 2007.
A B
1
2
3
4
5
6 Name
7
8
9
10
11
12
13 #DIV/0!
Thank you,
Malcolm





From: T. Valko on 28 May 2010 10:00
>I'm using Excel 2007.

Try one of these...

This will work in Excel 2007 and later:

=IFERROR(AVERAGE(B1:B12),"")

This will work in any version:

=IF(COUNT(B1:B12),AVERAGE(B1:B12),"")

--
Biff
Microsoft Excel MVP


"Malcolm" <Malcolm(a)discussions.microsoft.com> wrote in message
news:A8FB152D-D9D2-4ABC-ADF8-D8CD62A9A897(a)microsoft.com...
> On my worksheet I have a column of12 cells that I need to average.
> Unfortunately cell B6 contains text (see below). As shown the dreaded
> #DIV/0!
> displays in cell B13 until any rates are inputted into the column. After
> inputting the first rate the formula works great. I'm using the formula;
> =AVERAGE(B1:B12). Is there another formula I could use that will not
> display
> the #DIV/0! in cell B13 ? I'm using Excel 2007.
> A B
> 1
> 2
> 3
> 4
> 5
> 6 Name
> 7
> 8
> 9
> 10
> 11
> 12
> 13 #DIV/0!
> Thank you,
> Malcolm
>
>
>
>
>


From: Luke M on 28 May 2010 10:03
=IF(COUNT(B1:B12),AVERAGE(B1:B12),"NO NUMBERS")

--
Best Regards,

Luke M
"Malcolm" <Malcolm(a)discussions.microsoft.com> wrote in message
news:A8FB152D-D9D2-4ABC-ADF8-D8CD62A9A897(a)microsoft.com...
> On my worksheet I have a column of12 cells that I need to average.
> Unfortunately cell B6 contains text (see below). As shown the dreaded
> #DIV/0!
> displays in cell B13 until any rates are inputted into the column. After
> inputting the first rate the formula works great. I'm using the formula;
> =AVERAGE(B1:B12). Is there another formula I could use that will not
> display
> the #DIV/0! in cell B13 ? I'm using Excel 2007.
> A B
> 1
> 2
> 3
> 4
> 5
> 6 Name
> 7
> 8
> 9
> 10
> 11
> 12
> 13 #DIV/0!
> Thank you,
> Malcolm
>
>
>
>
>


From: Jacob Skaria on 28 May 2010 10:16
Even if you dont have the text in B6 it will return the error...when you dont
have any numbers in that range...Try

=IF(COUNT(B1:B12),AVERAGE(B1:B12),"")

--
Jacob (MVP - Excel)


"Malcolm" wrote:

> On my worksheet I have a column of12 cells that I need to average.
> Unfortunately cell B6 contains text (see below). As shown the dreaded #DIV/0!
> displays in cell B13 until any rates are inputted into the column. After
> inputting the first rate the formula works great. I'm using the formula;
> =AVERAGE(B1:B12). Is there another formula I could use that will not display
> the #DIV/0! in cell B13 ? I'm using Excel 2007.
> A B
> 1
> 2
> 3
> 4
> 5
> 6 Name
> 7
> 8
> 9
> 10
> 11
> 12
> 13 #DIV/0!
> Thank you,
> Malcolm
>
>
>
>
>
From: Malcolm on 28 May 2010 10:31
Jacob, Hi,
Your MVP status is well deserved. Your solution worked perfectly and allowed
me to clean up not only my initial problem, but some other averaging
problems in my workbook.

Many thanks,
Malcolm

"Jacob Skaria" wrote:

> Even if you dont have the text in B6 it will return the error...when you dont
> have any numbers in that range...Try
>
> =IF(COUNT(B1:B12),AVERAGE(B1:B12),"")
>
> --
> Jacob (MVP - Excel)
>
>
> "Malcolm" wrote:
>
> > On my worksheet I have a column of12 cells that I need to average.
> > Unfortunately cell B6 contains text (see below). As shown the dreaded #DIV/0!
> > displays in cell B13 until any rates are inputted into the column. After
> > inputting the first rate the formula works great. I'm using the formula;
> > =AVERAGE(B1:B12). Is there another formula I could use that will not display
> > the #DIV/0! in cell B13 ? I'm using Excel 2007.
> > A B
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6 Name
> > 7
> > 8
> > 9
> > 10
> > 11
> > 12
> > 13 #DIV/0!
> > Thank you,
> > Malcolm
> >
> >
> >
> >
> >
 | 
Pages: 1
Prev: Excel stops responding; then starts again
Next: Get column headings to float when scrolling down?