random start and calculation

[Guest]

I have 3 input cells (data will change based on user input) :
A1 300 (value) "start rate"
A2 10 (percent) "decline"
A2 feb-07 (date as month and year) "start date"

I need to populate cells based on the "start date", "decline", "start rate".

Example output:

jan07 0
feb07 300
mar07 290
arp07 280
may07 270
jun07 260
jul07 250
aug07 240
sep07 230
oct07 220
nov07 210
dec07 200

Another example is the start date could change to may-07 and the output
would start the cacluation on May07 through dec07. So may07 will be 300,
jun07 is 290, jul07 is 280, aug07 is 270, ....dec07 is 230.

I am OK with the math but I am having problems getting the right
functions/code for the startdate and the calculations after that.

thanks!

 

[Biff]

Hi!

Maybe something like this:

A1 = 300
A2 = 10
A3 = 2/1/2007 (formatted to appear as Feb 07)

A5 = 1/1/2007 (formatted to appear as mmm yy)
A6 = 2/1/2007
A7 = 3/1/2007
...
...
A17 = 12/1/2007

Enter this formula in B5:

=IF(A5>=A$3,MAX(A$1-(A$2*COUNT(B$4:B4)),0),"")

Cell B4 should be empty or it can be a TEXT header.

Biff

 

[Guest]

thank you Biff. I made a mistake on my example. I need the deline to be on
the previous value NOT the ORIGINAL value (in example it is 300)

Jan07 0
Feb07 300
Mar07 270
Apr07 243
May07 218.7
Jun07 196.83
Jul07 177.14
dec07 104.61

sorry for the confusion! I am still having problems with pointing to the
correct cell for calculations. Thanks!

 

[Biff]

Hi!

Ok, that changes things!

First, how are you calculating to arrive at your results? Are you rounding
or truncating the values?

If I round to 2 decimal places I get different results and if I truncate I
get different results:

Jul07 177.14
dec07 104.61

..................Rounded....................Truncated
Jul07..........177.15........................177.14
Dec07........104.62........................104.58

See this screencap for the differences:

http://img107.imageshack.us/img107/5178/sample3aa.jpg

In any event, here are the formulas for both, rounded or truncated:

Enter this formula in B5:

=IF(A5>=A$3,A$1,"")

Enter one of these in B6:

For rounded values:

=IF(A6=A$3,A$1,IF(A6>A$3,MAX(ROUND(IF(ISNUMBER(B5),B5-(B5*A$2/100),""),2),0),""))

For truncated values:

=IF(A6=A$3,A$1,IF(A6>A$3,MAX(TRUNC(IF(ISNUMBER(B5),B5-(B5*A$2/100),""),2),0),""))

Copy the formula in B6 down to B16.

Biff