Problem showing visual the right way (clustered column chart)

[nor303]

Hi

I have this data set that im trying to show as clustered column chart, its a sensitivity analysis. However the problem is that I dont get the data correct in my visual (all scenarios shows the loan value in different intervalls so the total value is equal in all scenarios). See the visual on how I want it to be:

and here is my data:

KundeLånEiendomsverdi LTV LTV Intervall LTV -10%  LTV -15%  LTV -25%  LTV -10%2  LTV -15%3  LTV -25%4 

1	52	75	0,69	60%-75%	0,77	0,82	0,92	75%-100%	75%-100%	75%-100%
2	10	25	0,40	40%-60%	0,44	0,47	0,53	40%-60%	40%-60%	40%-60%
3	50	60	0,83	75%-100%	0,93	0,98	1,11	75%-100%	75%-100%	Over 100%
4	205	240	0,85	75%-100%	0,95	1,00	1,14	75%-100%	Over 100%	Over 100%
5	20	25	0,80	75%-100%	0,89	0,94	1,07	75%-100%	75%-100%	Over 100%
6	35	50	0,70	60%-75%	0,78	0,82	0,93	75%-100%	75%-100%	75%-100%
7	85	100	0,85	75%-100%	0,94	1,00	1,13	75%-100%	Over 100%	Over 100%
8	45	100	0,45	40%-60%	0,50	0,53	0,60	40%-60%	40%-60%	60%-75%
9	54	50	1,08	Over 100%	1,20	1,27	1,44	Over 100%	Over 100%	Over 100%
10	90	120	0,75	75%-100%	0,83	0,88	1,00	75%-100%	75%-100%	Over 100%
11	11	60	0,18	Under 40%	0,20	0,22	0,24	Under 40%	Under 40%	Under 40%
12	155	250	0,62	60%-75%	0,69	0,73	0,83	60%-75%	60%-75%	75%-100%
13	74	87	0,85	75%-100%	0,95	1,00	1,13	75%-100%	Over 100%	Over 100%
14	65	98	0,66	60%-75%	0,74	0,78	0,88	60%-75%	75%-100%	75%-100%
15	45	50	0,90	75%-100%	1,00	1,06	1,20	Over 100%	Over 100%	Over 100%
16	55	80	0,69	60%-75%	0,76	0,81	0,92	75%-100%	75%-100%	75%-100%
17	66	68	0,97	75%-100%	1,08	1,14	1,29	Over 100%	Over 100%	Over 100%
18	87	105	0,83	75%-100%	0,92	0,97	1,10	75%-100%	75%-100%	Over 100%
19	89	97	0,92	75%-100%	1,02	1,08	1,22	Over 100%	Over 100%	Over 100%
20	14	30	0,47	40%-60%	0,52	0,55	0,62	40%-60%	40%-60%	60%-75%

Any good ideas or suggestions on how to do this?

Thanks:)

[lbendlin]

I would simplify the source data (you really only need the first three columns),  have a separate table for the intervals (and make the intervals not overlap!),  and then use "What if?"  parameters to model the LTV changes.

[nor303]

Hi

Im not sure how I should do your suggestion in practice. Can you explain a little more? Thanks!

[lbendlin]

Sure. But first I need you to define your non-overlapping buckets.

[nor303]

Thanks! Im guessing that the buckets are the intervalls? They are in fact not overlapping - in fact the column is a conditional column like this LTV < 0,4 = Under 40% next is LTV < 0,6 = 40% - 60% etc.... the last is LTV =>1 = Over 100%

So it should be ok, but do you suggest that I dont make a conditional column to make the intervalls? 

Can this problem also be solved with some measures so that its possible to use this across several of sheets from different quarters?