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 


0
rpmcmurphy (23)
8/26/2005 4:56:54 PM
excel.misc 78881 articles. 5 followers. Follow

21 Replies
645 Views

Similar Articles

[PageSpeed] 46

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
0
domenic22 (716)
8/26/2005 5:50:29 PM
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
>
>


0
Don
8/26/2005 5:52:23 PM
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?

0
CycleZen (674)
8/26/2005 6:01:27 PM
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 
> 
> 
> 
0
FSt1 (238)
8/26/2005 6:58:56 PM
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!
0
domenic22 (716)
8/26/2005 7:41:40 PM
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 


0
8/26/2005 7:48:13 PM
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
0
domenic22 (716)
8/26/2005 8:31:42 PM
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 


0
8/26/2005 8:58:28 PM
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 


0
8/26/2005 9:06:52 PM
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?
0
domenic22 (716)
8/26/2005 9:37:24 PM
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? 


0
8/26/2005 9:53:48 PM
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?
0
domenic22 (716)
8/26/2005 10:50:02 PM
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?
>
>

0
ragdyer1 (4060)
8/27/2005 1:18:26 AM
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?
>>
>>
> 


0
8/27/2005 4:38:55 AM
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? 


0
8/27/2005 4:53:35 AM
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?
>>
>>
>



0
ragdyer1 (4060)
8/27/2005 5:01:44 AM
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?
>
> 


0
8/27/2005 8:16:57 AM
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 


0
8/27/2005 8:25:16 AM
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



0
ragdyer1 (4060)
8/27/2005 12:07:32 PM
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
>
>
> 


0
rpmcmurphy (23)
8/27/2005 2:32:49 PM
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
>
>
>



0
ragdyer1 (4060)
8/27/2005 5:13:37 PM
Reply:

Similar Artilces:

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

loss of connection to e2k3 server after 4 or 5 days uptime
Hi all, I'm having a strange issue with Outlook & our Exchange server. This is our topology (all one domain): Datacenter: 2 Windows 2003 SP1 Enterprise Domain Controllers 1 Windows 2003 SP1 Enterprise Exchange 2003 SP1 w/KB894549 Enterprise Back-end 1 Windows 2000 SP4 Advanced Server Exchange 2003 SP1 w/KB894549 Enterprise Front-end | site-to-site IPSec VPN w/CheckPoint R55 (Datacenter) & Cisco 1721 (Corporate Office) | Corporate Office: 1 Windows 2000 SP4 Advanced Server Domain Controller 50 Windows XP SP1/SP2 PCs w/Outlook 2003 SP1 What happens is that after 4 or 5 days ...

Exchange 5.5 / Exchange 2003
We migrated our NT4 network into a Win2k (SP4) network using Active Directory. Our 5.5 exchange server is in this forest/domain and running on a Windows 2000 member server. We have 1 forest and 1 domain. We want to replace the existing exchange server with new hardware and it will have Exchange 2003 on an Windows Server 2003. The questions: 1. I presume that the new server has to have the same name so that we don't have to change our clients' settings. 2. What is the best method to migrate the mail to the new server? 3. Any issues with running Exchange 2003 on a 2000 A/D? (I ...

00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very well thanks to RD All I need to do now is sort out the date this is copied from sheet1 onto the totals page and works fine when the date is put into sheet1. However if the cell is blank on sheet2 the date is put into the totals sheet as 00/01/1900 when the date is put in sheet2 all is well with the right date entered into the totals sheet. So how do I get the totals date to stay blank until the weekly sheets have a date put into the sheet please. the format I am using is below many thanks for all the help I ...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

Meetings Exchange 5.5
Dear, Dear, I always receives a "Not read" message form a person which I didn't invite for an appointment, everytime I invite another and always the same colleague. We're from IT, but haven't had this kind of problem before. Please give any advice where to search .... Hope our Exchange 5.5 (W2000 clients) isn't "tripping"? Thanks, S. Does the user you are sending the meeting request to have a delegate set?, is is calendar set to send meeting requests to both the user and the delegate. "Steve" <Steve@steve.com> wrote in message news:3...

Problem with Outlook 2003 & Exchange Server 5.5
It seems Outlook 2003 causes my Exchange Server 5.5 Sp4 to hang. I'm running Windows 2000 on the Exchange server and WinXP on the client running Outlook 2003 There is a notice on microsofts website about installing a patch for Exchange. Has to do with rules processing. "Scott" <anonymous@discussions.microsoft.com> wrote in message news:0b0501c39efa$5fd011b0$a601280a@phx.gbl... > It seems Outlook 2003 causes my Exchange Server 5.5 Sp4 > to hang. I'm running Windows 2000 on the Exchange server > and WinXP on the client running Outlook 2003 ...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Help: Disclaimer on Exchange5.5
I have implement imsext.dll on Exchange 5.5 Server. and it works for inboundappend and inboundprepend, however, it doesn't work for outboundappend and outboundprepend. I appreciate for any comments or advices. "Little Cat" wrote: > I have implement imsext.dll on Exchange 5.5 Server. and it works for > inboundappend and inboundprepend, however, it doesn't work for > outboundappend and outboundprepend. > > I appreciate for any comments or advices. > >LC, can i ask where you got the imsext.dll file? I'm try to outbound pre-prend on Exch...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Data Validation in cell
Hi I have a sales order form with the equipment on the second sheet as "PartNo" "Description" "Cost" selected each column of data then clicked in the cellname box and called them "PartNo" "Description" "Cost" On the sales order form I have the columns "Qty" PartNo" "Description" "Serial No" "QtyDes" "Price" Total" I have set up data validation for the description as a list with =Description what I want it to do is display the part no in the part number Column and the cost in...

How do Macro to EDIT cells?
Am using MS Excel XP (2002). All I want to do is a short macro to, in effect, press F2 (to EDIT the cell), then let's say delete the first character of the cell and change it to let's say X, like this... Before: _Mary Jones *Joe Smith %Peter Rabbbit After: XMary Jones XJoe Smith XPeter Rabbbit I just want to put the cursor on a given cell, click CNTL-A to run the macro, and have it change one cell at a time. There are reasons why I can't use the search and replace. Problem is... if I record the macro (turn it on, press the key sequences, F2, Home, Delete, X, GoDownOneCell, ...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

customer loyalty
does anyone have a recomendation for how to handle this type of loyalty program? Simply we were thinking we could generate a report that shows all customers with sales over $300 and then manually assign them vouchers but this could be time consumming. Also - how do you give a customer a gift voucher - you would have to sell it to them to track it voucher # at redemption but how do you sell it without selling it? Any thoughts? all the loyalty programs I have found are based on points. My customer wants it simply based on net sales. There is a customer loyalty addon that supports thi...

Exchange 5.5 Maintenance not happening?
I run an Exchange 5.5 server (SP4). I'm looking for the entry in the event log that tells me how much whitespace was in the IS. But there are no events for the information store in the application log. I check the Exchange Administrator and it says that maintnenance is to happen between 1 and 6AM. Are there other settings that affect IS maintenance? Dean You want to make sure the time allowed for backup doesn't overlap with the time of the maintenance. The events you are looking for in the application log are events 1221. -- Michael Barta [MSFT] Microsoft Exchange Support Pl...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Cells do not Retain Formatting After Pasting.
I have very a simple spreadsheet with lists of names, addresses, telephone numbers etc. I have preset the formatting for the telephone number column, to "text" and centre alignment. When I paste in a telephone number from Outlook Express such as 01282830756 it changes the format and I lose the first 0 and the alignment is from the right. After pasting I have to reformat to text and centre alignment. This is a real pain and time consuming. Is there any way to cure this and force Excel to retain the formatting I have set? Robert Paste the values only. Edit, Paste Special, ...

Which function to use? Trying to ref a cell based on another.
I'm starting to get confused by the vocabulary in Excel. I'm not necessarily looking for someone to just give me a solution to my problem. I'd just like some advice on which functions I should be reading up on. I'm still a newbie to the formulas. If Excel would let me, the closest I could express what I'm trying to do is something like this. =IF(A3=sheet2!A$:A$, sheet2!H$:H$ Of course this doesn't work b/c I don't know what I'm doing. I know this is simple to some of you gurus out there, everybody starts somewhere. To explain what I'm trying to...

Remove last 5.5 server
I am trying to remove our last 5.5 server and get the following error message: "The server could not be deleted because some public folders were not successfully moved to a different server. You do not have the permissions required to complete the operation on the information store. Microsoft ID: c1040af3" I have gone through the MS document on this and believe I have the necessary permissions set. Any idea's on where I should start looking? Have you moved the System folders as well as the regular public folders? -- Teresa Appelgate Exchange User Education ****** Discla...

Make pasted value in last blank cell the focus
Whichever cell has the focus when the following code is run that is the row that will be copied into any other sheet in the range. What I need is the row containing the last blank cell in "B" in which the value of "B9" take the focus, although the exact row to be copied wont be determined until the sheet has first been sorted. Here is the code in it entirety: Option Explicit Option Compare Text Private Sub AddRow_Click() Dim rng As Range Dim lr As Long Dim sh As Worksheet Dim ws As Worksheet Dim i As Integer Dim FD As String 'find string ...

Exchange 5.5 : How to move users to new domain?
I have 15 users on an Exchange 5.5 / NT 4.0 domain. The NT 4.0 PC is the PDC of the domain (no BDC). I need to move those users into an existing Server 2003 Active Directory domain and do so without losing their Exchange 5.5 mailboxes. What would be the best way to do this? The main question is what you want to do with Site Service account, which is cornerstone to Exchange operations? Rick Troha wrote: > I have 15 users on an Exchange 5.5 / NT 4.0 domain. > The NT 4.0 PC is the PDC of the domain (no BDC). > > I need to move those users into an existing Server > 2003 Activ...

Unwanted blank line in cell with wrapped text
Unwanted blank line sometimes appears in cell with wrapped text. Editing, Autofit height and width, and justify do not eliminate it. Forcing column width to be excessively wide makes it go away, as does reducing the font size (neither of which I want to do). What's going on? This only happens in some wrapped text cells. bbl If you uncheck "wrap text" do you see a little square box in the text where the blank line is? If so, someone entered a new line using ALT + ENTER. In that case, you can get rid of these boxes by Edit>Replace what: hit ALT + 0010(you won't se...

How to convert text-cells
Hi, I've problems to import Excel-spreadsheet-data into our database. I'm using Borland-Delphi-ADO, but a test with MS-Access importing feature leeds me into the same problems. The spreadsheet contains a col with only 9-digit values, but they have got the text-attribut except of two that are numbers. I see this when I delete the aligning-property, all cells are left justified, but the two right. I know I can give a col the text-attribute while importing data from a textfile. But I dont know how to delete the text-attribute or set it for a complete col. Best regards Dietmar "...

Nested IF that will ignore blank (empty) cells
Hi, I have a simple nested IF function (Excel 2003) which compares numbers in two cells, then returns a word depending on whether one number is greater than, equal to, or less than the other: =IF(F3>G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No"))) Either (or both) of the compared cells might contain the number 0 (zero) in which case I want the IF function to display the appropriate word. However if the compared cells are blank (have no data in them), my unsophisticated IF function thinks that F3=G3 and returns the word "Equal". How can I get the ...