Excel genius required!

[halfwheel]

Hi

I am trying to alter an excel spreadsheet that I downloaded from a thread on this forum. I havent used Excel before and Im having trouble with the formula that is responsible for calculating the capital gain. As it is at the moment even if a stock isnt sold it is assuming that it was a 100% loss. Is anyone a genius at excel that can change the formula for me or tell me what to do? I have loaded some figures into it so that you can see what I mean


Thanks


Halfwheel.

P.S Im new to this Forum and I have to thank everyone for sharing their ideas on here. I have been lurking for a while and reading all of your posts and it has been very helpful

 

[exgeo]

Have a look in help for the "If" statement in formulas. I think that's what you need. If the condition is met, do the first thing, if not do the second if I remember correctly. Help will have the syntax.

 

[professor_frink]

Hi

I am trying to alter an excel spreadsheet that I downloaded from a thread on this forum. I havent used Excel before and Im having trouble with the formula that is responsible for calculating the capital gain. As it is at the moment even if a stock isnt sold it is assuming that it was a 100% loss. Is anyone a genius at excel that can change the formula for me or tell me what to do? I have loaded some figures into it so that you can see what I mean


Thanks


Halfwheel.

P.S Im new to this Forum and I have to thank everyone for sharing their ideas on here. I have been lurking for a while and reading all of your posts and it has been very helpful

based on a 30 second look at the spreadsheet( so I have no idea if this means everything else on the sheet will still work properly), if you change the formula in S22 to this-

=IF(N22>0,N22-H22,0)

then it shouldn't put anything in S22 until you've filled out the details of the sale.

Hope it helps

 

[halfwheel]

At reference S22, Formula is =N22-H22

N22 is blank because it is nil because it hasnt been sold
H22 is $2024.60

The answer it gives me is $2024.60. I want it to stay blank or zero until there is a value at N22

 

[halfwheel]

Gee thanks Bob. Is there any way of stopping the red 100% showing up in the total % column. Ive spent all day trying to fix this. Thanks !!

 

[professor_frink]

Gee thanks Bob. Is there any way of stopping the red 100% showing up in the total % column. Ive spent all day trying to fix this. Thanks !!

No worries halfwheel. Try this in T22-

=IF(N22>0,(N22/H22)-1,0)

Should do the trick.

 

[halfwheel]

Thanks Bob. It solved that but now I have another problem. Boxes with name in them???

 

[halfwheel]

Sorry Bob. My mistake. I must have added a wrong value somewhere because Ive fixed it although the percentage calculated is out somehow

 

[professor_frink]

not quite sure what you did there

Go to the bottom right hand corner of cell S21 and drag it down over the grey cells below. That should clear it up for you.

If you aren't quite sure of what I'm talking about(don't worry, I don't know what I'm going on about half of the time!), I've attached your spreadsheet onto this post. It should be fixed now

 

[professor_frink]

Sorry Bob. My mistake. I must have added a wrong value somewhere because Ive fixed it although the percentage calculated is out somehow

No worries halfwheel. Good luck with it

 

[halfwheel]

Thanks Bob. Really appreciate that. I have been getting really frustrated with it !!


Is the percentage supposed to be reading 101.94% when I bought for $2016 and sold for $2055.20 ? I was after the percentage gained

 

[professor_frink]

Thanks Bob. Really appreciate that. I have been getting really frustrated with it !!


Is the percentage supposed to be reading 101.94% when I bought for $2016 and sold for $2055.20 ? I was after the percentage gained

T21 should be-

=IF(N21>0,((N21-H21)/H21),0)

That should fix it up for you

 

[halfwheel]

Thanks Bob. Thats saved me so much work. Thanks a bunch !! Only problem I have now is there isnt a big enough total in the Total $ column !!!! Being new to the market I guess it will be a while until I fix that one.

Thanks again


Halfwheel (Alan)

 

[halfwheel]

Can you please tell me how to convert this formula so that the answer is rounded up to the next whole number. (I dont want any decimal places)

=SUM(85+(D9/3)+((D11/(7/10))-D11))



Thank you

 

[halfwheel]

Can you please tell me how to convert this formula so that the answer is rounded up to the next whole number. (I dont want any decimal places)

=SUM(85+(D9/3)+((D11/(7/10))-D11))



Thank you

Scratch that ... I figured it out

 

[CFD]

Depending on what you are doing with the answer to the formula and what you did to not have decimal places, be aware that if you used formatting it only changes what you see and not what is calculated. You need to use a function in your formula to change what is calculated.