Excel 2007 Spreadsheet Woes

Merodoc

I am dreadfully incompetent at all things Excel or Microsoft Office, so I need the help of the community on managing my portfolio.

I am trying to create a stock portfolio data spreadsheet for Excel 2007, but I am encountering many problems.

Firstly, I am trying to find a formula that will divide the current value of the stock with the initial price. However, I receive a #Value error (or some equivalent) whenever I try to enter =Quotient(G6:G100,F6-F100).
(Look at link below: you will see two colums {init price, value}--I want to have the most recent entry be subtracted by the initial price and then divided by the initial price (F6-G6)/(G6). However, I also want it to automatically adjust itself with the most recent entry (a macro, maybe).

Also, can Excel automatically change the color of the text depending on the preceeding value? If I enter in $222 in one cell followed by $232 in the next, can Excel color the numbers red or green depending on higher/lower?

More later. (If you need further explaining, feel free to PM me.)

I appreciate all help.


http://img75.imageshack.us/img75/9139/portvb5.png

n25philly

hey genious, if you are no good at excel, why are you using a beta version of the next office that is way different from the older version and still has bugs to be fixed in it?

Merodoc

I don't want to pay hundreds of dollars for a product that will soon be out of date. Right back at you, genuis.

britoca

Okay, I don't know how much you know about excel, so I don't want to give out extremely detailed and unecessary tips, and it's a bit hard to see exactly what u are trying to do if not sitting there with u and discussing it, but about this formula:

=Quotient(G6:G100,F6-F100)

did u review the help for that function? It will give you the bottom value of that operation. It will not accept ranges like you have there, only values.
Quotient function help

If you want to calculate over an array, or a series of values listed in a collumn, do it like this:

=Quotient(G6,$F$6-$F$100) for the first cell to calculate. The $ lock the coordinates for the cells F6 and F100. You can lock also the letter or the number only ($F6 or F$6)

Then, assuming that you have all the values already listed in that column G, you hover the mouse over the right bottom corner of the cell you just calculated your formula into. the cursor changes to a dark cross. You can just left-click-hold and drag that down to row 100 and it should auto-fill the formula for all those cells with their corresponding cells calculated.

Sorry, not the best way to explain this, but I hope that helped

n25philly

brilliant.  good luck with it then.

Merodoc

I appreciate your help, britoca, but I can't seem to understand your directions. :P

Here is another picture---> http://img64.imageshack.us/img64/8357/port1vy3.png

britoca

in H6 enter the following:

=QUOTIENT((F6-$G$6),$G$6)

then do the right corner cell drag thing. Drag it as far down as you have values in column F. So if you have values in cells F7, F8, 9, ... 25, etc, drag the right bottom corner of cell G6 down to those same rows.

The formulas inside each of the newly filled cells in collumn G should now have
=QUOTIENT((F6-$G$6),$G$6)
=QUOTIENT((F7-$G$6),$G$6)
=QUOTIENT((F8-$G$6),$G$6)
=QUOTIENT((F9-$G$6),$G$6)
etc

Merodoc

I am getting an error reading "too few arguments"....

britoca

hmm if that spreadsheet is the one you're using and you typed in exaclty that formula, it should work