# Which approach to create a chart similar to stock charts - different periods % series start from zero

#### fabiospark

##### New Member
Do you know the charts you usually find on finance pages whe looking for stock data?
You can see how the stock performed in a give period: a day, a week, a month, a year or you can choose
the initial and last dates of the priod you want to monitor.
Whichever the length you choose, the data will use the stock price of the first day of the chosen period
as a floor against which the subsequent variations of the price are confronted and the percentage value of the ratio
are used to present the performance line in the chart.

I'd like to build a thing like that where I can choose the initial and final date a period and in some way the value
of the first day becomes the floor for the subsequent calculations.

As an example let's say I have a table with dates in the first column and values in the second as you see at the end of the post.

If I choose 1-1-20 as the starting date I'd like to have these four percentage values:

val 1 = (123-120)/120 = (B2-\$B\$1)/\$B\$1 = 2.5%
val 2 = (132-120)/120 = (B3-\$B\$1)/\$B\$1 = 10%
val 3 = (126-120)/120 = (B4-\$B\$1)/\$B\$1 = 5%
val 4 = (135-120)/120 = (B5-\$B\$1)/\$B\$1 = 12.5%

But, if I choose 2-1-20 as the starting date I'd like to have these three percentage values:

val 1 = (132-123)/123 = (B3-\$B\$2)/\$B\$2 = 7,32%
val 2 = (126-123)/123 = (B4-\$B\$2)/\$B\$2 = 2.44%
val 3 = (135-123)/123 = (B5-\$B\$2)/\$B\$2 = 9.76%

Does anybody know it it is possible to do that without having to build a column for each period?
And maybe how to do it "on the fly", with some good formula in a named range or anything else?
For now, please consider Excel (2003) only, no VBA.
I can filter the data set, if it is useful or needed.

Thank you.

DatesValues% from 1-1-20% from 2-1-20
1-1-20120
2-1-201232.5%
3-1-2013210%7.32%
4-1-201265%2.44%
5-1-2013512.5%9.76%

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Replies
0
Views
518
Replies
0
Views
99
Replies
0
Views
77
Replies
14
Views
166
Replies
1
Views
106

### Forum statistics

Threads
1,127,936
Messages
5,627,714
Members
416,268
Latest member
zRGZgDNnmaPhepwviEou

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back