asked on

Calculate midpoint of quarter to last years midpoint of quarter

QoQ.xlsx

I have some formulas comparing entire quarters (every 13 weeks) of a year to entire quarters of the previous year, but how can I compare the mid points (weeks) of the same? So week 14-16 of 2020 vs week 14-16 of 2019, and then later week 14-17 of 2020 to week 14-17 of 2019. And after week 26, start from week 27 (week 27-28 vs week 27-18) etc....

Saqib Husain

Can you show the expected results on the file and how would you do it manually?

Ejgil Hedegaard

You can sum the periods.
See sheet column H:L.
QoQ.xlsx

Flyster

You can you the SUMIFS formula like this:

=SUMIFS(C:C,B:B,">"&13,B:B,"<"&17,A:A,A14)

Open in new window

Where your sum range is column C, 1st criteria is column B (Week) is more than 13, 2nd criteria is column B is less than 17 (Giving you a week range of 14-16) and the 3rd criteria is equal to the year equal to the row this formula is placed in. As in this case it's in F14 giving a value of 157. Is this the result you were looking for?

Paul

Flyster

Slight correction, this formula allows you to enter the actual weeks needed:

=SUMIFS(C:C,B:B,">="&14,B:B,"<="&16,A:A,A14)

Open in new window

newparadigmz

ASKER

QoQ.xlsx

That seems pretty close to what I need but the 14 and 16 in that example need to be dynamic, and able to "rollover" when there's a new quarter. Should be clearer in the example I updated. Column F has two formulas (highlighted/non highlighted). So looking for 1 formula smart enough to encapsulate both, but also a way to ask, "what is the difference between this mid quarter point in time to last years same period?" Any incremental solutions are greatly appreciated!!

Flyster

Unfortunately I'm unable to directly upload your workbook as it comes up as being corrupt. I can import it into a new workbook but I only get hard numbers, not formulas. A simple sum formula gives you the mid quarter results, such as =SUM(C54:C56) in H55. The previous year value just refers to the same week as the last year (=H3 in I55)

QoQ_040620.xlsx

newparadigmz

ASKER

This is what I was trying to do.

On column D and drag down:

=IF(C2<>"",IF(OR((B2=1),(B2=14),(B2=27),(B2=40)),C2,C2+D1),"")

Open in new window

Thanks for the suggestions!

ASKER CERTIFIED SOLUTION

Flyster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newparadigmz

ASKER

Yes, totally, Thank You!

I prefer what I came up with for simplicity, but was definitely asking for your type of answer. Will need need some time to unravel that a bit but it def works.

Flyster

Thanks! Glad it worked for you.