ignoring non-numerical values in sumproduct and transpose

Hi
This is my first post.
I've seen a few threads here with formulas to calculate the absolute difference between pairs, a common statistical process. It involves SUMPRODUCT and TRANSPOSE into an array.

I have issue which must have an easy solution!? In the list of data there are values (which I want to use) and blank values ("") which I don't want used in the calculation.

The formula I have come up with is shown below but I just can't figure out how to ignore the blanks:

{SUMPRODUCT(ABS(E6:E96-TRANSPOSE(E6:E96)))/((COUNTA(E6:E96)*((COUNTA(E6:E96)-1))))}

The real headache is getting the SUMPRODUCT(ABS(E6:E96-TRANSPOSE(E6:E96))) to ignore the blanks. I can get around the bottom bit.

The formula is for the Gini Mean Difference (a different function to the Gini Coefficient).

There has to be a simple solution!

Any help appreciated.

truedogz

Hi

I am trying to attach a sheet indicating what I am trying to do.


TruedogzGMD Example.xlsx