Conditional formatting based on trends

[Philip Drury]

I have a summary spreadsheet that works a a dashboard for performance, cells
turn green or red using conditional formatting based on performace values
each month. The sheet conatins 12 months of data and the last column uses
conditional formatting to display an arrow showing whether there was an
improvement on last month or not. We have now been asked to turn the cell
containing the arrow amber if the trend based on the last 3 months only is
that we are going to fail to meet target. Has anyone got any ideas how we can
do this??
Thanks

 

[Pete_UK]

Give some more details - sheet names that you use, cells where the
data is located, formulae that you use now etc.

Pete

 

[Philip Drury]

The structure of the work book is that it has an input sheet, this then
supplys data to individual performance sheets according to the relevant
performance indicator - these sheeta are then summerised on the summary sheet
(I can send you the summary sheet if that helps?). The majority of formula
are lookups with conditional formatting for the colour changes.

 

[Fred Smith]

You'll get much better and faster responses if you provide the specifics
that Pete asked for. What is "the cell containing the arrow"? Is it A1,
Q147, IV65000? Also, how do you know when "the trend based on the last 3
months only is that we're going to fail to meet the target"? Is some cell
negative? Is some range less than some other range?

If you want the solution, provide the specifics in your message. People
don't want to download spreadsheets because of the risk of viruses.

Regards,
Fred.

 

[AdamV]

what version are you using?
is this arrow some custom thing or a built-in format from Excel 2007?
how do you intend to calculate a trend?

if the last three months sales were 1000, 2000, 4000 units, do you predict
next months to be 2333 (the average), 7000 (delta is 1000, 2000, ...3000) or
8000 (delta doubles each time)?

you might be looking for a conditional format formula something like
AVERAGE(LastThreeMonths)*4 < AnnualTarget