| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How to refer to every 5 th cell?
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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? |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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! |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 |
|
#11
|
|||
|
|||
|
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? |
|
#12
|
|||
|
|||
|
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" wrote in message ... 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? |
|
#13
|
|||
|
|||
|
In article ,
"r.p.mcmurphy" 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? |
|
#14
|
|||
|
|||
|
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:BJ4C4))+BQ4)*0.09615 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "r.p.mcmurphy" wrote in message ... 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" wrote in message ... 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? |
|
#15
|
|||
|
|||
|
Getting error message NAME?
steve "RagDyer" wrote in message ... 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:BJ4C4))+BQ4)*0.09615 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "r.p.mcmurphy" wrote in message ... 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" wrote in message ... 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? |
|
#16
|
|||
|
|||
|
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" wrote in message ... In article , "r.p.mcmurphy" 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? |
|
#17
|
|||
|
|||
|
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" wrote in message ... Getting error message NAME? steve "RagDyer" wrote in message ... 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:BJ4C4))+BQ4)*0.09615 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "r.p.mcmurphy" wrote in message ... 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" wrote in message ... 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? |
|
#18
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... In article , "r.p.mcmurphy" 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? |
|
#19
|
|||
|
|||
|
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 |
|
#20
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#21
|
|||
|
|||
|
Bang on mate! Thanks for everyones help!
steve "RagDyeR" wrote in message ... 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" wrote in message ... 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 |
|
#22
|
|||
|
|||
|
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" wrote in message ... Bang on mate! Thanks for everyones help! steve "RagDyeR" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I refer to the tab name in a cell formula in Excel? | Excel Discussion (Misc queries) | |||
| macro help | Excel Discussion (Misc queries) | |||
| How do I refer a Range to a Cell | Excel Worksheet Functions | |||
| up to 7 functions? | Excel Worksheet Functions | |||
| VLookup resulting in a blank cell... | Excel Worksheet Functions | |||
Linear Mode
