# Thread: Excel Formula for Stocks (Excel 2003)

1. ## Re: Excel Formula for Stocks (Excel 2003)

What is in column K? Is it a number with a custom format that puts the " YH" after it, or is it a text value with the YH typed into the cell? Attaching that actual workbook would be a big help to those trying to answer your question.

2. ## Re: Excel Formula for Stocks (Excel 2003)

You could add in M1 the Target percent (eg 80%)
and put something like this in M2 and copy it down
=IF(E2/K2>\$M\$1,"\$\$","")
and copy it down the rows.

Then when the value was >80% of the max it would display "\$\$"

Change the percent as desired to match the required sensitivity.

Steve

3. ## Re: Excel Formula for Stocks (Excel 2003)

Legare:
Col K draws in the year highs for the stocks from a downloaded spreadsheet. I append the "YH" as a text to the number so I know what it means for instance 39YH means that the stock had a year high of 39 and 39 is a number
Jerome

4. ## Re: Excel Formula for Stocks (Excel 2003)

Steve:
That will work if I can get it to ignore n/a values for new stocks that do not have 52 week high values recorded yet in the yahoo database. ie can it not give a #value error for n/a values in K.?
Jerome

5. ## Re: Excel Formula for Stocks (Excel 2003)

=if(isna(k2),"",IF(E2/K2>\$M\$1,"\$\$",""))

If you add text to col K, it will not work. You must keep K a number (or put the number into another column and reference that.) I asumed it was custom formatting.

Steve

6. ## Re: Excel Formula for Stocks (Excel 2003)

Then I think you want a formula something like:

<pre>=IF(ISERROR(E3),"",IF(E3-VALUE(TRIM(LEFT(K3,LEN(K3)-2)))>=-1,"\$\$\$",""))
</pre>

However, it would make things much easier if you put the YH (and other designations) in separate columns.

7. ## Re: Excel Formula for Stocks (Excel 2003)

Legare:
Many thanks, this is terriffic
Jerome

8. ## Re: Excel Formula for Stocks (Excel 2003)

Steve:
Many thanks this is wonderful
Jerome

9. ## Re: Excel Formula for Stocks (Excel 2003)

I was hoping you were going to post a copy of your Stock Spreadsheet so everyone at Woody's could use it to make some extra money. Not even a stock tip............wheres the appreciation?

yoyophil

10. ## Re: Excel Formula for Stocks (Excel 2003)

I wish I was making money, only the oil stocks seem to be approaching highs.
The photo of the spreadsheet kind of explains it all
Jerome

11. ## Excel Formula for Stocks (Excel 2003)

Extremely wide picture that caused horizontal scrolling reduced in size by HansV; in the future please attach a sample workbook instead of a huge picture of one.

I would like to have column M say on the attached print screen spreadsheet to reflect when a stock is near its high graphically. Any suggestions would be appreciated.
For example IF K5, The year High, - E5, the current price, is > or = - 1 or 0 or whatever number makes sense then Column M might have 2 red ** or 2 red \$\$ or something else interesting. So can scan a spreadsheet quickly for stocks approaching their 52 week highs.
Thanks
Jerome

#### Posting Permissions

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