• 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.

YTD/ YTG formula

felixmars

New Member
Hi all,

My question is simple but I struggling since this morning to find a way to do it...
Basically in my company we use 13 period a year (not in month as companies usually do).

I'm currently working on an excel that need to be updated each month. I need to automatize the calculation of the YTG/ YTD cells. For the moment I'm just using a simple sum formula (e.g. for YTD I'm doing sum (P3:U3) and Sum(V3:AB:3) for YTG figures) however at each period I need to change the letters in the formula to have the correct data which is annoying with so many lines (full P&L). So I'm wondering if you guys have a way that by entering the period number (comprise between 1 & 13) the YTG & YTD calculations will change depending of the period number.
I hope my question is clear enough but if not don't hesitate to tell me and I'll try to explain it better.

Thanks,

felixmars
 

ETAF

New Member
you maybe able to use a indirect with a switch/choose
do you have a small sample sheet you could attach ?

Do you have a cell where you put period ? 1,2,etc
Is the range for 1 P3, 2 P3:Q3 , 3 P3:R3

maybe even a lookup table again with indirect

I did something like that for a dashboard many years ago - so we the period end date in the dashboard title and all the summary cam over based on that cell
 

felixmars

New Member
Hi Etaf,

Thanks for the reply, I just attached a sample sheet, you'll find on the 2nd tab in blue the cells I want to automatize just by changing the period number in a cell (or by any other mean but it seems the easiest way)
The range from P3:U3 = 7 first period of the year while V3:AB3 is for the 6 remaining one (YTG)
I hope with this sample you'll have a better idea of what I want
 

Attachments

ETAF

New Member
The range from P3:U3 = 7 first period of the year while V3:AB3 is for the 6 remaining one (YTG)
not sure i follow exactly

P3:U3 = 7 - means what
Thats the first period

13 period months -
i assumed you would want 1 to 13 periods
so summing up each month
and so
P3:U3 is 6 periods
P1 FY22 to P6 FY22

so that period 6
but you say thats the
first period of the year
so my assumption and what you have shown are not quite what i thought

would you mid just explaining a little more

I suspect it may be possible to use a SUMIF() or similar and use the title format P7 FY22
 

ETAF

New Member
i'm sure this can be improved , but to give you an idea

if you add a row , which you can hide , and then you mark the periods , 1,2,3 4 etc

then a SUMIF()
would work

I have added a row , under your titles and put 1 to 13 in P to AB

I then used the periods left cell , which says 7 as the number to be less than - but this can be any cell

just to give you an idea

the formula in D16,
the row 3 added 1-13
and the cell K15 as the number of periods

Just a concept
I'm sure it could be altered to use the title itself, so that FY21 , Fy22 and Fy23 could be used
maybe
202101 - 202113 for 13 periods in FY21
202201 - 202213 for 13 periods in FY22
202301 - 202313 for 13 periods in FY23

then use 2 cells with the period in from to
so
202201 to 202206

then a sumifs( range to sum, criteria range , ">="&202201 , criteria range , "<="&202206 )

would that hidden row work for you - and 2 cells to enter the period from and to
 

Attachments

Last edited:

ETAF

New Member
i have updated
=SUMIFS($C$4:$AO$4,$C$3:$AO$3,">="&E24,$C$3:$AO$3,"<="&E25)
using a Period start as described and Period end
so in
E24 = 202201
E25 = 202206

and change those to change the reporting period

will use P to U

but now you can change the period to any fy period
just with the hidden row 3

thoughts - something you can do - or not
 

Attachments

felixmars

New Member
Hi Etaf,

Sorry for the late reply didn't saw you've replied so fast to my question. I'll give a try to your formulas and keep you updated about it but I think this is a good idea as I tried many other ways to do it but it seems to be the easiest way ! thanks for the time you spent to help me, i'm quite new to the use of excel but i'll improve on it

felix
 

p45cal

Well-Known Member
For cell D15 you can have something like:
=SUM(OFFSET($P3,0,0,,$B$23))
where cell B23 contains you period number 1 to 13
All your YTG and YTD calculations can refer to this single cell B23 (it can be any cell btw), so that changing B23's value will change all formula results that refer to it..
[Purists will say that OFFSET is a volatile function; a more complex formula could be devised which uses INDEX instead, but as long as there are not too many OFFSET functions used you won't notice it. However OFFSET is dead easy to understand.]
 

felixmars

New Member
Hi p45cal thanks for the help it's very helpful !
Do you have any tips on how I can formulate the YTG calculation (cell E15) ?

Thank you
 
Top