Excel genius required! - Aussie Stock Forums

1. Excel genius required!

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

2. Re: Excel genius required!

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.

3. Re: Excel genius required!

Originally Posted by 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&#37; 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

4. Re: Excel genius required!

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

5. Re: Excel genius required!

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 !!

6. Re: Excel genius required!

Originally Posted by 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 !!
No worries halfwheel. Try this in T22-

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

Should do the trick.

7. Re: Excel genius required!

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

8. Re: Excel genius required!

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

9. Re: Excel genius required!

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

10. Re: Excel genius required!

Originally Posted by halfwheel
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

11. Re: Excel genius required!

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

12. Re: Excel genius required!

Originally Posted by halfwheel
Thanks Bob. Really appreciate that. I have been getting really frustrated with it !!

Is the percentage supposed to be reading 101.94&#37; 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

13. Re: Excel genius required!

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)

14. Re: Excel genius required!

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

15. Re: Excel genius required!

Originally Posted by 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

16. Re: Excel genius required!

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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Aussie Stock Forums