Forum Discussion
Calculate same period in Excel
Hi everyone,
I am trying to use excel to achieve the following but not sure if it something possible.
For some reasons, I need it to be in excel.
I have a sale sheet with customer's sales and visit. Each Customers has a start and end membership period.
I wanted to calculate the sales & visit made by this customer within their current period.
For example, Customer A start date is in June 24 and will end May 25. Since now is March, it will calculate the total sale or visit made between June 24 till Feb 25 and compare it with same period last year (Jun23 to May 24) and then calculate the percent change.
I tried using some formula, but the calculation is wrong. For example, Customer A Visit from June24 to Feb25 should be 24 but excel answer is 22.
Also is it possible to display the monthly breakdown table ie. June24 to Feb25 and June 23 to Feb24?
Would appreciate any advice.
Thanks so much in advance.
Made some changes on the % calculation from "Visit - Sales" to "Current Sales - Previous Sales". Also added few columns for fiscal periods for the breakdown table. Made a few pivot tables in Sheet2, one of them should fit your request.
If use structured table, perhaps you mean
for current visits
=SUMIFS( [Visit], [Customer], [@Customer], [Date], ">=" & [@DateStart], [Date], "<=" & [@DateEnd] )for previous visits
=SUMIFS( [Visit], [Customer], [@Customer], [Date], ">=" & EDATE([@DateStart], -12), [Date], "<=" & EDATE([@DateEnd], -12) )Same for sales
Rather than an image, since you pretty clearly have a spreadsheet with a good set of data on Customer A, would you be willing to share that actual spreadsheet?
