Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why is this not working?
why is this not working? it should add up the values of every 6th cell (H6
through to FB6), but if any cell is over the value of D6 then D6 's value is taken as the value for that particular cell in that particular instance. it seemed to be working, but if i enter a value of over D6 in H6 etc, the answer is worng. =SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*(H6:FB6<=D6) *H6:FB6)+D6*SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*( H6:FB6D6)) cheers! steve |
#2
|
|||
|
|||
The formula seems to work as you've described. Let's take a look at the
following example where... D6 contains 100 H6 contains 180 N6 contains 60 T6 contains 160 Z6 contains 80 ....shouldn't the answer be 340? In article , "R.P.McMurphy" wrote: why is this not working? it should add up the values of every 6th cell (H6 through to FB6), but if any cell is over the value of D6 then D6 's value is taken as the value for that particular cell in that particular instance. it seemed to be working, but if i enter a value of over D6 in H6 etc, the answer is worng. =SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*(H6:FB6<=D6) *H6:FB6)+D6*SUMPRODUCT((MOD( COLUMN(H6:FB6),6)=2)*(H6:FB6D6)) cheers! steve |
#3
|
|||
|
|||
yes it should.
but its not working. let me give you the full formula as im using it. =(SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=1)*(H6:FB6<=D6 )*H6:FB6)+D6*SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=1)* (H6:FB6D6)+FK6))*(D6*$E6)/(D6*52) its as before, only with adding FJ6 what ever its value and then multipling the whole answer by (D6*E6)/(D6*52) any help? steve "Domenic" wrote in message ... The formula seems to work as you've described. Let's take a look at the following example where... D6 contains 100 H6 contains 180 N6 contains 60 T6 contains 160 Z6 contains 80 ...shouldn't the answer be 340? In article , "R.P.McMurphy" wrote: why is this not working? it should add up the values of every 6th cell (H6 through to FB6), but if any cell is over the value of D6 then D6 's value is taken as the value for that particular cell in that particular instance. it seemed to be working, but if i enter a value of over D6 in H6 etc, the answer is worng. =SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*(H6:FB6<=D6) *H6:FB6)+D6*SUMPRODUCT((MOD( COLUMN(H6:FB6),6)=2)*(H6:FB6D6)) cheers! steve |
#4
|
|||
|
|||
ive just tryied it further down the sheet and it seems to work ok...using
this =(SUMPRODUCT((MOD(COLUMN(G24:FA24),6)=1)*(G24:FA24 <=C24)*G24:FA24)+C24*SUMPRODUCT((MOD(COLUMN(G24:FA 24),6)=1)*(G24:FA24C24)+FJ24))*(C24*$E24)/(C24*52) odd steve "Domenic" wrote in message ... The formula seems to work as you've described. Let's take a look at the following example where... D6 contains 100 H6 contains 180 N6 contains 60 T6 contains 160 Z6 contains 80 ...shouldn't the answer be 340? In article , "R.P.McMurphy" wrote: why is this not working? it should add up the values of every 6th cell (H6 through to FB6), but if any cell is over the value of D6 then D6 's value is taken as the value for that particular cell in that particular instance. it seemed to be working, but if i enter a value of over D6 in H6 etc, the answer is worng. =SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*(H6:FB6<=D6) *H6:FB6)+D6*SUMPRODUCT((MOD( COLUMN(H6:FB6),6)=2)*(H6:FB6D6)) cheers! steve |
#5
|
|||
|
|||
sorted. got a bracet in the wrong place! ;-)
steve "Domenic" wrote in message ... The formula seems to work as you've described. Let's take a look at the following example where... D6 contains 100 H6 contains 180 N6 contains 60 T6 contains 160 Z6 contains 80 ...shouldn't the answer be 340? In article , "R.P.McMurphy" wrote: why is this not working? it should add up the values of every 6th cell (H6 through to FB6), but if any cell is over the value of D6 then D6 's value is taken as the value for that particular cell in that particular instance. it seemed to be working, but if i enter a value of over D6 in H6 etc, the answer is worng. =SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*(H6:FB6<=D6) *H6:FB6)+D6*SUMPRODUCT((MOD( COLUMN(H6:FB6),6)=2)*(H6:FB6D6)) cheers! steve |
#6
|
|||
|
|||
In article ,
"R.P.McMurphy" wrote: ive just tryied it further down the sheet and it seems to work ok...using this =(SUMPRODUCT((MOD(COLUMN(G24:FA24),6)=1)*(G24:FA24 <=C24)*G24:FA24)+C24*SUMPROD UCT((MOD(COLUMN(G24:FA24),6)=1)*(G24:FA24C24)+FJ2 4))*(C24*$E24)/(C24*52) odd The reason this works is that you've changed your starting column. The MOD part... (MOD(COLUMN(G24:FA24),6)=1) ....means that every 6th cell is evaluated as TRUE starting with G24, whereas in your previous formula... (MOD(COLUMN(H6:FB6),6)=1) ....means that every 6th cell is evaluated as TRUE starting with M6 not H6. To start with H6 you would need to change '=1' to '=2'. However, you could use the following instead... (MOD(COLUMN(H6:FB6)-COLUMN(H6),6)=0) ....which would always ensure that every 6th cell is evaluated as TRUE starting with the first cell in the range, in this case H6. Also, it would allow you to insert a new column before Column H without affecting the formula. Hope this helps! |
#7
|
|||
|
|||
hehe...yep i got it now! tar muchly!
steve "Domenic" wrote in message ... In article , "R.P.McMurphy" wrote: ive just tryied it further down the sheet and it seems to work ok...using this =(SUMPRODUCT((MOD(COLUMN(G24:FA24),6)=1)*(G24:FA24 <=C24)*G24:FA24)+C24*SUMPROD UCT((MOD(COLUMN(G24:FA24),6)=1)*(G24:FA24C24)+FJ2 4))*(C24*$E24)/(C24*52) odd The reason this works is that you've changed your starting column. The MOD part... (MOD(COLUMN(G24:FA24),6)=1) ...means that every 6th cell is evaluated as TRUE starting with G24, whereas in your previous formula... (MOD(COLUMN(H6:FB6),6)=1) ...means that every 6th cell is evaluated as TRUE starting with M6 not H6. To start with H6 you would need to change '=1' to '=2'. However, you could use the following instead... (MOD(COLUMN(H6:FB6)-COLUMN(H6),6)=0) ...which would always ensure that every 6th cell is evaluated as TRUE starting with the first cell in the range, in this case H6. Also, it would allow you to insert a new column before Column H without affecting the formula. Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the working days difference between to dates? | Excel Discussion (Misc queries) | |||
how do i count how many people are working between two times in e | Excel Worksheet Functions | |||
Working time and days | Excel Worksheet Functions | |||
Working time and days | Excel Discussion (Misc queries) | |||
Trace Dependents and Precedents not working | Excel Worksheet Functions |