Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function to use?
How do I spread a remainning balance of 11 mos gradually upward untill it is
gone with 1 known starting number, and an annual number. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function to use?
How do I spread a remainning balance of 11 mos gradually upward untill
it is gone with 1 known starting number, and an annual number. One way (among many possible) is illustrated in the csv file below. ------------------------------- cut here ------------------------------- Annual,10000 Jan,100 Feb,=B2+(B$1-12*B$2)/66 Mar,=B3+(B$1-12*B$2)/66 Apr,=B4+(B$1-12*B$2)/66 May,=B5+(B$1-12*B$2)/66 Jun,=B6+(B$1-12*B$2)/66 Jul,=B7+(B$1-12*B$2)/66 Aug,=B8+(B$1-12*B$2)/66 Sep,=B9+(B$1-12*B$2)/66 Oct,=B10+(B$1-12*B$2)/66 Nov,=B11+(B$1-12*B$2)/66 Dec,=B12+(B$1-12*B$2)/66 Total,=SUM(B2:B13) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function to use?
It works great. Can you explain why mul 12, subs the princ., then div. By
66. Is this higher math ?? Is it possible to explain, I am intrigue. So I guess it can not be done with a function. "Jay" wrote: How do I spread a remainning balance of 11 mos gradually upward untill it is gone with 1 known starting number, and an annual number. One way (among many possible) is illustrated in the csv file below. ------------------------------- cut here ------------------------------- Annual,10000 Jan,100 Feb,=B2+(B$1-12*B$2)/66 Mar,=B3+(B$1-12*B$2)/66 Apr,=B4+(B$1-12*B$2)/66 May,=B5+(B$1-12*B$2)/66 Jun,=B6+(B$1-12*B$2)/66 Jul,=B7+(B$1-12*B$2)/66 Aug,=B8+(B$1-12*B$2)/66 Sep,=B9+(B$1-12*B$2)/66 Oct,=B10+(B$1-12*B$2)/66 Nov,=B11+(B$1-12*B$2)/66 Dec,=B12+(B$1-12*B$2)/66 Total,=SUM(B2:B13) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function to use?
"Thuy" wrote...
It works great. Can you explain why mul 12, subs the princ., then div. By 66. Is this higher math ?? Is it possible to explain, I am intrigue. So I guess it can not be done with a function. "Jay" wrote: .... Annual,10000 Jan,100 Feb,=B2+(B$1-12*B$2)/66 .... Dec,=B12+(B$1-12*B$2)/66 Total,=SUM(B2:B13) The amounts in months 2 through 12 are higher than the amount in month 1, so the amounts for all 12 months include at least the month 1 amount. So the remainder to be spread over months 2 through 12 is the original amount less 12 times the month 1 amount, B1-12*B2. As for the 66, the month 2 amount is the month 1 amount plus the increment (B$1-12*B$2)/66. The month 3 amount is the month 2 amount plus the same increment, which equals the month 1 amount plus 2 times the same increment. The month 4 amount is the month 3 amount plus the same increment, so the month 1 amount plus 3 times the same increment. So the month M amount is always the month 1 amount plus (M-1) times the same increment, or Annual,10000 Jan,100,Increment,=(B1-12*B2)/66 Feb,=B$2+1*D$2 Mar,=B$2+2*D$2 Apr,=B$2+3*D$2 .... Nov,=B$2+10*D$2 Dec,=B$2+11*D$2 Total,=SUM(B2:B13) SUM(B2:B13) == (B2) + (B2 + 1*D2) + (B2 + 2*D2) +...+ (B2 + 11*D2) == B2 + B2 + B2 +...+ B2 + (0 + 1 + 2 +...+ 11)*D2 == 12*B2 + 66*D2 == 12*B2 + 66*(B1 - 12*B2)/66 == 12*B2 + B1 - 12*B2 == B1 The 66 is just the sum of 1 to 11. As for doing this with a function, use SYD for months 2 through 12. B3: =SYD(B$1-12*B$2,0,11,ROWS(B3:B$13))+B$2 Fill B3 down into B4:B13. While it could be done using functions, this is one instance in which simpler formulas, like Jay's, would be better. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function to use?
It works great. Can you explain why mul 12, subs the princ., then
div. By 66. Is this higher math ?? High-school math, tops. Start with a problem statement: Total = January amount + January amount + increment + January amount + 2 * increment + .... + January amount + 11 * increment and solve for "increment". This approach assumes that you want the monthly increases to be linear. Other approaches are possible if the monthly increases are supposed to follow a different rule. So I guess it can not be done with a function. There are lots of approaches. I'm sure some would use a function. If there's some reason why a function is required, please explain. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |