% change on LAST 2 populated rows of data

[mart10]

I am using Excel 2010

I have rows of data containing currency exchange rates structured as follows:
A B C D E F
%test
CCY CAD EUR JPY HKD USD
3/4/14 1.5 1.2 100 10 1.65
4/4/14 1.4 1.3 110 9 1.7
ETC

The history is built up each day via a macro to paste data onto the next available row to put the date and the rates (my post of last week sorted this)

My next issue is to work out a % change for each ccy based upon the LAST two rows of data in this sheet. As you can see the %change data formula will allways be in the top row. How can I create the formula please?

I am going to conditioanlly format any change more than 2 % - I know how to do this btw

 

[SkipVought]

Hi,

Use COUNT() or COUNTA() to determine the row count in a range.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mart10]

sorry You have lost me on this and how to do it

 

[mart10]

oK the best mthod for me would be to pre test the data . This makes it easier so All I need now is to find the max date which is easy but thn for each clumn to bring in the offseted data

So how can i do this then

 

[SkipVought]

If you use INDEX() along with the count of the rows in the range, then THAT values is the second argument, and of course the third argument would pick off the values in that row.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

I loaded your table into my Excel and CONVERTED it to a Structured Table, default named Table1.

Created a MaxRows cell in A1 (and Named the range as such) with formula
[tt]
A1: =COUNT(Table1[CCY])
[/tt]

Then the average in row 1 for each column
[pre]
(LastRowValue - PrevRowValue )/ LastRowValue
B1: =(INDEX(Table1[CAD],RowCount,1)-INDEX(Table1[CAD],RowCount-1,1))/INDEX(Table1[CAD],RowCount,1)
[/pre]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mart10]

Sorry

I understand the 2nd bit but new to Excel so can you explain further:


I loaded your table into my Excel and CONVERTED it to a Structured Table, default named Table1.

Created a MaxRows cell in A1 (and Named the range as such) with formula

A1: =COUNT(Table1[CCY])

 

[SkipVought]

I don't know what 2nd bit refers to.

Did this give you the desired results?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}