Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average when No Data
I use this formula:
=AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7)) When there is no data in the range, the formula returns DIV/0. Is there a way to modify the formula so that it returns """ instead of DIV/0. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average when No Data
=IF(COUNT(I7:AQ7)=0,"",AVERAGE(IF(ISNUMBER(I7:AQ7) ;I7:AQ7)))
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "carl" wrote in message ... I use this formula: =AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7)) When there is no data in the range, the formula returns DIV/0. Is there a way to modify the formula so that it returns """ instead of DIV/0. Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average when No Data
Change to:
=IF(AND(NOT(ISNUMBER(I7:AQ7))),"",AVERAGE(IF(ISNUM BER(I7:AQ7),I7:AQ7))) -- Regards, Dave "carl" wrote: I use this formula: =AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7)) When there is no data in the range, the formula returns DIV/0. Is there a way to modify the formula so that it returns """ instead of DIV/0. Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average when No Data
Hi!
One way (array entered): =IF(COUNT(I7:AQ7),AVERAGE(IF(ISNUMBER(I7:AQ7),I7:A Q7)),"") If Count = 0 the formula returns blank (""). Biff "carl" wrote in message ... I use this formula: =AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7)) When there is no data in the range, the formula returns DIV/0. Is there a way to modify the formula so that it returns """ instead of DIV/0. Thank you in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average when No Data
Since =Average() ignores text, is there a reason you used the array formula?
=if(count(I7:aq7)=0;"";average(i7:aq7)) The array formula will ignore errors (#ref, div/0, etc), though. carl wrote: I use this formula: =AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7)) When there is no data in the range, the formula returns DIV/0. Is there a way to modify the formula so that it returns """ instead of DIV/0. Thank you in advance. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Two data sets, one average | Charts and Charting in Excel | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |