Filtering Function??

Is there a function or formula that can filter other cells? I have a sheet that has averages of different variables of data on another sheet. Now, I am having to input the averages by hand because some of the other values are “NaN.” In other words, I filter out NaN’s on certain columns and generate values that I need. Is there a way that I can write a function that can call a filter, and then do some computing? Or, is there a more logical program to use than Excel? I tried to explain this, the best I could, but if there are any questions please ask. I am having a hard time researching this on my own.

Originally Posted by Stormseed

Can't you just do a Find/Replace (Ctrl+H) and remove the "NaN" ?

Incase you are not aware of the steps:
Select or highlight your data, Excel menubar --> Edit --> Replace, (or press Ctrl+H) - in the "Find What" option, type "NaN" without quotes and then leave the "Replace with" option blank and click on "Replace All".

Or you want something else ? Could you upload your workbook for us to have a closer look ?

I tried this, and I get a #VALUE! in my resultant cells because of the blanks.
since its averaging, 0's would also change the results..

Originally Posted by ContaminatedWitExcel

Can u post an exampel?

how do i do this?

Originally Posted by shg

Read the forum rules?

you asking me? i'm new to the forum. lol

most of the data is classified, however I was going to make example workbook but it would be even more confusing..

I am dealing with a large amount of data on several sheets. The data are from different tests with different conditions, so my first sheet has sums/averages of only certain values, but I am having to do the filtering and calculations by hand to put together my first sheet.

My idea was to try and find a way to write a function in the first sheet cells that can filter what I don't want to include in the calculations, and then do the computing. Otherwise, if there is just one variable change, I have to do it all over by hand.

I tried to explain this as simple as possible.. Any ideas would be great!

i guess, a VBA (macro) code would be best for me..
i've never written one so i'll have to do some research

Originally Posted by JBeaucaire

Are you simply asking how to write a formula to average a range of cells and ignore cells that are zero or "NaN" in value when computing the average? Excel can't do that on its own, but you can construct your own average formula that does that:

=SUM(A1:A100)/(COUNTIF(A1:A100,">0"))

This formula ignores text values and only "counts" the cells greater than zero when doing the division.

thanks for the info!
i am just trying to filter cells in general.. without having to do it by hand
i.e. have a column that filters different data than another column next to it
but have the column do this on its own..

i guess its just a matter of writing the vba code to do this

could somebody link me a good tutorial on VBA on Excel?