Trigger Figures anyone?

[Macanta]

Hi Folks,

This problem is about monitoring the price of stocks on the stock market and
selling when a stock falls back from a percentage above its highest price. I
want to be able to input a figure (the daily price) in each cell in a row
and have the latest figure highlighted in bold or in a different color if it
has fallen a set percentage below the previously highest recorded price. So,
say we have set the percentage to 15 and we start on Jan 1st at 100 and the
price rises to 115 by Feb 1st. If the price then falls and by March 1st it
hits 97.75 then it has hit its trigger figure to be sold. This is the figure
that needs to be highlighed. I would also like to have the trigger figures
on display, say in column B for easy reference and for changing if
necessary.

Many thanks.


Mac

 

[Pete_UK]

Assume you have row 1 for headings and that column A is for name of
stock, column B is trigger level (eg 85%), column C is first date (eg
1st Jan) and column D onwards is for subsequent dates - you record the
stock values in these columns from row 2 downwards.

Highlight the cells from D2 as far across as you want to go and as far
down as you have stocks, and click Format | Conditional Formatting and
select Formula is from the first pull-down, then enter this formula:

=AND(D2>0,D2/MAX($C22)<$B2)

then click Format in the panel and choose your effect - e.g. Red
foreground with yellow background, then click OK. Test it out with some
values eg 100 start going up to 115, then reducing to 95 - the
formatting comes into effect if the percentage of the value in a cell
compared with the largest preceeding value drops below the trigger
level in column B, as requested. Note trigger level is entered as 85%
(or 87.5%), not 15% (or 12.5%).

Hope this helps.

Pete