Looking for a way to convert monthly prices into quarterly and seasonal prices without having to use manual formulas

[lemondifficult]

Hi,

I work in the energy industry and my role involves tracking wholesale energy prices. Every day, I obtain the previous working day's closing prices for wholesale gas and electricity prices that are broken down on a monthly delivery basis (as they would be for oil prices), of which I have provided a screenshot below

However the market doesn't really trade individual months after a certain point ahead in time, with energy trading in quarterly or seasonal blocks as per below

Q1 2017 = Jan 17 to Mar 17
Q2 2017 = Apr 17 to Jun 17 and so on.

Sum17 = Apr 17 to Sep 17
Win17 = Oct 17 to Mar 18 and so on.

At present, on the same sheet I have lots of additional columns to cover all the available quarters and seasons, with simple AVERAGE formulas to cover the relevant 3 or 6 months for that period that I manually need to add when I include new periods. Ideally I would like to leave these "INPUT - " sheets to just store the monthly prices, and then in a separate sheet have the quarters and seasons pre-defined across the top row, but automatically populate the cells in the column with the average of the monthly prices for that period.

I would need the cells to be left blank if there is no data on that date for even just one of the months included in the period that needs to averaged (i.e for Q1 2017, if there is no data for Jan 17, Feb 17 or Mar 17 then the cell should remain blank).

Is anyone able to provide some assistance with this? I have uploaded a workbook with the data in its monthly format on one sheet, and a second sheet laying out how the data needs to be output at this link - http://s000.tinyupload.com/?file_id=06114884213139691317

I would also need it to be fairly easy to add additional Quarter and Seasons to the output sheet and still have the data populate automatically.

I realise some people may say this type of thing would best be done in Access, however that isnt going to be an option in the short-medium term, so at the minute i need to try and find an Excel fix!

Any help people can provide would be much appreciated. Thanks in advance.

Micheal

 

[oldbrewer]

understanding the question is always the hardest bit...

why do you detail prices for jan feb mar 17 when you say there is only a single price for that quarter ?

 

[lemondifficult]

hi oldbrewer,

the closer it gets to the point of delivery, the more liquidity in the monthly markets there is. so suppliers are happy to trade the "front months", which would generally be the next 6 months ahead and then past that point it would be quarters and seasons.

as to why the prices are still provided in a monthly format - im not able to answer that question im afraid!

 

[Krayons]

Don't ask me how I did this, its complicated as ****, but it works.

You must first add a column into "INPUT - Elec" so that it starts at "Jan 05", not "Feb 05".

Once you do that, copy the following formula into cell B2 of your output sheet. From there, copy formula to the right, then copy down.

=IF(ISERROR(AVERAGE(INDIRECT("'INPUT - Elec'!" & SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q1","Jan "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q2","Apr "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q3","Jul "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q4","Oct "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","") & ROW() & ":" & LEFT(ADDRESS(1,COLUMN(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q1","Jan "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q2","Apr "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q3","Jul "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q4","Oct "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","")&"1"))+2,4),LEN(ADDRESS(1,COLUMN(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q1","Jan "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q2","Apr "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q3","Jul "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q4","Oct "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","")&"1"))+2,4))-1) & ROW()))),"",AVERAGE(INDIRECT("'INPUT - Elec'!" & SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q1","Jan "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q2","Apr "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q3","Jul "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q4","Oct "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","") & ROW() & ":" & LEFT(ADDRESS(1,COLUMN(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q1","Jan "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q2","Apr "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q3","Jul "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q4","Oct "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","")&"1"))+2,4),LEN(ADDRESS(1,COLUMN(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q1","Jan "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q2","Apr "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q3","Jul "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),IF(LEFT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2)="Q4","Oct "&RIGHT(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$1"),2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","")&"1"))+2,4))-1) & ROW())))

Download workbook here:
TinyUpload.com - best file hosting solution, with no limits, totaly free

EDIT:

Backup your work first. I take absolutely no responsibility if **** hits the fan. Also, this is extremely calculation intensive. You've been warned lol.

 

[oldbrewer]

	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18
05/10/2016	58	57.5	57	56.5	56	55.5	55	54.5	54	53.5	53	52.5	52	51.5	51	50.5	50
06/10/2016	58.3	57.8	57.3	56.8	56.3	55.8	55.3	54.8	54.3	53.8	53.3	52.8	52.3	51.8	51.3	50.8	50.3
			problem statement determine quarterly averages from jan-17 for each day
05/10/2016					56.5			55			53.5			52			50.5
06/10/2016					56.8			55.3			53.8			52.3			50.8
			it appears to be as simple as this but I bet it isn't…..
			easy to add odd summer and winter month blocks

<colgroup><col span="3"><col span="15"></colgroup><tbody>
</tbody>

 

[Krayons]

I've significantly improved the performance of the formula I posted previously.

New formula:

=AVERAGE(INDIRECT("'INPUT - Elec'!" & SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","") & ROW() & ":" & SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error")))),'INPUT - Elec'!$1:$1,0)))+2,4),"1","") & ROW()))

The logic:

first, we must identify what the header in the INPUT sheet should be, based on the header in the OUTPUT sheet

=IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error"))))

okay, now that we have identified what the starting header should be for each quarter, we must now search the INPUT sheet to find the column with that matching header, and return the column number so we can build our AVERAGE() formula. we can use INDEX() and MATCH() to make this happen.

=COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error")))),'INPUT - Elec'!$1:$1,0)))

but wait, AVERAGE() will only accept columns in letter format, so lets use SUBSTITUE() again to turn the column number, into a letter.

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error")))),'INPUT - Elec'!$1:$1,0))),4),"1","")

now that we have identified what the beginning column letter should be, we need to find what the ending column letter would be for that quarter. since we know a quarter is always three months, lets just increase the starting column, by two. So lets use the same formula we used to get the starting column number.

=COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error")))),'INPUT - Elec'!$1:$1,0)))+2

same deal, we have the column number, but AVERAGE() only accepts letters, so lets convert using SUBSTITUTE()

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('INPUT - Elec'!$1:$1,0,MATCH(IF(LEFT(B$1,2)="Q1","Jan "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q2","Apr "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q3","Jul "&RIGHT(B$1,2),IF(LEFT(B$1,2)="Q4","Oct "&RIGHT(B$1,2),"error")))),'INPUT - Elec'!$1:$1,0)))+2,4),"1","")

great, so now we've found our starting and ending columns, for each quarter. let's throw it all together using INDIRECT() to build our AVERAGE() formula.

the average formula will look like:

=AVERAGE(INDIRECT("'INPUT - Elec'!" & start_col & ROW() & ":" & end_col & ROW()))

Just replace start_col with the formula we built for finding the starting column, and end_col with the one for finding the ending column.

Then, to clean it up, we can use ISERROR() within an IF statement to remove anything that errors out (because data doesnt exist on the input sheet)

=IF(ISERROR(formula_here),"",formula_here)

Here is the working example with the improved formula:
TinyUpload.com - best file hosting solution, with no limits, totaly free

I will let the community take over from here. There is still a performance bottleneck when using ISERROR() to clean up the sheet since we're duplicating the formula in every cell. Perhaps someone else can improve this further.

-B

 

[Krayons]

And here's the workbook that shows you the output of each logic step:

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[lemondifficult]

hi both,

many thanks for responding to my query.

oldbrewer - sorry, but im not sure what it is you are suggesting?

Krayons- looks like you really put some work into this! it looks promising, however I'm on a half-day in work today so havent had a chance to fully look at it. i will have a proper look next week, but looks like this may work. i will get back to you if i have any queries.

thanks again both. really do appreciate the responses.

have a great weekend!