Filtered averages and count

[mart10]

I am using Excel 2013

I have data as follows. it is a matrix of foreign exchanges rates which are built up every day . I need to get the count and the average rate from FILTERED DATA and the results MUST show at the top in row 1. So the history is built up as shown and then what I do is filter column B using the 'Between' function to get start and end day of a particular month. I want to do it like this not in any other way. But how can i get the average FX rate and the count please.

I show simple example based on just selecting 3 days of data (cell A1 is the count)



A B C D E
row 1 3 1.90 2.20
row 2 Date AUD CAD EUR JPY ........
row 3 1/12/2014 1.85 2.00 1.2
row 4 2/12/2014 1.90 2.20
row 5 3/12/2014 1.95 2.40
.....
....

 

[combo]

You can use SUBTOTAL with first argument equal to 1 for average and 2 for counting numbers. It automatically includes only filtered (visible) records.
Alternatively it is possible to build filter and calculate with excel database functions DAVERAGE and DCOUNT.

combo

 

[Andrzejek]

As FYI, use Pre tag to align your data. It is a lot easier to read

[pre] A B C D E
row 1 3 1.90 2.20
row 2 Date AUD CAD EUR JPY ........
row 3 1/12/2014 1.85 2.00 1.2
row 4 2/12/2014 1.90 2.20
row 5 3/12/2014 1.95 2.40
.....
....[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.