spreading values across a time frame

[REDeveloper]

I was wondering how I would take two columns and spread a certain value across multiple rows (or one row depending on the difference in years.

For example start year 2010, end year 2010-this would be 100 percent allotted to one year

start year 2011 and end year 2012- this would be 50 percent allotted to two years.

etc.

[DonkeyOte]

Welcome to the Board.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

[REDeveloper]

I think that should work for the attachment.


excel q's.pdf

[DonkeyOte]

Better to post a workbook rather than a pdf... people are generally disinclined to spend their time mocking up a file to test on that you could provide in the first instance.

[REDeveloper]

I know how to do the if statement in general, the only part I am having trouble is getting it to produce a value based on if it is between the beginning and end years

=IF($E8>J$6>$C8,(1/(($E8+1)-$C8))*$G8,0)

The first part of the if statement does not work and that is what I have trouble with

Book2.xlsx

[DonkeyOte]

based on your file

J8: =AND(J$6>=$C8,J$6<=$E8)*($G8/(1+($E8-$C8)))
applied across matrix

... it would however make sense to store the annual amount say in F or H and use that value rather than continuously recalculating, eg:

H8: =$G8/(1+($E8-$C8))
copied down

then

J8: =AND(J$6>=$C8,J$6<=$E8)*$H8
applied across matrix

[REDeveloper]

thanks for putting up with a first time user. I appreciate it. I have a renewed appreciation for excel