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

=SUM(IF(G4:BJ4C4,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:BJ4C4),C4
,G4:BJ4)))*0.09615

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

Hope this helps!

In article ,
"R.P.McMurphy" wrote:

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

=SUM(IF(G4:BJ4C4,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:BJ4C4,C4
,G4:BJ4)))*0.09615

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

In article ,
Domenic wrote:

Try...

=SUM(IF(MOD(COLUMN(G4:BJ4)-COLUMN(G4),5)=0,IF((G4:BJ4<"")*(G4:BJ4C4),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" wrote in message
...
Try...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"R.P.McMurphy" wrote:

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

=SUM(IF(G4:BJ4C4,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:BJ4C4,C
4,G4:BJ4)))+IF((BQ4<"")*(BQ4C4),C4,BQ4))*0.09615

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

Hope this helps!

In article ,
"r.p.mcmurphy" 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" wrote in message
...
Try...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"r.p.mcmurphy" 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

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

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

why does it refer to C4?

cheers!

steve

"Domenic" wrote in message
...
Try...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"r.p.mcmurphy" 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 ,
"r.p.mcmurphy" 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<"")*(BQ4C4),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:BJ4C4,C
4,G4:BJ4)))+BQ4)*0.09615

Is it?

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

--
Don Guillett
SalesAid Software

"R.P.McMurphy" wrote in message
...
I'm currently working with this array formula...

=SUM(IF(G4:BJ4C4,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:BJ4C4,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