I need a formula that keeps a running total of the following.
Add 91.6667% of current month, subtract 8.3333 from the previous 11 months.
So, January should be 91.667% of 100
February should be the amount from January less 8.3333% PLUS 91.6667% of 90 (february numbers)
Etc Etc
Spreadsheet attached.
Will this work...
In A3 =A2*0.916667
in B3, copied across...
=(A3*0.916667)+(B2*0.916667)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
No, I think that is taking the previous month numbers for the 8.33 less%. I've add another example to show you what i'm looking for. Thanks.
In A3 the formula is:
Formula:
Please Login or Register to view this content.
In B3 according to the way that you write the instructions, you want the total from January (A2) -8.3333 % + February (B2) value of 110 *.91667 then in March you would want the total of (January + February) - 8.3333% + C2*.91667
Formula:Please Login or Register to view this content.
This will give you values in A3:L3 of
91.6667, 192.50037, 302.50074, 421.66781, 559.16825, 705.83542, 843.33595, 971.66978, 1090.83691, 1200.83734, 1301.67107, 1393.3381
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Try this in A3 & copy across.
=SUMPRODUCT(1-COLUMN($A3:A3)*8.3333%,N(OFFSET($A2,,COLUMNS($A3:A3)-COLUMN($A3:A3))))
Got your desired results.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
@newdoverman. I think your formula works, but I didn't explain it right.
Example: In January there is 120. 110 is added into the "bucket" in the first month. In all subsequent months 10 is subtracted from the bucket (until there is nothing left from January).
THEN, in February, there is 120, so again, 110 is added into the bucket and released at the same rate as January. So, the total would be 100 from January and 110 from February. And that continues....
@Haseeb A. This almost works. It works for the first 12 months, but then if I wanted to continue the dates it wouldn't work. It should only look at last 12 months, so if I added January of the next year it wouldn't work. Can you fix this? Also, could you explain what the formula is doing? Thanks.
Last edited by amartino44; 12-16-2013 at 04:23 PM.
Try this,
=SUMPRODUCT(1-COLUMN($A3:INDEX($A3:A3,MIN(12,COLUMN(A3))))*8.3333%,N(OFFSET($A2,,COLUMNS($A3:A3)-COLUMN($A3:INDEX($A3:A3,MIN(12,COLUMN(A3)))))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks