LinkBack URL
About LinkBacksI'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
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 <[email protected]>,
"R.P.McMurphy" <[email protected]> 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
[email protected]
"R.P.McMurphy" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
Domenic <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "R.P.McMurphy" <[email protected]> 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 <[email protected]>,
"r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>,
> "r.p.mcmurphy" <[email protected]> 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 <[email protected]>,
"r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "r.p.mcmurphy" <[email protected]> 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 <[email protected]>,
"r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > In article <[email protected]>,
> > "r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > In article <[email protected]>,
>> > "r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>,
> "r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
Getting error message NAME?
steve
"RagDyer" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > In article <[email protected]>,
>> > "r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> In article <[email protected]>,
>> "r.p.mcmurphy" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
Bang on mate! Thanks for everyones help!
steve
"RagDyeR" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks