MS Excel formula question.

[Big Al.]

OK Peeps. Need some help please with a EXEL formula please.

Here’s the scenario!

Blank spreadsheet, in Row A, I need 52 weeks and two additional columns, Titled:- Total and 12 Week Usage.

Row B will have values entered under the respective weeks.

The Row B first Total Column will be the sum of the previous 52 weekly entries. So far so good! I can do that.

Now here’s where I need assistance.

In Row B In the 12 Week Usage column, I require a 12 week Total usage calculation, this will be the sum of the current week and the last 11 weeks.

Now what I would ideally like to happen with this, is that this 12 week Usage column becomes a Running 12 Week Usage Total and automatically adjust itself as the weeks progress though the year.

i.e
If we are in week 43 this week, it totals all the figures back to week 31 into the cell
Next week it would automatically total all the weeks from week 44 back to week 32 in that cell.
and so on etc.

Can it be done? If so how?

TYIA for your assistance.

[Miles_stylus]

Sorry, I'm being thick here but I can picture what you are talking about = Row A (is this column A?) with two additional columns (B and C?) and then you talk about Column B having something else in!

Sorry if I'm being thick but can you re-state the situation using Rows and columns references?

Thanks

Miles

[Big Al.]

Thanks For the prompt reply I'll be back shortly...

[Big Al.]

OK cell references…..
In cell A1 = Header = WK1
In cell B1 = WK2
In cell C1 = WK3 etc, etc to WK52 in cell AZ1
Then in cell BA one Header = Total
In the next cell BB1 Header = 12 Week Usage

Now in cell A2 thought to cell AZ2, I enter weekly usage figures.
Cell BA2 will sum the cells A2 to AZ2.
Cell BB2 is the cell I want to do the rolling 12 week usage figure.

Does that make sense?

Hope it does.

If it don't drop me a mail and I'll attach an example spread sheet.

Thanks again.

[Kinky]

So basically, as I understand it, all you need to do is calculate what this week is, then add up this week + the 11 previous weeks?

K

[Kinky]

Al,

The lazy way to do it is to simply add the formula for each week of the year - so it's already done.

And then use a formula to display the contents of the cell that matches this weeks number.

I hope that makes sense?

K

Edited by Kinky on Wednesday 25th October 18:31

[roy the boy]

I've taken it as column A and column B so the 52 weeks run down - seems easier to me to have the row number correspond to the week number.

Try this - may suit

Cell A55 put formula - =COUNTBLANK(B1:B52) (you can hide the contents of this cell)
Cell B54 put formula - =SUM(OFFSET($B$1,40-$A$55,0,12,1))

Basically , A55 counts the number of blank cells, then B54 sums 12 rows down starting from 12 rows up from the last blank cell. You will get a #ref error in B54 until 12 weeks figures have been entered, but after that, providing the cells below are blank, it should work.

Doing it your way

Cell BC1 put formula - =COUNTBLANK(A2:AZ2)
Cell BB2 put formula - =SUM(OFFSET($A$2,0,40-$BC$1,1,12))

Edited by roy the boy on Wednesday 25th October 19:42

[Big Al.]

Kinky said:

So basically, as I understand it, all you need to do is calculate what this week is, then add up this week + the 11 previous weeks?

K

By jove I think he's got it!

But It's got to be a rolling total i.e. drop the last (13th) week, and add on the next.

[Big Al.]

roy the boy said:

I've taken it as column A and column B so the 52 weeks run down - seems easier to me to have the row number correspond to the week number.

Try this - may suit

Cell A55 put formula - =COUNTBLANK(B1:B52) (you can hide the contents of this cell)
Cell B54 put formula - =SUM(OFFSET($B$1,40-$A$55,0,12,1))

Basically , A55 counts the number of blank cells, then B54 sums 12 rows down starting from 12 rows up from the last blank cell. You will get a #ref error in B54 until 12 weeks figures have been entered, but after that, providing the cells below are blank, it should work.

Edited by roy the boy on Wednesday 25th October 19:27

Sound the biz... Hav'nt got around to trying it out yet.

The reason for going across is I have lots of part numbers that will need to go down the page.

[roy the boy]

[redacted]

[Big Al.]

That appears to have done the trick, It worked on a test file, I'll try it in situ the the pukker file tomorrow.

Thanks again.