Three columns of numbers: How to find which is closest to 0?

[philia]

I have three columns of percentages, negative and positive. I need to know which column is closest, on average, to 0.

I used the ABS function on each column and then calculated the AVERAGE of each ABS column to see which of the three original columns had the lowest average but I'm seeking a more elegant solution (even a more elegant use of the ABS function) and method. If anybody has a suggestion, I'd be grateful. Thank you.

 

[mirabeau]

if you like the ABS kind of approach you could look at the AVEDEV() function.

Although for statistical work "closeness" is more often considered as least sum of squared deviations rather than least sum of absolute deviations.

There's reasons for this ...

 

[philia]

Would a STDEVP function be the most useful tool here?

 

[mirabeau]

Would a STDEVP function be the most useful tool here?

STDEV type of calculations give information about average closeness to the mean (or "average") value of your data and/or population.

But your opening post asked about closeness to zero. For this you could try the SUMSQ function (if your columns are of equal length) or otherwise use SUMSQ/COUNT (with due allowance if you've got, or think you may have, empty cells or non-numeric cells)

I don't give this in more detail, because just how you go about it really depends on why you're doing this in the first place.

 

[philia]

STDEV type of calculations give information about average closeness to the mean (or "average") value of your data and/or population.

But your opening post asked about closeness to zero. For this you could try the SUMSQ function (if your columns are of equal length) or otherwise use SUMSQ/COUNT (with due allowance if you've got, or think you may have, empty cells or non-numeric cells)

I don't give this in more detail, because just how you go about it really depends on why you're doing this in the first place.

Thank you for your response.

It has been a long while since I've studied statistics, so I suspect I'm not analyzing this properly. Essentially, I want to know which of these three columns of numbers would be the best fit for a line with a value of 0. Maybe a coefficient of determination (CORREL?) would be what I'm looking for here? Could I lay out a column of 0s, at the same length as the three columns (which are all the same length), and see which has the highest r value?

 

[mirabeau]

It's not clear what you mean by "line with a value of 0"

If you want to correlate a set of numbers with zero (or any constant value) it might be instructive to try it and see, but the outcome is indeterminate, i.e. it doesn't mean anything. Excel will give you a div by zero error.

The CORREL function is based on the same kind of sums of squared deviations from the mean as is STDEV (cross-products as well with CORREL) and, without knowing more about why you're doing this, wouldn't seem of much use in measuring closeness to zero.

If you want average closeness to zero of a set of numbers, probably (again depends on why you're doing it) the best approach would be to calculate the average sum of squared differences from zero. "Best" because more standard statistical methods are then available for obtaining any subsequent statistical properties of your results.

Or the AVEDEV approach of calculating average of absolute differences could also be OK.

 

[philia]

It's not clear what you mean by "line with a value of 0"

If you want to correlate a set of numbers with zero (or any constant value) it might be instructive to try it and see, but the outcome is indeterminate, i.e. it doesn't mean anything. Excel will give you a div by zero error.

The CORREL function is based on the same kind of sums of squared deviations from the mean as is STDEV (cross-products as well with CORREL) and, without knowing more about why you're doing this, wouldn't seem of much use in measuring closeness to zero.

If you want average closeness to zero of a set of numbers, probably (again depends on why you're doing it) the best approach would be to calculate the average sum of squared differences from zero. "Best" because more standard statistical methods are then available for obtaining any subsequent statistical properties of your results.

Or the AVEDEV approach of calculating average of absolute differences could also be OK.

Again, thank you for taking the time to respond.

I'll try to explain the project: The three columns of numbers were originally generated from a database, and each created using a distinct and different filter. Those three columns were each then set against a fourth column of pre-existing numbers by simply subtracting them from the values of this fourth column--hence the negative and positive values.

I will look at measures of dispersion for now. I'm using STDEVP, because the set of values in each column is finite and complete, and I hope that's correct--rather than using STDEV, for instance. I need to read up on the difference between STDEVP and AVEDEV here...I feel I've taken enough of your time, but if you have any commentary or thoughts it would be most welcome too.