Solved

Partially completed Graphic for two Conditions

Posted on 2013-06-09
5
162 Views
Last Modified: 2013-06-10
EE Pros,

I have a nice graphic that has two conditions.  The first condition is based on a "view" of benefits received - Conservative, Realistic, Aggressive.  The second condition has to do with the % of benefit applied during a particular year (for 3 years) and a "Total".   I've got the first condition down, but adding the second condition has complicated my graphic.  I'm trying to use Indexing instead of "if" statements for the calculations.

The business application is rather simple.  By pressing the two buttons, I should be able to view any year at any condition and at the end a total based on the condition choosen.  See example for clear explanation.

Thank you in advance.

B.
Complex-dynamic-graphic-v2.xlsm
0
Comment
Question by:Bright01
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39234247
Please explain the logic of your table. In particular, the meaning of "Benefits" (1 thru 4) isn't clear.
Perhaps, in addition to explaining your idea, please tell me where my approach is wrong:-
I think that for any given year a conservative approach should give a result different from an aggressive one. Therefore you need a table with years, say, on the X axis and Con/Real/Agg on the Y axis. If you would have such a table you could place the 20% benefit in the first year in relation to the Condition (risk-taking preference).
In short, if you wish to let your graphic display the effect of both Condition and Year it would be your table's duty to establish a relationshop between the two. This isn't the case, and therefore you have difficulty. To resolve the problem establish the relationship.
0
 

Author Comment

by:Bright01
ID: 39234301
Faustulus,

Thank you for responding and jumping in on this one.    

The logic in the table is as follows;

There are two independent tables
1.) A Benefits Table (B8:D11)
2.) A Time Table (F8:G11)

Column H is the result of combining the Condition (Table 1) with the Timing (Table 2) which is the same as selecting Year 3 since the years are added or summed as they progress.

I've got the Condition working so as you tap the Condition button, the Total in Column H changes.  However, what I don't know how to do is to add in the Timing into the calculation.  as you select the Years (1 - 3 and then total), you must add previous years.  

So to give you an example; If I selected "Realistic" as the Condition and "Year 1" as the Timing, the result for Benefit 1 would be 6 X .2 or 1.2 (that would appear in Column H8).  If I leave it on "Realistic" and select Year 2, the formula must calculate both year 1 (1.2) and add year 2 (which would be 6 X .6 = 3.6 + 1.2 (for year 1) or a result of 4.8.

In your example, normally that would work. However, the two tables are independent. While you are correct that the results do change when moving from "Condition" (Conservative, Realistic, Aggressive), the relationship with the Year or Timing is additive (Year 1 is the Condition's value * the % of results or impact in that year); and is additive depending on which Year:  Year 1, Year 2 is Year 1 & 2, Year 3 is Year 1,2 and 3, and total is actually the sum which is Year 3).

It may seem complex, but what you are solving for here is how to insert the correct logic into bringing in the yearly influence as time marches on.

Make sense?

B.
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39234518
I think I got the idea. Please take a look at the attached workbook. I made a few changes intended as suggestions. They don't influence the formula I put in column H. Perhaps I still didn't quite understand your reasoning, but to my present understanding there is a logical flaw in your logic:-
If the total for Year 2 (Conservative) = (3 * 0.2)  + (3 * 0.6)
then Year 3 must = (3 * 0.2)  + (3 * 0.6) + (3 * 1)
which would be Year 1 + Year 2 + Year 3 = Total
Therefore Total can't be greater than Year 3, and for this reason the cycle you click through by pressing the Year button should not include the Total column. Therefore the code for Sub Year_Click should be Range("A5") = Range("A5") Mod 3 + 1 instead of Mod 4 + 1.
I hope my formula can do what you want and my suggestions are useful. If you need either to be explained please do let me know.
Complex-dynamic-graphic-v2.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39235107
Perfect!  Great work.  You should be on a game show with your ability to walk through that kind of mathematical logic..........

Really appreciate it.

B.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39236538
Thank you, B.
EE will be delighted to become a game show. Well, I would be. :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now