How to refer to every 5 th cell?

I'm currently working with this array formula...

=SUM(IF(G4:BJ4>C4,C4,G4:BJ4))*0.09615

How do i change this so that G4:BJ4 refers to every 5th cell staring with
G4.

Ie G4, L4, Q4, V4 ......etc.

CHeers

steve

Try...

=SUM(IF(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0,IF((G4:BJ4<>"")*(G4:BJ4>C4),C4
,G4:BJ4)))*0.09615

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <430f49ee$0$17485$ed2e19e4@ptn-nntp-reader04.plus.net>,
"R.P.McMurphy" <rpmcmurphy@ntlworld.com> wrote:

> I'm currently working with this array formula...
>
> =SUM(IF(G4:BJ4>C4,C4,G4:BJ4))*0.09615
>
> How do i change this so that G4:BJ4 refers to every 5th cell staring with
> G4.
>
> Ie G4, L4, Q4, V4 ......etc.
>
> CHeers
>
> steve

try this idea. Modify to suit your column instead of row
=SUM(IF(G1:G31>C4,G1:H31)*(MOD(ROW(G1:H31)-ROW(G1),5)=1))

--
Don Guillett
SalesAid Software
donaldb@281.com
"R.P.McMurphy" <rpmcmurphy@ntlworld.com> wrote in message
news:430f49ee$0$17485$ed2e19e4@ptn-nntp-reader04.plus.net...
> I'm currently working with this array formula...
>
> =SUM(IF(G4:BJ4>C4,C4,G4:BJ4))*0.09615
>
> How do i change this so that G4:BJ4 refers to every 5th cell staring with
> G4.
>
> Ie G4, L4, Q4, V4 ......etc.
>
> CHeers
>
> steve
>
>

The best way I can think of is to copy the cell over to the right such
that it picks up every cell reference, then delete the formulas that
don't apply (the ones that are not every 5th), then move the remaining
ones into their proper place. Chunky and inelegant, but it will work.
How many do you have to do?

hi

=SUM(IF(SUM(G3,L3,P3,U3,Z3)>C3,C3,SUM(G3,L3,P3,U3,Z3))*0.09615)

you will have to add more cells but i tested this and it worked on my pc.

Regards
FSt1

"R.P.McMurphy" wrote:

> I'm currently working with this array formula...
>
> =SUM(IF(G4:BJ4>C4,C4,G4:BJ4))*0.09615
>
> How do i change this so that G4:BJ4 refers to every 5th cell staring with
> G4.
>
> Ie G4, L4, Q4, V4 ......etc.
>
> CHeers
>
> steve
>
>
>

Make that...

=SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C4
,G4:BJ4)))*0.09615

....confirmed with CONTROL+SHIFT+ENTER.

In article <domenic22-1D8A7B.13502926082005@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> wrote:

> Try...
>
> =SUM(IF(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0,IF((G4:BJ4<>"")*(G4:BJ4>C4),C4
> ,G4:BJ4)))*0.09615
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!

Thats working great! thanks! next question, i want to add the value
contained in BQ4 to the array just before the final *0.09615
calculation...can you help?

Thanks!

steve

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-1D8A7B.13502926082005@msnews.microsoft.com...
> Try...
>
> =SUM(IF(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0,IF((G4:BJ4<>"")*(G4:BJ4>C4),C4
> ,G4:BJ4)))*0.09615
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <430f49ee$0$17485$ed2e19e4@ptn-nntp-reader04.plus.net>,
> "R.P.McMurphy" <rpmcmurphy@ntlworld.com> wrote:
>
>> I'm currently working with this array formula...
>>
>> =SUM(IF(G4:BJ4>C4,C4,G4:BJ4))*0.09615
>>
>> How do i change this so that G4:BJ4 refers to every 5th cell staring with
>> G4.
>>
>> Ie G4, L4, Q4, V4 ......etc.
>>
>> CHeers
>>
>> steve

Try...

=(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
4,G4:BJ4)))+IF((BQ4<>"")*(BQ4>C4),C4,BQ4))*0.09615

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <430f71ff$0$22930$ed2619ec@ptn-nntp-reader01.plus.net>,
"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:

> Thats working great! thanks! next question, i want to add the value
> contained in BQ4 to the array just before the final *0.09615
> calculation...can you help?
>
> Thanks!
>
> steve

Excel is not accepting it for some reason. any idea where the fault may be?

steve

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-A9E1DE.16314226082005@msnews.microsoft.com...
> Try...
>
> =(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
> 4,G4:BJ4)))+IF((BQ4<>"")*(BQ4>C4),C4,BQ4))*0.09615
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <430f71ff$0$22930$ed2619ec@ptn-nntp-reader01.plus.net>,
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>
>> Thats working great! thanks! next question, i want to add the value
>> contained in BQ4 to the array just before the final *0.09615
>> calculation...can you help?
>>
>> Thanks!
>>
>> steve

That's it! Brilliant!

however there seems to be a problem with the original part now. where i
wanted each 5th cell to be read, if the value of that cell was higher than
cell C4 then C4's value would take precedent.

thanks for your help!

steve

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-9B9BF3.17372426082005@msnews.microsoft.com...
> In article <430f846d$0$22951$ed2619ec@ptn-nntp-reader01.plus.net>,
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>
>> Excel is not accepting it for some reason. any idea where the fault may
>> be?
>
> If you're copying and pasting the formula into your worksheet, make sure
> that a line break hasn't been added.
>
>> im a bit confused about the bit you added in order to add the value of
>> BQ4...this bit..
>>
>> +IF((BQ4<>"")*(BQ4>C4),C4,BQ4))
>>
>> why does it refer to C4?
>
> I assumed, incorrectly it appears, that BQ4 would following the same
> logic as cells in G4:BJ4. Maybe this what you mean...
>
> =(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
> 4,G4:BJ4)))+BQ4)*0.09615
>
> Is it?

In article <430f846d$0$22951$ed2619ec@ptn-nntp-reader01.plus.net>,
"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:

> Excel is not accepting it for some reason. any idea where the fault may be?

If you're copying and pasting the formula into your worksheet, make sure
that a line break hasn't been added.

> im a bit confused about the bit you added in order to add the value of
> BQ4...this bit..
>
> +IF((BQ4<>"")*(BQ4>C4),C4,BQ4))
>
> why does it refer to C4?

I assumed, incorrectly it appears, that BQ4 would following the same
logic as cells in G4:BJ4. Maybe this what you mean...

=(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
4,G4:BJ4)))+BQ4)*0.09615

Is it?

im a bit confused about the bit you added in order to add the value of
BQ4...this bit..

+IF((BQ4<>"")*(BQ4>C4),C4,BQ4))

why does it refer to C4?

cheers!

steve

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-A9E1DE.16314226082005@msnews.microsoft.com...
> Try...
>
> =(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
> 4,G4:BJ4)))+IF((BQ4<>"")*(BQ4>C4),C4,BQ4))*0.09615
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <430f71ff$0$22930$ed2619ec@ptn-nntp-reader01.plus.net>,
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>
>> Thats working great! thanks! next question, i want to add the value
>> contained in BQ4 to the array just before the final *0.09615
>> calculation...can you help?
>>
>> Thanks!
>>
>> steve

In article <430f8f6d$0$17502$ed2e19e4@ptn-nntp-reader04.plus.net>,
"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:

> ...there seems to be a problem with the original part now. where i
> wanted each 5th cell to be read, if the value of that cell was higher than
> cell C4 then C4's value would take precedent.

Can you elaborate? Maybe provide an example?

Would you perhaps be interested in a *non-array* formula?

Try this:

=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=C4)*G4:BJ4)+C4*SUMPRODUCT((M
OD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>C4))+BQ4)*0.09615

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
news:430f8f6d$0$17502$ed2e19e4@ptn-nntp-reader04.plus.net...
> That's it! Brilliant!
>
> however there seems to be a problem with the original part now. where i
> wanted each 5th cell to be read, if the value of that cell was higher than
> cell C4 then C4's value would take precedent.
>
> thanks for your help!
>
> steve
>
> "Domenic" <domenic22@sympatico.ca> wrote in message
> news:domenic22-9B9BF3.17372426082005@msnews.microsoft.com...
> > In article <430f846d$0$22951$ed2619ec@ptn-nntp-reader01.plus.net>,
> > "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
> >
> >> Excel is not accepting it for some reason. any idea where the fault
may
> >> be?
> >
> > If you're copying and pasting the formula into your worksheet, make sure
> > that a line break hasn't been added.
> >
> >> im a bit confused about the bit you added in order to add the value of
> >> BQ4...this bit..
> >>
> >> +IF((BQ4<>"")*(BQ4>C4),C4,BQ4))
> >>
> >> why does it refer to C4?
> >
> > I assumed, incorrectly it appears, that BQ4 would following the same
> > logic as cells in G4:BJ4. Maybe this what you mean...
> >
> >
=(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
> > 4,G4:BJ4)))+BQ4)*0.09615
> >
> > Is it?
>
>

Getting error message NAME?

steve

"RagDyer" <RagDyer@cutoutmsn.com> wrote in message
news:%23LhVmZqqFHA.2244@tk2msftngp13.phx.gbl...
> Would you perhaps be interested in a *non-array* formula?
>
> Try this:
>
> =(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=C4)*G4:BJ4)+C4*SUMPRODUCT((M
> OD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>C4))+BQ4)*0.09615
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
> news:430f8f6d$0$17502$ed2e19e4@ptn-nntp-reader04.plus.net...
>> That's it! Brilliant!
>>
>> however there seems to be a problem with the original part now. where i
>> wanted each 5th cell to be read, if the value of that cell was higher
>> than
>> cell C4 then C4's value would take precedent.
>>
>> thanks for your help!
>>
>> steve
>>
>> "Domenic" <domenic22@sympatico.ca> wrote in message
>> news:domenic22-9B9BF3.17372426082005@msnews.microsoft.com...
>> > In article <430f846d$0$22951$ed2619ec@ptn-nntp-reader01.plus.net>,
>> > "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>> >
>> >> Excel is not accepting it for some reason. any idea where the fault
> may
>> >> be?
>> >
>> > If you're copying and pasting the formula into your worksheet, make
>> > sure
>> > that a line break hasn't been added.
>> >
>> >> im a bit confused about the bit you added in order to add the value of
>> >> BQ4...this bit..
>> >>
>> >> +IF((BQ4<>"")*(BQ4>C4),C4,BQ4))
>> >>
>> >> why does it refer to C4?
>> >
>> > I assumed, incorrectly it appears, that BQ4 would following the same
>> > logic as cells in G4:BJ4. Maybe this what you mean...
>> >
>> >
> =(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
>> > 4,G4:BJ4)))+BQ4)*0.09615
>> >
>> > Is it?
>>
>>
>

Yes, what I'm trying to do is get excel to calculate accumulated holiday
entitlement. If and employee works 169 hours a month, then she accumulates
holiday at a rate of .09615 hours per hour worked (working on an example of
a 5 week holiday entitlement)

in cell G4 and every 5th cell there after for 12 occurrences, is the number
of hours worked per month. if during any of these months, they work over
what they are contracted to work (C4) then the value of C4 is taken as the
figure worked for that month.

add these 12 months up, then add on to this figure $BQ4 which is the total
of hours holiday already taken during the year (because holiday is
accumulated during paid holidays also) and multiply the total of these
calculations by .09615

this results in a running total of accumulated hours as we go through the
year.

Hope this helps.

TIA

Steve

i.e. if in month
"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-68D77B.18500226082005@msnews.microsoft.com...
> In article <430f8f6d$0$17502$ed2e19e4@ptn-nntp-reader04.plus.net>,
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>
>> ...there seems to be a problem with the original part now. where i
>> wanted each 5th cell to be read, if the value of that cell was higher
>> than
>> cell C4 then C4's value would take precedent.
>
> Can you elaborate? Maybe provide an example?

Look out for "word wrap".

Could be one of the functions might be missing a letter or have something
extra added.

With this formula, that error should only come up if something is
misspelled.

You *did copy* the formula, didn't you, without any manual entries?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------



"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
news:430fee64$0$97099$ed2619ec@ptn-nntp-reader03.plus.net...
Getting error message NAME?

steve

"RagDyer" <RagDyer@cutoutmsn.com> wrote in message
news:%23LhVmZqqFHA.2244@tk2msftngp13.phx.gbl...
> Would you perhaps be interested in a *non-array* formula?
>
> Try this:
>
>
=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=C4)*G4:BJ4)+C4*SUMPRODUCT((M
> OD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>C4))+BQ4)*0.09615
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
> news:430f8f6d$0$17502$ed2e19e4@ptn-nntp-reader04.plus.net...
>> That's it! Brilliant!
>>
>> however there seems to be a problem with the original part now. where i
>> wanted each 5th cell to be read, if the value of that cell was higher
>> than
>> cell C4 then C4's value would take precedent.
>>
>> thanks for your help!
>>
>> steve
>>
>> "Domenic" <domenic22@sympatico.ca> wrote in message
>> news:domenic22-9B9BF3.17372426082005@msnews.microsoft.com...
>> > In article <430f846d$0$22951$ed2619ec@ptn-nntp-reader01.plus.net>,
>> > "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>> >
>> >> Excel is not accepting it for some reason. any idea where the fault
> may
>> >> be?
>> >
>> > If you're copying and pasting the formula into your worksheet, make
>> > sure
>> > that a line break hasn't been added.
>> >
>> >> im a bit confused about the bit you added in order to add the value of
>> >> BQ4...this bit..
>> >>
>> >> +IF((BQ4<>"")*(BQ4>C4),C4,BQ4))
>> >>
>> >> why does it refer to C4?
>> >
>> > I assumed, incorrectly it appears, that BQ4 would following the same
>> > logic as cells in G4:BJ4. Maybe this what you mean...
>> >
>> >
> =(SUM(IF((G4:BJ4<>"")*(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0),IF(G4:BJ4>C4,C
>> > 4,G4:BJ4)))+BQ4)*0.09615
>> >
>> > Is it?
>>
>>
>

Right. this is working well.

=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=((C4*52)/12))*G4:BJ4)+((C4*52)/12)*SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>((C4*52)/12)))+BQ4)*0.09615

problem is, i've copied and pasted this formula to the next column but its
not working right there. even though the cells are just one cell off the
pervious one. what i am trying to work out here is sleepin holiday
allowance. it works out exactly the same way as for hours. i am putting in
exactly the same values as for hours but the calculation is returning low.
16.25 down.

=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*52)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4>((D4*52)/12)))+BR4)*(D4*$E4)/(D4*52)

any ideas? i suppose i could just ask it to add the 16.25....but I'd like
to understand why its doing this!

TIA

steve



"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
news:430ff1d1$0$97115$ed2619ec@ptn-nntp-reader03.plus.net...
> Yes, what I'm trying to do is get excel to calculate accumulated holiday
> entitlement. If and employee works 169 hours a month, then she
> accumulates holiday at a rate of .09615 hours per hour worked (working on
> an example of a 5 week holiday entitlement)
>
> in cell G4 and every 5th cell there after for 12 occurrences, is the
> number of hours worked per month. if during any of these months, they
> work over what they are contracted to work (C4) then the value of C4 is
> taken as the figure worked for that month.
>
> add these 12 months up, then add on to this figure $BQ4 which is the total
> of hours holiday already taken during the year (because holiday is
> accumulated during paid holidays also) and multiply the total of these
> calculations by .09615
>
> this results in a running total of accumulated hours as we go through the
> year.
>
> Hope this helps.
>
> TIA
>
> Steve
>
> i.e. if in month
> "Domenic" <domenic22@sympatico.ca> wrote in message
> news:domenic22-68D77B.18500226082005@msnews.microsoft.com...
>> In article <430f8f6d$0$17502$ed2e19e4@ptn-nntp-reader04.plus.net>,
>> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote:
>>
>>> ...there seems to be a problem with the original part now. where i
>>> wanted each 5th cell to be read, if the value of that cell was higher
>>> than
>>> cell C4 then C4's value would take precedent.
>>
>> Can you elaborate? Maybe provide an example?
>
>

Oops, ignore the different end bit...just see it as 0.09615
ie

=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=((C4*52)/12))*G4:BJ4)+((C4*52)/12)*SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>((C4*52)/12)))+BQ4)*0.09615

works

=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*52)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4>((D4*52)/12)))+BR4)*0.09615

returns 16.25 lower despite same figures

cheers

steve

Since you changed your starting column from G to H (Column7 to Column8),
Your Mod formula should have a remainder of 3 instead of 2 (7-5=2, 8-5=3)

Revise your formula to this, and see if it works:

=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=3)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*5
2)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=3)*(H4:BK4>((D4*52)/12)))+BR4)*(D4*
$E4)/(D4*52)

If you still have a problem after this change, I would suspect your logic,
in the way you constructed those final two ending calculations, because the
formula is now syntactically correct.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
news:4310236e$0$17462$ed2e19e4@ptn-nntp-reader04.plus.net...
Oops, ignore the different end bit...just see it as 0.09615
ie

=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=((C4*52)/12))*G4:BJ4)+((C4*5
2)/12)*SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>((C4*52)/12)))+BQ4)*0.09
615

works

=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*5
2)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4>((D4*52)/12)))+BR4)*0.09
615

returns 16.25 lower despite same figures

cheers

steve

Bang on mate! Thanks for everyones help!

steve


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:%23Z$dr$vqFHA.3352@TK2MSFTNGP14.phx.gbl...
> Since you changed your starting column from G to H (Column7 to Column8),
> Your Mod formula should have a remainder of 3 instead of 2 (7-5=2, 8-5=3)
>
> Revise your formula to this, and see if it works:
>
> =(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=3)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*5
> 2)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=3)*(H4:BK4>((D4*52)/12)))+BR4)*(D4*
> $E4)/(D4*52)
>
> If you still have a problem after this change, I would suspect your logic,
> in the way you constructed those final two ending calculations, because
> the
> formula is now syntactically correct.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
> news:4310236e$0$17462$ed2e19e4@ptn-nntp-reader04.plus.net...
> Oops, ignore the different end bit...just see it as 0.09615
> ie
>
> =(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=((C4*52)/12))*G4:BJ4)+((C4*5
> 2)/12)*SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>((C4*52)/12)))+BQ4)*0.09
> 615
>
> works
>
> =(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*5
> 2)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4>((D4*52)/12)))+BR4)*0.09
> 615
>
> returns 16.25 lower despite same figures
>
> cheers
>
> steve
>
>
>

Glad you finally got it!

Appreciate the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"R.P.McMurphy" <rpmcmurphy@ntlworld.com> wrote in message
news:431079ab$0$17489$ed2e19e4@ptn-nntp-reader04.plus.net...
Bang on mate! Thanks for everyones help!

steve


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:%23Z$dr$vqFHA.3352@TK2MSFTNGP14.phx.gbl...
> Since you changed your starting column from G to H (Column7 to Column8),
> Your Mod formula should have a remainder of 3 instead of 2 (7-5=2, 8-5=3)
>
> Revise your formula to this, and see if it works:
>
>
=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=3)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*5
>
2)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=3)*(H4:BK4>((D4*52)/12)))+BR4)*(D4*
> $E4)/(D4*52)
>
> If you still have a problem after this change, I would suspect your logic,
> in the way you constructed those final two ending calculations, because
> the
> formula is now syntactically correct.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "r.p.mcmurphy" <redleadertwoSPACE@SPACEntlworld.com> wrote in message
> news:4310236e$0$17462$ed2e19e4@ptn-nntp-reader04.plus.net...
> Oops, ignore the different end bit...just see it as 0.09615
> ie
>
>
=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=((C4*52)/12))*G4:BJ4)+((C4*5
>
2)/12)*SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4>((C4*52)/12)))+BQ4)*0.09
> 615
>
> works
>
>
=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4<=((D4*52)/12))*H4:BK4)+((D4*5
>
2)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4>((D4*52)/12)))+BR4)*0.09
> 615
>
> returns 16.25 lower despite same figures
>
> cheers
>
> steve
>
>
>