# Thread: Cell calculations

1. ## Cell calculations

Hi,

I have a spreadsheet with date along the top column and as year progresses the column are filled in.

What I would like to do is have a final column that calculates the difference between year start and the current position automatically.

Hope I have explained myself

2. Hi

We need a little more info.
How many columns are you using? 365??
Are the dates entries going across by day? or by month?

zeddy

3. The heading will be by week so 52/53 column headings.

On the rows will be namess

4. Hi

Still not sure what you require.

Lets start with a sample file (see attached file)
This sample file has 53 columns, for Week1 to Week53
Enter a 'start date' in cell [C2]

I chose a week-ending date of Fri 2-Jan-2015 as my start date.
You could pick any date e.g. ending on a Sunday, or starting on a Monday etc. etc.
Each subsequent week is just adding 7 days to this.

Cell [BE2] has a formula which shows today's date.
Formula in cell [BF2] then returns the appropriate week number.

I added some random numbers as test data in the week columns.
Then, a formula in column [BD] adds the total for weeks 1 up to the current week.
Formulas in column [B] show year-to-date results.
This is to allow the data to be shown (using windows-freeze-panes) as you scroll across through the weeks. (You could use vba to 'lock future weeks' to prevent data entry etc etc.)

Using this file as a basis, perhaps you can tell us further what you are looking for.
Or edit the file and post back etc etc.

zeddy

5. Hi Zeddy,

thank you for that and its exactly what I am looking for bar I dont want the rows totalled up, just say the difference between the first and current week to be displayed in the split pane column.

On my version of excel I could not see any formulas in the last two columns against each row?

Thanks

6. Hi

..there are no formulas in the last two columns, just the formulas in cells [BE2] and [BF2]

See attached version2; I changed the formulas in column [BD] to subtract Week 1 value from current Week value.

zeddy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•