Using Excel to auto spread data

[Guest]

Hi,

Is it possible to put in a constant in column A, and Excel can equally
spread this constant based on the start & finish dates in column B and C to
column D and beyond?

How about have the constant spread into a S-curve or different curves? For
example, front end loaded early months or load up the later months?

Thanks.

 

[Pete_UK]

You would need a macro to do this - a formula can only return a result
to the cell it is in.

Hope this helps.

Pete

 

[Guest]

For a constant spread based on the number of days within each month that are
also included in your date range you could do something like this:

A2 = amount to spread
B2 start date
C2 end date

D1:Z1 dates, 1st of successive months (format as ddd yy, if you wish)

in D2 use this formula copied across

=MAX(0,MIN($C2+1,E$1)-MAX($B2,D$1))*$A2/($C2-$B2+1)

format as number or currency