#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. |
#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 |
#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 |
#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 |
All times are GMT +1. The time now is 06:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com