Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#DIV/0! Error
I am having problems with a spreadsheet I am creating. The spreadsheet looks
something like this. Average Most Recent 2004 2005 2006 2007 all data 12 Months Jan 1 2 3 0 Feb 4 5 6 0 Mar 7 8 9 0 Apr 10 11 0 0 The formula I am using for the average of all data is =sumif(B11:E11,"1")/Countif (B11:E11,"1") This formula seems to work. However, when there is no data I get a #DIV/0! Error. How can I fix this. Also, I am still not getting a correct formula to retrieve the most recent 12 months of data. Any help would be appreciated. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#DIV/0! Error
One way:
=if(countif(b11:e11,"1")=0,"No numbers =1", sumif(B11:E11,"1")/Countif(B11:E11,"1")) Kim wrote: I am having problems with a spreadsheet I am creating. The spreadsheet looks something like this. Average Most Recent 2004 2005 2006 2007 all data 12 Months Jan 1 2 3 0 Feb 4 5 6 0 Mar 7 8 9 0 Apr 10 11 0 0 The formula I am using for the average of all data is =sumif(B11:E11,"1")/Countif (B11:E11,"1") This formula seems to work. However, when there is no data I get a #DIV/0! Error. How can I fix this. Also, I am still not getting a correct formula to retrieve the most recent 12 months of data. Any help would be appreciated. Thanks in advance. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#DIV/0! Error
Thanks for your help. That formula worked! Any ideas for the Most Recent 12
Months? Currently I am using Lookup(maxb11:e11),b11:e11). It works somewhat. But it puts a 0 in for the most recent 12 months some of the time. Even if the same info is in the month before. Not sure why. "Dave Peterson" wrote: One way: =if(countif(b11:e11,"1")=0,"No numbers =1", sumif(B11:E11,"1")/Countif(B11:E11,"1")) Kim wrote: I am having problems with a spreadsheet I am creating. The spreadsheet looks something like this. Average Most Recent 2004 2005 2006 2007 all data 12 Months Jan 1 2 3 0 Feb 4 5 6 0 Mar 7 8 9 0 Apr 10 11 0 0 The formula I am using for the average of all data is =sumif(B11:E11,"1")/Countif (B11:E11,"1") This formula seems to work. However, when there is no data I get a #DIV/0! Error. How can I fix this. Also, I am still not getting a correct formula to retrieve the most recent 12 months of data. Any help would be appreciated. Thanks in advance. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
#DIV/0! Error
Nope.
I would think it would be a lot easier to pick out the most recent months if the data were rearranged. Date Qty Jan 1, 2006 3 Feb 1, 2006 5 ..... Kim wrote: Thanks for your help. That formula worked! Any ideas for the Most Recent 12 Months? Currently I am using Lookup(maxb11:e11),b11:e11). It works somewhat. But it puts a 0 in for the most recent 12 months some of the time. Even if the same info is in the month before. Not sure why. "Dave Peterson" wrote: One way: =if(countif(b11:e11,"1")=0,"No numbers =1", sumif(B11:E11,"1")/Countif(B11:E11,"1")) Kim wrote: I am having problems with a spreadsheet I am creating. The spreadsheet looks something like this. Average Most Recent 2004 2005 2006 2007 all data 12 Months Jan 1 2 3 0 Feb 4 5 6 0 Mar 7 8 9 0 Apr 10 11 0 0 The formula I am using for the average of all data is =sumif(B11:E11,"1")/Countif (B11:E11,"1") This formula seems to work. However, when there is no data I get a #DIV/0! Error. How can I fix this. Also, I am still not getting a correct formula to retrieve the most recent 12 months of data. Any help would be appreciated. Thanks in advance. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |