03-12-2019 09:11 AM
I have a file in which I'm going to be constantly expanding the vertical length of a 36 month span of months. Specifically, I'll have various sections (all very similar, but not identical, in structure). They will have certain cost lines (which will vary), and then they'll total to a "Subtotal Cost" line, per section. I need a 'Total Cost' of all the 'Subtotal Cost' lines. (To be clear, the way the file is structured, I cannot use the subtotal function to avoid double counting)
What I'd like to do is name a range as being all subtotal lines (so exactly the same 36 columns of width, but rows in various places in the file. My thought was then to make something like an index of offset formula within a Sum function, but I can't figure out how to effectively say "all the X columns of each row. Here's what I have:
=SUM(INDEX(_testrange2,1,C$15))
where "_testrange2" is all the rows, of same width, in a single defined range. C$15 happens to be the row in which I'm placing the number of the month, with the logic being as I copy this formula across, it would grab the appropriate month column.
Whether I enter, ctrl-enter, or ctrl-shft-enter the above, get the same result.
How do I get it to understand I want all rows, X column?
The reason I need this is that as I add sections, I'd simply adjust the named range with the additional subtotal line of the new section, thus updating all instances where I need to get the total.
Thanks for any/all suggestions.
03-12-2019 09:40 AM
03-12-2019 01:17 PM
It's an option. It's just not the way this file works, for various reasons that would take too long to describe. I've just got to believe there's a way to effective take an array and (formulaically, not VBA) use the rows as I would any set of rows in Excel, whereby I can take the Xth column, and sum all amounts in that column.
Thanks for the idea of separating into pivots. Again, just not in the cards for this file, for a host of reasons.
03-12-2019 02:44 PM
03-12-2019 02:53 PM
A11 should read 14 (because right now the named range only includes the 5th and 7th row. Once I update the range to also include the 9th row, I'll want the row 11 result to automatically show 27 (the addition of 13 from A9)
hope that clarifies.
03-12-2019 06:00 PM - edited 03-12-2019 06:00 PM
Thought I'd give a sense of what I'm dealing with, as I'd love thoughts on best way to implement pivots for this. The attached is a fraction of an extract, which shows the basic dynamics:
- there is a section of assumptions (this is one line of assumption...there are about 20-30 per section, and currently there are 20 sections)
- there is an actual time of months (this shows 12, but it's a 3 year model, so there are 36 columns of months
- this is a set of assumptions that occur on an absolute time basis (the assumptions say between months 1-5 there are 50 members acquired, and then from month 6 onward there will be 100 per month)
- there are certain assumptions (not shown here, to keep it less complicated), which are 'as of joining' dynamics...so it'll say from month 1-3 there will be no fee charged, and then from months 4-6 a 50% fee will be charged, and then followed by full fee). This means that for a customer joining in month 3, their first month (the "month 1" as of joining) is in actuality month 3 of real time...so the 'as of joining' assumption would be applied starting in actual month 3, for 3 months (up to month 6), then the as of joining assumption between month 4-6 applied to actual month 7-9...
and the above dynamics would be recreated for every month's group of customers, for 36 months...and there will be between 20-40 versions of all that.
Hence I put it horizontally for time, sum it up by section vertically, and need to be able to expand/contract the spots vertically that I'm capturing.
Currently the file is 14K lines long, and that's just for the revenue assumptions of the various scenarios we're envisioning (this is a broad platform we're creating).
That said, I'd LOVE to do this with pivots. The only way I could think of doing it would be to flip the entirety of my calculations into a vertical stream, where each line represents one month's worth of one customer's worth of data.
If you have ideas of how to efficiently use pivots to calculate the elements once, and then be able to flip them around for various reporting needs, would welcome your suggestions.
Thanks.
03-12-2019 09:57 PM
With your definition of _testrange2 only this will work:
=INDEX(_testrange2,0,1,1)+INDEX(_testrange2,0,1,2)
Which is the same as:
=SUM(A5;A7)
You definitely should reconsider your setup as Jan Karel already suggested.
03-12-2019 10:29 PM
In the attached file, the formula in A11 is:
=SUM(INDEX(A:A,G1):INDEX(A:A,G2))
G1 and G2 are the cell references for the start and end of the row to sum, respectively.
03-13-2019 07:23 AM
@Detlef Lewinand @Jan Karel Pieterse , Would love to have your thoughts on how to use pivots. Here is a small extract of the way the file works.
Basically, there is a section of assumptions (to be more precise, a section for each of 30-50 'types' of customers, and each section has about 10 parameters that dictate the behavior of revenue for that customer). Then there are a number of cost sections that do similar calculations. There are two concepts (one of which is shown in attached):
- there is 'absolute time' (the dynamics that happen to a group of customers depending on when they join in the lifecycle of the company. So in this example, months 1-5 would have 50 of revenue, and months 6 onward (36-month modeling window) would have 100.
- then there is the concept of relative time (the dynamics that a customer group experiences, depending on when they join, relative to their month of joining). To relate this to what everyone may recognize, this is the sort of "sign up and get one month free" type of dynamic. If you sign up in the company's month 6 of operation, then that's 'relatively' your month 1 with the company.
Each section has a subtotaling of the units (people/customers) that are derived using the assumptions, and the revenues (units multiplied by price and chargeability factor). Those revenues are what would make up this named range I want. The ability to expand that revenue total by including additional lines, from newly modeled sections, is how this question came about.
That's the brief description, to try to explain what the file shows. Again, this is two assumptions of one section. There will be up to 50 types of these sections, and there are about 20 assumptions. Obviously can't really debate the need for the assumptions. It is what it is, as part of a business model that's being modeled. I love pivots, would love to use them, so if any thoughts on how to do this efficiently, happy to see it. Right now, with what I've shown here, and the limited number of sections that are modeled out, the file is 14K lines long. If I were to flip it vertically (one thought I had, to enable pivoting, including using a 'Period' sort of column to delineate each month, each data element, etc., the file would turn into 36 times that length...again, that's having modeled out only about 4 of these sections.
Happy to hear thoughts. Thanks.
03-13-2019 10:57 AM
Sorry, I don't have the time to dive deeply in that very special model.