Formula to compare last 2 numbers in row for upward, downward trend calculation

[Zyphon]

Hi All,

This is a follow up to a recent post of mine regarding KPI figures (link below) which was answered by cbatrody (kudos to cbatrody many thanks).

http://www.excelforum.com/showthread...t=#post4139336

In the thread above I asked for a formula that would look at only the last value of a row that matched the month of today's date. I would like to know what formula I could use to compare the last two numbers in a column so that I can track upwards, downwards or sideways trend.

I have attached a sample to illustrate the desired outcome. It needs to be dynamic and look at the dates in row 2 (formatted in 'mmm' format) and compare the latest month to the previous month. So for example in the attached as it is July 24 today and not the end of July; it should compare the May and June figures.

Take row 6 as an example; in column R I am using the SIGN formula:

Please Login or Register  to view this content.

This compares June (column G6) and subtracts the figure from May (column F6). The result produces 1 for updward trend, -1 for downward trend and 0 for sideways or no movement trend. Then I use the Arros Icon set to map the direction. And an aditional conditional formatting Red Amber Green to track we the figures are in relation to the annual targets. So you may have an amber target but an upward trend.

Is there a way to adapt the formula in the link to my previous post so that it can compare the last two figures?

[Pete_UK]

Try this in R4:

=SIGN(INDEX(E4:P4,COUNT(E4:P4))-INDEX(E4:P4,COUNT(E4:P4)-1))

then copy down.

Hope this helps.

Pete

[Zyphon]

Hi Pete,

Sorry for the late response, I've very busy at work today. Thanks for your response and the excellent formula it works great! The only issue I may have is that the spreadsheet for the 'live' KPI spreadsheet is linked and shows zeros this may affect this formula. Sorry I should have mentioned that.

I got around this by using this formula, I hope this works ok it seems to.

Please Login or Register  to view this content.

Many thanks again for your help Pete I have added to your Rep and marked the thread as solved.

[Zyphon]

Sorry my post above is incorrect. What I meant to say is that in the cells in columns E:P which are linked. I used the formula below so that blanks appear instead of zeros.

Please Login or Register  to view this content.

[Pete_UK]

Yeah, that makes more sense now.

Glad you got it working - thanks for the rep.

Pete