Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if
N4:N200 does not have any data to report. Is it possible to modify this formula so that the cell the formula is in reports a zero 0 if it does not have any data to report? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
You don't need the =sum() function.
=average(n4:n200) will do the same. =if(count(n4:n200)=0,"No numbers",average(n4:n200)) would check to make sure that there's numbers to be counted/summed/averaged. MCheru wrote: I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if N4:N200 does not have any data to report. Is it possible to modify this formula so that the cell the formula is in reports a zero 0 if it does not have any data to report? -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
try
=IF(ISERROR(SUM(AVERAGE(N4:N200))),0,SUM(AVERAGE(N 4:N200))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "MCheru" wrote: I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if N4:N200 does not have any data to report. Is it possible to modify this formula so that the cell the formula is in reports a zero 0 if it does not have any data to report? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
How clever. Thank you!
"Dave Peterson" wrote: You don't need the =sum() function. =average(n4:n200) will do the same. =if(count(n4:n200)=0,"No numbers",average(n4:n200)) would check to make sure that there's numbers to be counted/summed/averaged. MCheru wrote: I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if N4:N200 does not have any data to report. Is it possible to modify this formula so that the cell the formula is in reports a zero รขฌล0รขฌย if it does not have any data to report? -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
It is helpful. Thanks for the tip! I appreciate you're help.
"francis" wrote: try =IF(ISERROR(SUM(AVERAGE(N4:N200))),0,SUM(AVERAGE(N 4:N200))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "MCheru" wrote: I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if N4:N200 does not have any data to report. Is it possible to modify this formula so that the cell the formula is in reports a zero 0 if it does not have any data to report? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
Point taken. Thank you. Interesting point regarding the ISERROR.
"Gord Dibben" wrote: As Dave pointed out the use of SUM is a waste of space. Also, ISERROR will mask all errors which may not be desirable. IMO the use of ISERROR is used too much with little thought given to correcting the reason for the error. Trap for 0 in some other way..........see Dave's post for that. Gord Dibben MS Excel MVP On Tue, 17 Mar 2009 12:04:01 -0700, francis wrote: try =IF(ISERROR(SUM(AVERAGE(N4:N200))),0,SUM(AVERAGE( N4:N200))) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminating #DIV/O!
Works like a charm! Thank you for you're help.
"David Biddulph" wrote: May I ask you a question in return? What do you think the SUM function does for you in that formula? In what way do you expect =SUM(AVERAGE(N4:N200)) to differ from =AVERAGE(N4:N200) ? Have you looked in Excel help for the SUM function to see what SUM does? You might try =IF(COUNT(N4:N200),AVERAGE(N4:N200),0) or =IF(ISERROR(AVERAGE(N4:N200)),0,AVERAGE(N4:N200)) -- David Biddulph "MCheru" wrote in message ... I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if N4:N200 does not have any data to report. Is it possible to modify this formula so that the cell the formula is in reports a zero "0" if it does not have any data to report? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A and eliminating it. | Excel Discussion (Misc queries) | |||
eliminating records! | Excel Worksheet Functions | |||
eliminating the 0 in DIV/0 | Excel Discussion (Misc queries) | |||
Eliminating #N/A | Excel Worksheet Functions | |||
Eliminating every 25th row | Excel Worksheet Functions |