Help!!! - skipping data in excel cells

[irishguy]

Hi,
i'm currently working on a thesis and just realized my calcs are all off. i'm using the filter option to filter my data. there is about 1,000 securities with prices for each cell and an option to filter by country. when i get the average of each country using the AVERAGE function, it sum totals everything, even though i've just filtered for that country. can anyone help? i highlighted the cells and that was fine as it just took the filtered cells so i assumed the formula would just calc the filtered cells.

any help would greatly be appreciated

 

[hiker95]

irishguy,

Welcome to the MrExcel forum.

In Excel HelP, lookup SUBTOTAL.


Replace your AVERAGE function with something like this:
=SUBTOTAL(101, and_the_range_like_A2:A100)

The 101 means ignore hidden values.

When you filter the data the average will change.

 

[borgem]

Try using Subtotal(101,Array) it averages the data while ignoring hidden data values.

Ref: link

 

[irishguy]

Thanks very much guys helped allot!