Discussion in 'Business Applications' started by Sid McGyver, Oct 6, 2008.

Not open for further replies.

Joined:
Oct 6, 2008
Messages:
5
We are playing a silly game that is teaching me about trading on the Stock Market. I am trying to figure out how to work my spreadsheet up so that it will tell me when to buy and when to sell my stocks in certain companies. I have the basic spreadsheet with category, business, bought, sold, buy, sell but I am having a problem. I need a column to automatically tell me the lowest amount seen in "buy" and the highest amount seen in "sell". Is there a way to do this? I used to think I was proficient in Excel but this gives new meaning to "tearing your hair out" for me. I'm sure that there are pivot tables and all sorts of other things to utilize but I'm not sure how when I'm only wanting to see things a certain way. Can you help?

2. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
It sounds like all you need are two formulas. If your "buy" is in column A and your "sell" is in column B, they would be
=MIN(A:A)
and
=MAX(B:B)

Joined:
Oct 6, 2008
Messages:
5
Thank you slurpee. That helps with the buy and sell but is there an easy way to be able to track the ups and downs to determine the buy and sell? In other words, if I check the market at 8am and gold is at 5K per share then check again at noon and see that gold is at 10k per share but I check at 3pm and gold is at 3k per share.........how to determine the lowest point and highest point so that the buy and sell would auto populate?

Joined:
Oct 6, 2008
Messages:
5
Would it help to upload my current spreadsheet? It isn't much but maybe give you an idea of what I'm trying to achieve without 100 columns of data.

5. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Yes, uploading your file would help - just have a bit of data, but make copious notes about what you want as the final result.

6. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
I'm thinking of a number between 1 and 10 .... .... .... and it's .... .... .... pivot table!

Joined:
Oct 6, 2008
Messages:
5
I think I may have figured it out literally but I'm not sure on the formula. I've attached the spreadsheet. Here is what I think it should do so that only the important numbers change.

If d1 is less than or equal to e1 then g1=d1
If d1 is greater than or equal to f1 then h1=d1

Does this make any sense? I want it to change the buy and sell based on the lows and highs that I identify from the "last check".

Example: If the lowest I've see brewery stock at is 5mil and the highest I've seen it at is 5mil then my buy and sell would be the same. However, if I check it tonight and it has gone up to 88mil then I want the sell to reflect that change when I enter it. This would mean manually changing the high and low which I can do as I don't think it would change that much over time unless we have another Enron incident! lol Tell me what you think and if there is a formula to plug in for that.

File size:
57 KB
Views:
281
8. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Well, in G2 you could have
=IF(D2<=E2,D2,"")
and in H2 you could have
=IF(D2>=F2,D2,"")

Joined:
Oct 6, 2008
Messages:
5
Yay! Thank you so much Slurpee! My mind was all jumbled up with learning all this stuff and I couldn't see the simplest solution to my spreadsheet problem. I appreciate it.

10. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Glad to help. Been at that "jumbled up" place myself, plenty of times!

As Seen On