Formula

[jrl1208]

Hi
Does anyone know if there is a formula to pick up the amount and spread it base on the order date. Like the example below:

Order Date Amount Jan14 Feb14 Mar14 Apr14 May14
Feb14 50,000 50,0000
May14 12,000 12,000
Mar14 20,000 20,000

Any help will be appreciated. Thanks

 

[r1c1]

What is the duration to spread this amount. Is it the months prior or after the order date?

If it is prior,

you can use a formula like this.

Assuming your data starts is in A2 (row 2 has headers, row 3 has values), like this:

=IF(C$2<=$A3, $B3/((YEAR($A3)-YEAR($C$2))*12 + MONTH($A3)-MONTH($C$2)+1),0)

See attachment for solution for both cases - before or after distribution.

 

[jrl1208]

Hi r1c1

Is it possible to allocate the whole amount into that month.
Eg Order date is Feb14 for $50,000, enter $50,000 in the month of Feb14

 

[NARAYANK991]

Hi ,

Use a simple IF statement , such as the following in C3 :

=IF($A3<>C$2,0,$B3)

Copy this across and down.

Narayan

 

[jrl1208]

Thanks Narayan