#### ELCADAROSA

##### New Member

- Joined
- Jul 24, 2010

- Messages
- 3

My spreadsheet is setup as follows:

Column A: Date of last trading day of the week - usually a Friday - format; Short Date (mm/dd/yyyy).

Column B: Closing price of the stock for that trading week - format; Accounting.

Column C: Total volume of trading of that stock for the week - format; Number.

Column D: Earnings Per Share (EPS) per Quarter, entered on the same row as the date the quarter ended - format; Accounting.

I have a chart setup as follows, but it isn't quite what I want:

Left Y-axis (primary axis): arithmetic scale, reflecting the EPS dollar amounts.

Right Y-axis (secondary axis): arithmetic scale, reflecting the stock price.

Horizontal X-axis: associated with week-ending date.

I want to change the Y-axis' such that one reflects the EPS amount (preferably the left axis), and the other the Stock Price such that each grid line Stock Price amount indicated is equivalent to 20x the EPS amount; i.e. a $.50 EPS gridline amount is the same as $10 stock price, a $1.00 EPS gridline is equivalent to $20 stock price, the $2.00 EPS = $40 price, $3.00 EPS = $60 price, $4.00 EPS = $80 price, ad-nauseum.

To put it more simply, each grid line should reflect:

Either: Stock Price = 20 * EPS

Or: EPS = .05 * Stock Price

I've seen charts like this before, so it must be able to be done. Using Microsoft Access is also a possibility, but I prefer to prove calculations, etc. in Excel before moving on to Access.

Using Office 2007/2010 on Windows XP & 7.

Any help is greatly appreciated.