Link to home
Create AccountLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Applying Calculations to a Dynamic Array

EE Professionals,

I have a great Dynamic Array Model that EEPs have helped me create.  I'm now taking it to the next step and need to see how I can apply formulas against the Array in Tab 2.  Attached is one mock up example (on Tab 2) on creating an Average Change based on dynamically added or removed columns.  I think if I understand how you provide that formula, I can add the others myself.

Thank you in advance.

B.
Copy-of-Financial-Model-v6.xlsm
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Insert two columns at I and J
I3:      For Oldest year
J3:      For Current year
I4       =OFFSET($B4,0,1)
J4:      =OFFSET($B4,0,MATCH(Financials!$E$6,Financial_Calc!$3:$3,0)-2)
K4:     =(J4-I4)/I4

Copy row 4 down
For below the yellow area you will have to enter the formulas and make the existing macro copy the formulas with the next and previous years.
Avatar of Bright01

ASKER

Ssaqibh,

Thanks for jumping in.  The formula works partially.  It has to know how many columns have been added since right now the formula gives me the growth change from oldest to newest but doesn't reflect the average per year growth which would be if it counted the number of columns that had been added or taken away.

Make sense?  

Not sure how to deal with the formulas "below".

B.
Can you give an example of average growth for four years?
Certainly.  The formula or number would change depending if I add or remove Columns in Tab 1.
Formula-Example.xlsm
Are you sure that formula is correct? The 4 is dividing only the last term.
And there are 3 terms added... so probably the divisor should be 3
Yes....you are correct!  Good catch.
But, you get what I'm trying to do right?  I'm trying to create the ability to add as many historical years as you like and then do calculations off of an array that can dynamically change based on different scenarios.

B.
This ARRAY formula will calculate the average growth as suggested

=AVERAGE((OFFSET(B4,0,2,1,Financials!$E$6-MIN(C3))-OFFSET(B4,0,1,1,Financials!$E$6-MIN(C3)))/OFFSET(B4,0,1,1,Financials!$E$6-MIN(C3)))
ssaqibh,

I worked with the formula for 30 min. and couldn't get it to work.

Where do I put it in?  I've attached the latest trial so you could see where I put it in.

Also, this doesn't get to the heart of what I'm trying to accomplish.  The formula is a simple example.  I'm trying to find a way to summarize whatever is produced by the dynamic array. If the array is 2 columns then there are 2 data elements, added together then divided by 2 to get an average.  If a 3rd or 4th column is added, then there are 3 or 4 data elements, added together and divided by the count (3 or 4).  When reset, there is only one data element so that data element is the product.  

Make sense?  It's not the formula, it's how the data is handled dynamically.  That's the difference.

Thank you in advance.....this is a hard one.

B.
Copy-of-Financial-Model-v7.xlsm
Although a bit complex, this is how I would take care of the progression of the years
Financial-Model-v7.xlsm
Ssaqibh,

I'm going to accept this answer after you and I chat about this for a sec.  You've given some good thought to this and I may be bordering on asking for something that is far more complex then easily doable.  

What I'm trying to accomplish is to have a array that expands and contracts and then do simple math on the array as it changes.  The problem I have is that any static formula needs to have a set number of cells that a formula has to operate on.  I thought I might be able to get by with using range names that as the array changed, the range and perhaps the range value would automatically change.  Or perhaps use some definition that would detect how many rows/columns were in the array and then adjust the formula.  It may be too complex to do easily and eligantly.  I'm going to have to think of a different approach to making this work.

Any thoughts you might have would be great.  And thank you for a lot of good, hard thought on the formula you created.

B.
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ssaqibh,

I think I may have had a breakthru on this last night while thinking about it.  I've attached the updated sheet.  Here's what I'm thinking.

In order to keep it simple and create the array that in the Calc WS I can do math against the array, I think I could use a formula (in the yellow cells).  The formula looks at the cells in the Financial Sheet and if a new column has been added, it will then copy the value from the appropriate cell; if not, it won't.  When I put my simple formula in, it worked until I reset it an then I got a #Ref error that I don't know how to fix.  In other words, the Array on the Calc. WS doesn't have to be dynamic, it just has to identify when to include information from the other WS.  I think this would make it much easier to use and simply the model.

Your thoughts?

B.
Copy-of-Financial-Model-v71.xlsm
You are probably going to enter into other problems. You might even succeed. I suggest that you open a new question for the new layout and keep this question open so that you can have both options open till you are satisfied with the design. Both layouts will have entirely different approaches and should not be mixed.
Great job!  I learned from your approach and am posting another question.  

Thank you,

B.