pls help

[shirley]

hi,

i have a small problem....here's what it looks like:

A(flag) B(date of sale) C(sale amt) D(Feb06) E(Mar06) F(Apr06).....
1 0 25000

ok heres where the problem is.....when there is a sale, the flag in col A
changes from 0 to 1. then the month of sale is entered in col B..... now the
first installment of the payment has to start in the month of the sale....for
eg. if the first sale is for 25000$ in the month of Mar06, then 50% of $25000
shud appear in Mar06 column and 25% of $25000 in the col 5 months from
Mar06(i.e. in Aug06) and the last 25% in the col 10 months from Mar06.

kindly help me if my problem is clear!

 

[Roger Govier]

Hi Shirley

Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006
formatted to just show Month and year with
Format>Cells>Number>Custom>mmmyy
Also, assuming you use a proper Excel date in column B, then enter the
following formula into cell D2 and copy across as far as required.
Copy the whole row down as far as required.
=IF($A3="","",IF(D$1<$B3,"",
IF(DATEDIF($B3,D$1,"m")=0,$C3*50%,
IF(DATEDIF($B3,D$1,"m")=5,$C3*25%,
IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,"")))))

Until you enter a value greater than 0 in your flag field, the values
will remain as 0
If you need more help on the undocumented Datedif function, take a look
at Chip Pearson's site
http://www.cpearson.com/excel/datedif.htm

 

[shirley]

Hi Roger

YAY! it worked!! Thanks for all the help. You are a life saver!!!

Shirley