Thank you...the formula works very well....a couple of further questions...If
I added a Year to Date with TY LY % also after the PTD, is the formula
changed slightly, and, you mentioned that the ("1:4") should be ("1:53") in
the final set up. I Created the spread as per your instructions and after
entering the data, the formula still show ("1:4"). could you explain further
please. Thanks a million John

"Leo Heuser" wrote:

"John48" skrev i en meddelelse
...
Here is a small portion of the spreadsheet and my problem.
Week 1 Week 2 Wk 3
Period to date

TY Ly % TY LY % TY Ly %
TY LY %

12 10 20% 10 10
12 30 -60%

As you can see, I have entered data in week one as 12 versus last year's
ten
which gives me a 20% increase for the week. But as you can see in the
period
to date column which shows a total of all the three weeks as 30 the
comparison is against the 30 which includes weeks one, two and three
together. What I would like to have happen is when I enter data for week
one,
the data under the period to date also reflects that so for instance in
the
example above the TY period to date total would also show 10 for last year
to
reflect the 20% increase shown in week one. However, when I enter week
two's
data for this year...say another 12 then I would want the total in Period
to
date to show the 2 weeks sales ie 24 AND the total Period to Date for LY
to
show also the total of weeks one and two ie 20. This would show a true
comparison between this year after two weeks and last year after 2 weeks
and
the percentage would be correct. Subsequently, when I enter the third
weeks'
sales say another 12, the period to date this year would then show 36 and
the
period to date for last year would show 30. How do I create a formula
that
would allow the number in the period to date last year column to reflect
the
data entries as I make them in weeks one, then two then three?? HELP
PLEASE
John

Hi John

Here's one way:

My test range: A1:P3 containing 4 weeks.
Each week is 3 columns, Week 1: A:C, Week 2: D:F, Week 3: G:I and
Week 4: J:L
Row 1 contains headings, Week 1, Week 2, etc.
Row 2 contains headings, TY, LY, %
I have put my "Period to date" in N1:P3

In N3 enter this formula:
=SUMPRODUCT(N(OFFSET(A3,,(ROW(INDIRECT("1:4"))-1)*3)))

In O3 enter this array formula:
=SUM(IF(N(OFFSET(A3,,(ROW(INDIRECT("1:4"))-1)*3)),N(OFFSET(B3,,(ROW(INDIRECT("1:4"))-1)*3))))
Must be entered with <Shift<Ctrl<Enter, also if
edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in braces { }. Don't enter these braces
yourself. They are Excel's way of showing, that a formula is an
array formula.

("1:4") must be ("1:53") in your final setup.

--
Best regards
Leo Heuser

Followup to newsgroup only please.