Why is this not working?

[R.P.McMurphy]

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:FB6>D6))

cheers!

steve

 

[Domenic]

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?

 

[R.P.McMurphy]

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:FB6>D6)+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

 

[R.P.McMurphy]

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:FA24),6)=1)*(G24:FA24>C24)+FJ24))*(C24*$E24)/(C24*52)

odd

steve

 

[R.P.McMurphy]

sorted. got a bracet in the wrong place! ;-)

steve

 

[Domenic]

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:FA24>C24)+FJ24))*(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!

 

[R.P.McMurphy]

hehe...yep i got it now! tar muchly!

steve

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!