# Dividing Annual Costs into Semi-Annual costs

#### EvansB2

##### Board Regular
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.

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

Hi, It was but not very good solution.

try:

Highlight A2 & B2 & copy across.

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

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

In this scenario, it would be:

Cool. Works a treat.

Thanks for your help.

Ben

Replies
1
Views
342
Replies
8
Views
437
Replies
0
Views
141
Replies
7
Views
493
Replies
5
Views
295

1,203,078
Messages
6,053,404
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back