Statistics in Excel ?


I have a list in excel that has sales transaction from 2013 till 2015 (and on going...)

I would like to have in excel a kind of benchmark (or report) that gives me those dimensions.. and of course on a date that I would punch in (today's date ?)

YTD Dimensions (Year to Date)
YTD 2015 : from 1/1/2015 till 4/4/2015
YTD 2014 : from 1/1/2014 till 4/4/2014 (PYTD)
YTD 2013 : from 1/1/2013 till 4/4/2013 (PYTD)

YTE Dimensions (Year to End)
YTE 2015 : from 1/1/2015 till 4/4/2015 (because we are now in April  4 !)
YTE 2014 : from 1/1/2014 till 31/12/2014 (PYTE)
YTE 2013 : from 1/1/2013 till 31/12/2013 (PYTE)

QTE Dimensions (Quarter to End)
QTE 2015 : from 1/1/2015 till 3/3/2015 (because we are now in April 4 !)
QTE 2014 : from 1/1/2014 till 31/3/2014 (PQTE)
QTE 2013 : from 1/1/2013 till 31/3/2013 (PQTE)

MTD Dimensions (Month to Date)
MTD 2015 : from 1/4/2015 till 4/4/2015
MTD 2014 : from 1/4/2014 till 4/4/2014 (PMTD)
MTD 2013 : from 1/4/2013 till 4/4/2013 (PMTD)

MTE Dimensions (month to End)
MTE 2015 : from 1/4/2015 till 4/4/2015 (because we are now in April 4 !)
MTE 2014 : from 1/4/2014 till 30/4/2014 (PMTE)
MTE 2013 : from 1/4/2013 till 30/4/2013 (PMTE)
Pierre AmmounIT ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And your question is?
Pierre AmmounIT ConsultantAuthor Commented:
How can I get those columns in excel out of the raw data.
the raw data being transactions by date.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Sounds like you have identified all of the steps.

So create 6 columns, one for each result, and use a formula to copy in the transaction amount if it's relevant, eg


Then either use SUMIFs or a pivottable to create the answer.

Good luck.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Pierre AmmounIT ConsultantAuthor Commented:
No offense , but If I knew how to use those formulae I wouldn't have posted the question in the first place !
Saqib Husain, SyedEngineerCommented:
How about a sample file to ease the working of the expert?
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No offense, but our job is to answer specific questions. If you want someone to design such a spreadsheet, then you can always Hire them...
Ejgil HedegaardCommented:
Something like this to get started on.
The principle is to set the date boundaries, column D and E, and then use Sumifs in column F.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pierre AmmounIT ConsultantAuthor Commented:
Ejgil Hedegaard : Thank you so much ! !That is exactly what I was looking for !
Now with the use of your formulae  I can proceed with the full table design. Thank you.

Phillip Burton : Have a look at the answer provided and you will see that no need to get upset. If you don't want to help/Can't/ Don't know just don't do it. It is a free world man !
Cheer up !
Pierre AmmounIT ConsultantAuthor Commented:
Exactly what I needed !!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.