Charts Frustration

[Mdueck]

Hi everyone,

I feel like i'm trying to achieve the simplest things but banging my head against the wall, I'm almost too embarrassed to ask!

I'm looking to create a chart like this with the policy values up the size (i.e. 100,000 etc) the bars to plot the age of expiry of each policy.

I'm looking to have the axis start at the age of the client (it's all on the same client so the age will always be the same) and finish at 100. 

I tried achieving this by putting in a dummy value of 100 but excel insists on plotting the bars against the policy values unless i delete the other series.

The idea is that this is responsive, summarising life insurance policies the client has vs the term it expires. I was planning to have a separate table with 'life events' behind it as vertical lines, plotting live events vs the age the policy expires and would like it to be responsive.

I tried a different approach as below, but this wasn't right either

In the above chart I used the ROW function to plot the age column starting from the clients age to 100 then used the same function to insert the policy amount in every cell until the Policy expiry age matches the clients age in the column (if that makes sense)

In summary, I want a chart that lists the policies on the Y axis and plots a bar to the clients policy expiry age. The X axis needs to ideally start from the clients age to 100.

Any thoughts would be massively appreciated

Thanks

[#2]

@Mdueck Not sure I fully understand but perhaps something like in the attached file?

Sorry for the blurred picture. Can't get it right for some reason. 

[#3]

@Riny_van_Eekelen

HI, thanks for your response, sorry I'm not clear it's gonna be my terminology and my brain also breaking...

The lines for the 40th and Retirement are perfect!

Basically, the amount of the policy e.g. Isn't actually that important and doesn't need plotting on the graph, ideally I would like the name of the policies (policy 1....) to be the Policy Amount, but I can figure that out with a text box or something.

My main issue is the age axis at the bottom

I'm trying to demonstrate when someone's like policy expires compared to their retirement age and other life events. 

So the age series is perfect but I want the bottom axis to start at the age of the client. The reason for this is that no one would have an insurance policy from the day they were born.

So I either want the axis to start from their current age or maybe it's easier to plot with a policy start age and policy end age... 

OR I just have the data plotted with a line or a dot where each policy ends then the axis does matter (I could just have a fixed range from say 18 - 90)

I'm sorry if I'm not making much sense. Im thinking my 2nd option without bars might be easier so I don't need a dynamic axis

[#4]

@Mdueck 

Don't understand. Why have amounts in the table if you don't want to plot them? What do you mean by "wanting the policy name to be the policy amount"? 

Dynamically setting the start of the age axis at the the client's age would require VBA (I think). Not something I do, I'm afraid.

Perhaps you can make a sketch of the kind of chart you want, because I find it hard to visualize otherwise.

[#5]

@Riny_van_Eekelen 

Yeah I think i'm giving up on the dynamic bit, I'm just setting the age from 18 - 90 or something like that.

I'm looking for it to look like this...

So i've basically set my table out like this, which will feel the chart. The Dep 1,2,3 are childrens ages, these and the retirement will be plotted in the same way you did with the trend lines (which i loved by the way):

So this table will be fed data from this table:

(The policy End Ages for Applicant 1(thomas) are in the highlighted cell column)
To avoid gaps in the chart, I want to say IF OWNER = AP1Name or "JOINT" then put the Policy End Age for that row in the graph chart.

I'm basically create 1 chart for applicant 1 that shows sole and joint policies and one for applicant 2 eventually.

I really appreciate you help, this has been very helpful

Marc - Financial Adviser.... not an excel expert

[#6 · Accepted solution]

@Mdueck Slightly adapted the chart. Does that help?

Riny - Excel Expert... not a Financial Advisor