"John48" skrev i en meddelelse
...
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