Dividing Annual Costs into Semi-Annual costs

[EvansB2]

Hi,

In worksheet 'X' I have in column A annual periods (Jan '12 to Dec '12) and column B (Jan '13 to Dec '13) and so forth for a period of 25 years with associated costs in each of the columns.

In another worksheet 'Y', I have in column A semi-annual period (Jan '12 to June '12 and column B (July '12 to Dec '12) and so fother as above.

I simply need to develop a formula that takes the annual costs in worksheet 'X' and drops it into the semi-annual cells in worksheet 'Y' - so in effect dividing the annual cost by 2 each time and then copying this along all colums (over 25 year periods).

Any help would be appreciated.

Regards

Ben

 

[Dannyh1]

Prob not the best solution, but could try:

A2 =HLOOKUP("*"&LEFT(A1,FIND("to",A1)-1)&"*",Sheet1!$A$1:$Z$100,2,FALSE)/2
B2 = A2

Highlight cells A2 & B2 & drag formula across.

 

[EvansB2]

Thanks.

I haven't got there yet, however I think I alter the way the dates are put in it might make it simplier, i.e. dates will be "period ending" - Dec '12, Dec '13, etc. And semi-annual periods; June '12, Dec 12, June '13, Dec '13, etc.

Is this what your solution is based on?

Ben

 

[Dannyh1]

Hi, It was but not very good solution.

try:

A2 =INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()-(COLUMN()-1)/2))/2
B2 =INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()-(COLUMN())/2))/2

Highlight A2 & B2 & copy across.

 

[EvansB2]

Thanks again.

I've got it working now with the above solution. What if the data isn't located exactly in the same place on both sheets? I haven't used the INDIRECT formula before so not sure how I would adapt it to take account of this.

Regards

Ben

 

[Dannyh1]

Hi,

Yes this could be adapted. How is the data set out?

good explanation of INDIRECT here: http://www.cpearson.com/excel/indirect.htm

 

[EvansB2]

It is likely when I have finished organising things that in this sheet (sheet 1) the date starts in cell J5 and associated costs J24. In the other sheet date starts in cell E76 and associated costs in E76.

Ben

 

[Dannyh1]

In this scenario, it would be:

E76 =INDIRECT("Sheet1!"&ADDRESS(ROW()-52,(COLUMN()+7)-(COLUMN()-1)/2))/2
F76 =INDIRECT("Sheet1!"&ADDRESS(ROW()-52,(COLUMN()+7)-(COLUMN())/2))/2

 

[EvansB2]

Cool. Works a treat.

Thanks for your help.

Ben

 

[Dannyh1]