A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Averaging a column that contains text



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 02:53 PM posted to microsoft.public.excel.worksheet.functions
malcolm
external usenet poster
 
Posts: 106
Default Averaging a column that contains text

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





  #2  
Old May 28th, 2010, 03:00 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Averaging a column that contains text

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" wrote in message
...
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







  #3  
Old May 28th, 2010, 03:03 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Averaging a column that contains text

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

--
Best Regards,

Luke M
"Malcolm" wrote in message
...
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







  #4  
Old May 28th, 2010, 03:16 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Averaging a column that contains text

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





  #5  
Old May 28th, 2010, 03:31 PM posted to microsoft.public.excel.worksheet.functions
malcolm
external usenet poster
 
Posts: 106
Default Averaging a column that contains text

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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.