• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Best Year/Quarter

F

farouk

New Member
Does anybody know how to compute a running best quarter (3 months) and year (12 months)?


So, if I've got data that spans over 5 years on a monthly basis and I wanted to know what my best and worst consecutive 3/12 month segments were, what formula would I use?


Thanks
 
Hi Farouk ,


Can the three consecutive months be any three consecutive months or do they have to be in the proper quarters ?


What I mean is the standard quarters may be :

[pre]
Code: 
Jan - Mar
Apr - Jun
Jul - Sep
Oct - Dec
[/pre]
or any other fixed set of quarters ; so will only the three consecutive months that fall in one set be counted , or can it be any three consecutive months e.g. Feb-Apr , Sep - Nov ,... ?


The same question applies to the year also.


Narayan
 
Farouk


Firstly, Welcome to the Chandoo.org forums


You may wish to upload a sample file containing some example data and then answer Narayan's questions above

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hello Narayank991 -


It can be any 3 consecutive months. Same for the year.


Hui - I wont be able to upload a file due to the sensitive nature of the data. If it's required I can try and work around it though.


Thanks for the help guys.


Farouk
 
Farouk


We only need the structure of where your data is

Not the actual values in the data area

ie: Where the top row is

and what type of data each column has eg: Strings, Dates, Numbers etc
 
Hello Farouk,


I am not sure what output you would like to get. See attached file. The first part is used 3 Months in a cell & used SUMPRODUCT. And calculate each years total & finally used INDEX/MATCH with MAX.


The 2nd one , is used without any helper. Just enter years in columns & underneath can see 3 consecutive months.


Link;


http://sdrv.ms/YZrUvI


Hope this helps,

Haseeb
 
Hi Hasseb,


as usual.. your every post is unique and full of energy.. :)

The best part for me..

Code: 
=MMULT({100,90;80,34;23,45},{-1;1})
will gives us..

(90-100),(34-80),(45-23)


This is second best this I have learned from you.. :)

Obviously first one is Text to Date..


Regards,

Deb
 
Hi Farouk,


if you happen to have value like Hasseb file, you could pivot date & value,


then right click on of the date choose group then choose quarter and years.


after you did that you can right click on the pivot and choose "shot field list" on the bottom then drag years & quarter from Row to Columns.


this way you can easily see quarter achievement as well as compare Q1 - 2012 with Q1 - 2013 and so on
 
WOW!!


Haseeb, that's some insane work on that sheet. It's pretty close to what I need so I should be able to tweak it a bit to get the results I require. I'm not sure what some of the formulas mean but I'm working on figuring out.


Thanks again for all your help.
 
You must log in or register to reply here.
Back
Top