Data analysis automation help

[danielcraw]

Hi all,

This is my first time using a form for excel help. I have a fairly good grasp on excel but have run into a brick wall with a model I am trying to automate. The model analysis one year of electricity prices. Each day of the year has 48 (half hour) periods with a price.

eg.
Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]1/1/14[/TD]
[TD]1[/TD]
[TD]58.00[/TD]
[/TR]
[TR]
[TD]1/1/14[/TD]
[TD]2[/TD]
[TD]58.50[/TD]
[/TR]
[TR]
[TD]1/1/14[/TD]
[TD]3[/TD]
[TD]59.00[/TD]
[/TR]
[TR]
[TD]1/1/14[/TD]
[TD]4[/TD]
[TD]58.25[/TD]
[/TR]
</tbody>[/TABLE]
etc.. I have this data for all of 2014

I also have demand data. The demand data is split into two sets - Non Business Days and Business Days - and is month specific.

eg.
Sheet 2
Non Business Days
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]....[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD].45[/TD]
[TD].50[/TD]
[TD]....[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD].30[/TD]
[TD].35[/TD]
[TD].50[/TD]
[TD].65[/TD]
[TD]....[/TD]
[/TR]
</tbody>[/TABLE]

Business Days
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]....[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD].50[/TD]
[TD].55[/TD]
[TD].65[/TD]
[TD].75[/TD]
[TD]....[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD].55[/TD]
[TD].80[/TD]
[TD].90[/TD]
[TD]1.00[/TD]
[TD]....[/TD]
[/TR]
</tbody>[/TABLE]
etc..

Each Day in a month has identical demand values.

My model is simple in that it multiplies the price of electricity at x period by the corresponding demand to calculate the exact cost for that period. So far I have manually identified Non Business days and Business days and manually typed the corresponding demand figure in beside the price. It has taken a long time - there must be a better way of doing this...

Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[TD]Demand[/TD]
[/TR]
[TR]
[TD]1/1/14[/TD]
[TD]1[/TD]
[TD]58.00[/TD]
[TD].25[/TD]
[/TR]
</tbody>[/TABLE]

etc.

I am looking for a way to automate the demand column in Sheet 1 so it looks for the corresponding value in Sheet 2 based the Month, Non Business day vs Business Day and the specified period.

Can anyone help me with this?

Cheers,

DC

 

[RoryA]

Where exactly (i.e. in which cells) are the demand tables? You basically want an INDEX(...,Match(), Match()) construction

 

[danielcraw]

Hi Rory

For Business Days:
A3 = Jan, B3 = Period 1 (P1), C3 = P2, D3 = P3 through to P48
A4 = Feb, B4 = P1 through to P48
A5 = Mar...
etc...
Non Business Days
A17 = Jan, B17 = P1... and the rest follows the same logic.