On Tue, 17 Nov 2009 16:00:28 -0800 (PST), zvkmpw
wrote:

so far the layout as follows:-
a6 buy/sell, input b6 amount buy,input
c6 amount sell,input ...

in c6 then i imagine the formula should go something like
=if(a6="sell",(b6-c6), but that does not work for some
reason.

I'd suggest starting with these headings for columns A, B, C.
A1: buy/sell
B1: transaction shares
C1: cumulative shares

Then in C2 put
=IF(A2="buy",B2,"error")
because the first transaction has to be a purchase.

Then in C3 put
=IF(A3="buy",C2+B3, IF(A3="sell", IF(B3C2,"error",C2-B3),""))
which gives a running share count by adding or subtracting as
indicated by column A.

Then copy C3 and paste into C4 downward.

This formula can be used as a model for other calculations of this
nature.


is it possible to have the live date and time displayed
in a selected cell?

=NOW()


anyone aware of portfolio template with more than the
basic features ...?

There are several to choose from he
http://office.microsoft.com/
en-us/templates/results.aspx?qu=excel+portfolio&av=TPL000


many thanks for your help
very impressed by =NOW()
I should explain that my portfolio is based on the microsoft template
http://office.microsoft.com/en-us/te...233521033.aspx
which i have modified. i changed a1,b1,c1 to a,b,and c ref 5. my
understanding is that the (a column remains a buy or sell),(c
cumulative the total number shares bought),' (b' transaction shares
comes into play as the number of shares in a sell transaction equal to
or less than the number shares in dc).I also changed formula to
reflect data entry starting at 6 instead of 2. added first formula as
pasted =IF(A6="buy",B6,"error") thats ok. added 2nd formula as pasted
=IF(A7="buy",C6+B7, IF(A7="sell", IF(B7C6,"error",C6-B7),""))
enter,ctrl/c paste to cells below, i find it odd that the cell
references remain irrespective of the cells the formula is now pasted
to. i am sure that i am doing something totally wrong but cant
figure??????and the cumulative /transaction as opposed to amount buy
amount sell has caused me a slight head scratching moment (peabrain
moment)