frequency excl #N/A

[Saintsman]

How do I calculate the frequency for a set of numbers which includes #NA
error ie ignore the error

Thanks

 

[~L]

The following worked for me:
=FREQUENCY(A1:A25*NOT(ISERROR(A1:A25)),B1:B25*NOT(ISERROR(B1:B25))

Let me know if it doesn't work for you.

 

[~L]

Forget this reply. It worked for me because I didn't recalculate and I was
in manual calculation mode. So, actually, it didn't work at all.

 

[~L]

=FREQUENCY(IF(NOT(ISNA(A1:A25)),A1:A25,0),IF(NOT(ISNA(B1:B25)),B1:B25,0)

Array entered using Control + Shift + Enter.

I'm going for coffee.

 

[Shane Devenshire]

Hi,

You didn't tell us the formula you are using for frequency, so

If you are using COUNTIF then

=COUNTIF(A$1:A$9,C1)

No array needed and no check for the NA.

If you are using FREQUENCY thent ry the following array entered formula:

=FREQUENCY(IF(ISNA(A1:A9),"",A1:A9),C1:C4)

Where A1:A9 is your data, C1:C4 is your bin range.

 

[Saintsman]

Thanks very much - that gave me what I needed

Hi,

You didn't tell us the formula you are using for frequency, so

If you are using COUNTIF then

=COUNTIF(A$1:A$9,C1)

No array needed and no check for the NA.

If you are using FREQUENCY thent ry the following array entered formula:

=FREQUENCY(IF(ISNA(A1:A9),"",A1:A9),C1:C4)

Where A1:A9 is your data, C1:C4 is your bin range.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire