| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT is OK for a while and later a similar formula returns 0
Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and
=SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I go to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero. They shouldn't because I'm just working across the columns and there are numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in advance! Pierian Spring |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT is OK for a while and later a similar formula returns 0
That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5
Try =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierian Spring" wrote in message ... Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I go to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero. They shouldn't because I'm just working across the columns and there are numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in advance! Pierian Spring |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT is OK for a while and later a similar formula retur
Hi Bob, you probably recognise your own formula from yesterday! I tried your
recommendation below in Column E (which is 5) and it worked. However, how do I move it on to Column F? I need to SUM ebery 5th Column. I thought you indicated that 2 SUMS B, G, etc. So if I modifiy 2 . . . . .? Sorry for being a nuisance. "Bob Phillips" wrote: That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5 Try =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierian Spring" wrote in message ... Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I go to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero. They shouldn't because I'm just working across the columns and there are numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in advance! Pierian Spring |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT is OK for a while and later a similar formula retur
Pierian,
the column being summed is determine by the value being compared against, =0 is columns E, J etc., =1 is F, K, etc. and so on, so just adjust that part of the formula. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierian Spring" wrote in message ... Hi Bob, you probably recognise your own formula from yesterday! I tried your recommendation below in Column E (which is 5) and it worked. However, how do I move it on to Column F? I need to SUM ebery 5th Column. I thought you indicated that 2 SUMS B, G, etc. So if I modifiy 2 . . . . .? Sorry for being a nuisance. "Bob Phillips" wrote: That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5 Try =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierian Spring" wrote in message ... Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I go to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero. They shouldn't because I'm just working across the columns and there are numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in advance! Pierian Spring |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT is OK for a while and later a similar formula retur
Thanks Bob, it worked a treat. All my formulae are now in place. Now I just
need to start populating the ss! Many thanks for your help! Pierian "Bob Phillips" wrote: Pierian, the column being summed is determine by the value being compared against, =0 is columns E, J etc., =1 is F, K, etc. and so on, so just adjust that part of the formula. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierian Spring" wrote in message ... Hi Bob, you probably recognise your own formula from yesterday! I tried your recommendation below in Column E (which is 5) and it worked. However, how do I move it on to Column F? I need to SUM ebery 5th Column. I thought you indicated that 2 SUMS B, G, etc. So if I modifiy 2 . . . . .? Sorry for being a nuisance. "Bob Phillips" wrote: That is because the MOD value of MOD(n,5) is 0,1,2,3,4, there is no 5 Try =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=0),53:53) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierian Spring" wrote in message ... Hello, I'm using =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=3),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=4),53:53) and they work fine. When I go to =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=5),53:53) and =SUMPRODUCT(--(MOD(COLUMN(53:53),5)=6),53:53) these formulae return zero. They shouldn't because I'm just working across the columns and there are numeric values in Columns 5 and 6. I'm a novice Excel user. Thanks in advance! Pierian Spring |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
