Take the 2-minute tour ×

Here is my problem today. I have a revenue (4M) and a rebate for each of the twelve months (a chart with 12 lines). My total rebate is the sum of the 12 rebates. Until there, no problem.

However, I would like to do a simulation and thus do a chart with 11 different values (revenue ranging from 3M to 5M) in order to see how the rebate would change. Here comes the problem, I would like to avoid having a 12 lines chart for each of my lines in my simulation chart.

Is there any way to do this on Excel (without using VBA).

Thank you very much for your help

share|improve this question
I am confused. Are you using a ratio to determine what the simulated rebate would be? –  Pynner Feb 6 '12 at 21:10
exactly. I would like to do a chart with different values of revenues, and for each revenue i'd get a different rebate. I would like to automatize it in other words –  SammyDow Feb 7 '12 at 8:44
add comment

1 Answer

A relatively easy way to get where you want to go is to create your simulations in seperate rows on one tab/area ("data"), then place your chart with the particular set of data to be visualized in a second tab/area ("chart data"). Using a key (ie "X") and vlookup you can flip between data sets. See the picture below... I've put them onto a single tab to show everything in a single screen.

Hopefully this makes sense.

chart sample

share|improve this answer
Thank you very much for the time you took to answer me. However it is not exactly what I wanted to do. My chart (chart_1) will not look like the one you have. It will rather be 5 columns (per example 5M CA, 4M CA, ..., 1M CA) and 3 rows (3 different methods to calculate the rebate). However the problem is that the result of each cell is the result of a long calculation based on another chart (chart_2) with 12 months. The problem is that to find all the values of chart_1, each time I need to change the CA in chart_2, and then I copy paste the result in chart_1 and I would like to automatize it –  SammyDow Feb 8 '12 at 8:34
mostly you need to re-organize your data to automate your solution. I'd need a fair bit more detail to give you specific help. In general you will layout the data such that a simulation fits onto a single row (Regardless of complexity). Once you have that done you can copy down rows and change input parameters fairly quickly. –  Pynner Feb 8 '12 at 17:01
Hey Pynner. Thank you again for your answer. The problem is not so much about having one line but it is about having the initial chart you have (the data chart). Would there be any way for me to send you the file so you it could be clearer about what I am talking about ? –  SammyDow Feb 9 '12 at 8:00
if you can post it somewhere for me to download, i'll take a look at it. doesn't seem to be any way to Private message my email on this site. –  Pynner Feb 11 '12 at 2:17
add comment

Your Answer


