Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random start and calculation
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random start and calculation
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 "Mona" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random start and calculation
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" wrote: 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 "Mona" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random start and calculation
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....................Trunc ated Jul07..........177.15........................177.1 4 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(A6A$3,MAX(ROUND(IF(ISNUMBER(B5) ,B5-(B5*A$2/100),""),2),0),"")) For truncated values: =IF(A6=A$3,A$1,IF(A6A$3,MAX(TRUNC(IF(ISNUMBER(B5) ,B5-(B5*A$2/100),""),2),0),"")) Copy the formula in B6 down to B16. Biff "Mona" wrote in message ... 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" wrote: 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 "Mona" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Date calculation | Excel Worksheet Functions | |||
Time Calculation For A Timesheet To Include Lunch | Excel Worksheet Functions | |||
Please help with time calculation | Excel Discussion (Misc queries) | |||
Help on automatically ending a calculation | Excel Worksheet Functions |