|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 16th, 2006, 05:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some column
I use this formula to sum the values in every fourth cell in colums
=SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of the colums, but returns the error #DIV/0! in the other colums. I do not get why I get the error only some of the time. What causes this and how do I correct it? Thanks -- Brent 
|
|
#2
March 16th, 2006, 06:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum every 4th cell returns #DIV/0! error in some column
Do you mean a #VALUE! error? If so, then it's likely that your range of
cells contains one or more text values. Maybe your range contains formula blanks. Try the following syntax instead... =SUMPRODUCT(--(MOD(ROW(E7:E190)-ROW(E7),4)=0),E7:E190) ....which ignores text values, including formula blanks. Hope this helps! In article , Brent wrote: I use this formula to sum the values in every fourth cell in colums =SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of the colums, but returns the error #DIV/0! in the other colums. I do not get why I get the error only some of the time. What causes this and how do I correct it? Thanks
|
|
#3
March 16th, 2006, 06:52 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum every 4th cell returns #DIV/0! error in some column
do the columns have cells returing a #DIV/0 error?
-- Regards, Tom Ogilvy "Brent" wrote: I use this formula to sum the values in every fourth cell in colums =SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of the colums, but returns the error #DIV/0! in the other colums. I do not get why I get the error only some of the time. What causes this and how do I correct it? Thanks -- Brent
|
|
#4
March 16th, 2006, 06:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum every 4th cell returns #DIV/0! error in some co
Hey Tom,
Yes, in some of the colums the cell with this formula displays #DIV/0!, in other columns I get the desired sums. All the cells with this formula are in the same row (row 198) and refer to data in rows 7 - 195.. Thanks for your interest and help. -- Brent "Tom Ogilvy" wrote: do the columns have cells returing a #DIV/0 error? -- Regards, Tom Ogilvy "Brent" wrote: I use this formula to sum the values in every fourth cell in colums =SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of the colums, but returns the error #DIV/0! in the other colums. I do not get why I get the error only some of the time. What causes this and how do I correct it? Thanks -- Brent
|
|
#5
March 16th, 2006, 07:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum every 4th cell returns #DIV/0! error in some co
In that case, try the following formula...
=SUM(IF(MOD(ROW(E7:E190)-ROW(E7),4)=0,IF(ISNUMBER(E7:E190),E7:E190))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Brent wrote: Hey Tom, Yes, in some of the colums the cell with this formula displays #DIV/0!, in other columns I get the desired sums. All the cells with this formula are in the same row (row 198) and refer to data in rows 7 - 195.. Thanks for your interest and help. -- Brent "Tom Ogilvy" wrote: do the columns have cells returing a #DIV/0 error? -- Regards, Tom Ogilvy "Brent" wrote: I use this formula to sum the values in every fourth cell in colums =SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of the colums, but returns the error #DIV/0! in the other colums. I do not get why I get the error only some of the time. What causes this and how do I correct it? Thanks -- Brent
|
|
#6
March 16th, 2006, 07:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum every 4th cell returns #DIV/0! error in some co
An alternative is to have your formulas return 0 or blank instead of
#DIV/0!. If you have your formulas return a blank, you can use the first formula I offered... In article , Domenic wrote: In that case, try the following formula... =SUM(IF(MOD(ROW(E7:E190)-ROW(E7),4)=0,IF(ISNUMBER(E7:E190),E7:E190))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps!
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Return SEARCHED Column Number of Numeric Label and Value | Sam via OfficeKB.com | Worksheet Functions | 23 | January 30th, 2006 06:16 PM |
| Match then lookup | Tenacity | Worksheet Functions | 10 | December 3rd, 2005 05:30 AM |
| Formula Problem - interrupted by #VALUE! in other cells!? | Ted | Worksheet Functions | 17 | November 25th, 2005 05:18 PM |
| unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
| IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
Linear Mode
