Frequency function omitting zeros and errors

**ArekRos** · 08-04-2012, 04:17 PM

Hi all,

I have a problem with completing a proper function in excel. The goal is to calculate a frequency of values in huge matrix. These values are the distances between particles in some material.
The problem is that formula must count even if there are errors in the array and also must not count zeros.

Do you have any idea how to solve this issue?

**shg** · 08-04-2012, 04:28 PM

Please Login or Register  to view this content.

The array formula in J1:J10 is

=FREQUENCY(IF(ISNUMBER(1/A1:G15), A1:G15), I1:I9)

**ArekRos** · 08-04-2012, 04:54 PM

Well, your formula is not solving my case, unfortunately.

Frankly speaking I will need something that combines those two functions with frequency function:
IFERROR(MATRIX;"") and COUNTIF(matrix;0)

I have used those function for calculating the average values (it looks like this: =AVERAGE(IFERROR(MATRIX;"");COUNTIF(matrix;0)) )
This time it I have to apply this for Frequency.
Any ideas?

**shg** · 08-04-2012, 05:01 PM

I have an idea that you should post a workbook and explain in context.

**ArekRos** · 08-04-2012, 05:28 PM

There is the part of the workbook we are talking about. The matrix array is much bigger as I said.
So I need the frequency at which each interval (or in this example "Class" number) is present in the matrix.

Usually I would use the following function:

=FREQUENCY(matrix, class) or like you see on the print screen: =frequency(A2:G12;I2:I12)

but as I said: I con not count "zeros" and the formula must be errors resistant...

frequency.JPG

**shg** · 08-04-2012, 06:11 PM

What is #ARG! It's not an Excel error.

Even so, the formula I posted should work fine. If you post a workbook instead of picture, I'll add it to the worksheet.