Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. Im using the formula; =AVERAGE(B1:B12). Is there another formula I could use that will not display the #DIV/0! in cell B13 ? Im 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. Im using the formula; =AVERAGE(B1:B12). Is there another formula I could use that will not display the #DIV/0! in cell B13 ? Im 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. Im using the formula; =AVERAGE(B1:B12). Is there another formula I could use that will not display the #DIV/0! in cell B13 ? Im using Excel 2007. A B 1 2 3 4 5 6 Name 7 8 9 10 11 12 13 #DIV/0! Thank you, Malcolm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging each 30 cells down a column | Excel Discussion (Misc queries) | |||
Averaging the two most recent entries in a column | Excel Discussion (Misc queries) | |||
Averaging last 5 cells in a column | Excel Worksheet Functions | |||
Averaging a column with 0's | Excel Worksheet Functions | |||
Averaging Text vs. Numbers | Excel Worksheet Functions |