How can I reference every 7th cell in a column?

[forkhandles]

Hi
I'm running 2002. I have some data and I want to set some criteria and take action , if necessary, on the 7th cell in a column, i.e. for the same day of each week. Now, I'm happy that I'm ok with the IF function, but I'm not sure how to reference the said cells to put in the function. Help, please!

Ok. Column D has various arguments in IF functions e.g. if my finish time - my start time is less than 8, assign 8; if 0 start time is assigned then assign 0 to Total. However, also I need to make it assign 9.6 to Total every Sunday if finish time - start time is less than 9.6 (excluding days off, i.e. when 0 is assigned). Hope this is clearer.

[oeldere]

I bet you get better help, if you post an example of your excel sheet (without confidentional information).

Please also add the expected result.

[dilipandey]

Hi forkhandles,

Welcome to the forum.

Try looking at Offset function to reference every 7th cell.. in case of any issues, upload an sample excel workbook.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[FDibbins]

without seeing your data/layout, its hard to say. could you post a sample workbook, showing what you are trying to do, and what your expected outcome is?

[jeversf]

offset.xlsx

Hi forkhandles,

Does this help at all? I used to offset function based on the adjacent cell being blank, but you could replace the criteria with pretty much anything you like.

[forkhandles]

Thanks, oeldere.
Here is my workbook and what I'm trying to achieve

[oeldere]

I not sure what you'r up to.

I try a solution with a pivot table.

Please reply if this is what you're up to.

[forkhandles]

Hi
I must admit,l've no idea what a pivot table is but it doesn't sound like what I want. I've tried offset too but that doesn't seem right either. Ok, Let me start at the begining:
These are the hours that I do at work. Management obviously keep a record but I've often disagreed with their Balance that they say I have (I'm contracted to do 10.37 hours per shift) so I thought i would do my own programme so I would have 'proof' that I was right. So, we are guaranteed 8 hours a day so, if less than eight hours worked, 8 is still assigned to my Total except, that is on a Sunday, when 9.6 hours is guaranteed. So, I've programmed in the 8 hours bit, easy cos it's every day but struggling on this once a week to assign a minimum of 9.6. Hope this makes it all clear now.

[oeldere]

Nope, please add your desired result in your sheet.

Please also fill the empty column (those who have an header) with data.

[forkhandles]

Hi, again, oeldere
I've entered the formula for how much above or below each days Total is from 10.37. I've put in false data for 11/10/2012 just to show that as the hours are less than 8, 8 is still assigned. The formula needs adjusting because for 30/09/12 the Total is 8.25 and, as that day was a Sunday, 9.6 needs to be assigned to the Total. The Balance is the running Total. As you see 1.58 was brought forward and, if 9.6 had been assigned to 30/10, then -0.77 would have been 'added' to 1.58. The formula for Balance isn't done yet as I want to sort this out first.

[oeldere]

I try again.

See the green cells, for the reuslt of the hours.

See the grey cells, for the result of the running totals; probably these values are false, but i don't know how you (want to) count them.

[forkhandles]

Hi oeldere
Thanks a million. I've managed to sort it all now due to your help. I'm sure it could be done in a tidier way but the main thing is it all works.

[Cutter]

@ forkhandles

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[forkhandles]

Noted. Thanks