formula problem

[John48]

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

 

[Leo Heuser]

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: A13 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 N13

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.

 

[John48]

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]

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

Hi John

I imagined a sheet with max 53 weeks. Week 1 in columns A:C, week 2
in D:F, week 3 in G:I,......, week 53 in columns FA:FC and PTD in for
instance FE:FG, but from your description it's probably YTD in FE:FG??

I'm probably dense, but I fail to see the difference between PTD and YTD!?
How is "Period" defined?

Then in FE3 you enter the formula:

=SUMPRODUCT(N(OFFSET(A3,,(ROW(INDIRECT("1:53"))-1)*3)))

And in FF3 this array formula:
=SUM(IF(N(OFFSET(A3,,(ROW(INDIRECT("1:53"))-1)*3)),N(OFFSET(B3,,(ROW(INDIRECT("1:53"))-1)*3))))

[("1:4") doesn't change automatically. You have to enter ("1:53") yourself.]

Apparently your sheet is not as I imagined, so you are welcome to
attach a copy to a personal mail, so I can have a look at it.
My mail address is: leo.heuser at adslhome.dk


Leo Heuser

 

[John48]

Thanks Leo...I wasn't too clear in my initial posting....PTD is the 4 week
Period...and there are 13 Period is the year..hence YTD. Gets a little more
complicated on the YTD. Each sheet is a period, there are 13 sheets and on
each sheet is the PTD and the YTD with the YTD carrying foreward from P1 to
P13!!
Anyway..I sent you the sheet..hope it is ok...tx a million rgds John

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

Hi John

I imagined a sheet with max 53 weeks. Week 1 in columns A:C, week 2
in D:F, week 3 in G:I,......, week 53 in columns FA:FC and PTD in for
instance FE:FG, but from your description it's probably YTD in FE:FG??

I'm probably dense, but I fail to see the difference between PTD and YTD!?
How is "Period" defined?

Then in FE3 you enter the formula:

=SUMPRODUCT(N(OFFSET(A3,,(ROW(INDIRECT("1:53"))-1)*3)))

And in FF3 this array formula:
=SUM(IF(N(OFFSET(A3,,(ROW(INDIRECT("1:53"))-1)*3)),N(OFFSET(B3,,(ROW(INDIRECT("1:53"))-1)*3))))

[("1:4") doesn't change automatically. You have to enter ("1:53") yourself.]

Apparently your sheet is not as I imagined, so you are welcome to
attach a copy to a personal mail, so I can have a look at it.
My mail address is: leo.heuser at adslhome.dk


Leo Heuser

 

[John48]

Hi again Leo...I was premature in thinking I needed additional formula for
the YTD, the PTD formulas work well in conjunction with the YTD formulas I
already have in place I think...but there may be a better way!! thanks again
John

Thanks Leo...I wasn't too clear in my initial posting....PTD is the 4 week
Period...and there are 13 Period is the year..hence YTD. Gets a little more
complicated on the YTD. Each sheet is a period, there are 13 sheets and on
each sheet is the PTD and the YTD with the YTD carrying foreward from P1 to
P13!!
Anyway..I sent you the sheet..hope it is ok...tx a million rgds John

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

Hi John

I imagined a sheet with max 53 weeks. Week 1 in columns A:C, week 2
in D:F, week 3 in G:I,......, week 53 in columns FA:FC and PTD in for
instance FE:FG, but from your description it's probably YTD in FE:FG??

I'm probably dense, but I fail to see the difference between PTD and YTD!?
How is "Period" defined?

Then in FE3 you enter the formula:

=SUMPRODUCT(N(OFFSET(A3,,(ROW(INDIRECT("1:53"))-1)*3)))

And in FF3 this array formula:
=SUM(IF(N(OFFSET(A3,,(ROW(INDIRECT("1:53"))-1)*3)),N(OFFSET(B3,,(ROW(INDIRECT("1:53"))-1)*3))))

[("1:4") doesn't change automatically. You have to enter ("1:53") yourself.]

Apparently your sheet is not as I imagined, so you are welcome to
attach a copy to a personal mail, so I can have a look at it.
My mail address is: leo.heuser at adslhome.dk


Leo Heuser

 

[Leo Heuser]

Hi again Leo...I was premature in thinking I needed additional formula for
the YTD, the PTD formulas work well in conjunction with the YTD formulas I
already have in place I think...but there may be a better way!! thanks
again
John

Hi again John

I have just returned the workbook.
Please let me know, if it works.

Regards
Leo Heuser

 

[John48]

Hi Leo...it is great...tx a million...now if I could just understand the
formula!!! Is there a reference I could read to get a better grasp of just
how it works...tx again, your help was really appreciated. rgds john

 

[Leo Heuser]

Hi Leo...it is great...tx a million...now if I could just understand the
formula!!! Is there a reference I could read to get a better grasp of just
how it works...tx again, your help was really appreciated. rgds john

Hi John

You're welcome and thank you for your feedback.

To the best of my knowledge no book about array formulae exist.

In various books a chapter may be included (for example in
John Walkenbach's "Excel 2000 Formulas").

You can also find an explanation in this article by Bob Umlas:

www.emailoffice.com/excel/arrays-bobumlas.html

Regards
Leo Heuser