Hello, I am trying to calculate a running month-end balance of deferred revenue using a formula.
The bottom of the attached shows the cascade/waterfall chart which calculates deferred balance by month, but that takes up too much space.
Wondering if someone can help automate this into one formula? I think I am close to it in the top section, but need help refining. Thanks!
I'll work on this when I get home from work as its going to take me >1 hour. You want none of the values used in the formulas on the right used, correct?
C4=sumproduct(offset(b4,0,0,-min(11,rows(c$4:c4)))*(11-row(c4)+row(offset(b4,0,0,-min(11,rows(c$4:c4)))))) or
c4=sumproduct((index(b$4:b4,max(1,rows(c$4:c4)-10)):b4)*(11-row(c4)+row(index(b$4:b4,max(1,rows(c$4:c4)-10)):b4)))
try the above and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
In c4
=SUMPRODUCT(B$4:B4,N(OFFSET($D$4:D4,ROWS($D$4:D4)-ROW($D$4:D4)+CELL("row",$D$4:D4)-1,0)))
Copy down
This only uses Col D as reference
Life's a spreadsheet, Excel!
Say thanks, Click *
=SUMPRODUCT((B$4:B4)*(11-ROW(C4)+ROW(C$4:C4))*(11-ROW(C4)+ROW(C$4:C4)>0))
can use above formula too
Awesome, thank you very much!!
Thanks for helping out, but marking as "solved" as Siva's suggestion works!
Thanks for your feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks