using COUNTIF with an ABS range

[Enz]

I have a range D249, where values could be negative or positive. I
need to take the ABS of the rows, and then count the values that fit
within ranges like <5%, 5.1-7%, greater than 10%.

I am having problems with getting the COUNTIF to work properly. Am I
missing something obvious, or perhaps is it better to do this using a
function?

Any help would be greatly appreciated.

regards,
Enzo

 

[Student]

Check out the FREQUENCY function and remember it has to be entered as
an array formula.

 

[Shane Devenshire]

FYI,

The FREQUENCY function can be entered as an array or not depending on what
you want. Both entry forms are useful.

One gives you the cum frequency to a point range, the other the incremental
frequency of the point range.

 

[Shane Devenshire]

Hi,

You haven't shown us the formulas you are using.

If you are trying to do =COUNITIF(ABS(D249),"<5%") its not going to
happen! The range argument of the SUMIF, COUNTIF, SUMIFS, COUNTIFS,
AVERAGEIF, and AVERAGEIFS aren't flexible enough to allow calculations, I've
asked Microsoft for this enhancement, but i'm not holding my breath.

Try:

=SUMPRODUCT(--(ABS(D249)<5%))
=SUMPRODUCT((ABS(D249)>5%)*(ABS(D249)<=7%))
=SUMPRODUCT(--(ABS(D249)>10%))

Of course you realize you have excluded 7.1-10%?

 

[Student]

The OP asked for a frequency distribution, not for cumulative
frequencies, and for distribution you need the array formula.

BTW, a much simpler solution (assuming Data Analysis ToolPak has been
installed), is to do it from Data Analysis -> Histogram, and there you
can get both distribution and cimulative frequencies, even Pareto
histogram and a chart.

 

[Shane Devenshire]

If you decide to attach this with the FREQUENCY function its going to be
tricky for a few reasons 1. The entry of the function, 2. the fact that
frequency ignores blank cells but ABS does not, 3. the fact that you are
excluding the range 7.1-10.


** I also just noticed that you excluded the value 5 from your range?? You
have <5 and 5.1-7.

1. Create a bin range that looks like this:
H I
0.00%
5.00%
7.00%
10.00%


Assuming these are in H1:H5, then highlight I1:I5 and enter the Array formula:
=FREQUENCY(ABS(D249),H1:H4)

Since this is an array formula it must be entered by pressing
Shift+Ctrl+Enter.

If any of the entries in D249 are blank or contain 0 you will get results
in I1. You can deal with the empty cell by using

=FREQUENCY(IF(D249="","",ABS(D249)),H1:H4)

the reason to extend the formulas down to I5 is because the FREQUENCY
function automatically adds an extra result for all those items above the
last bin. So by adding this you cover yourself in case you forgot something,
in our case anything above 10%.

Now regarding non-array entry of the FREQUENCY function: In J1 enter the
formula

=FREQUENCY($D$2:$D$49,H1)

and copy it down to J5. It isn't handling the abs stuff yet but the idea is
that the entry in J2 is the cum of all items 5% and lower, J3 is the cum of
all items 7% and lower...

Now to deal with the ABS's we will need to array enter the formula but NOT
the same way we did in column I. Select just J1 and array enter:

=FREQUENCY(IF(D249="","",ABS(D249)),H1)

Copy this formula down. The array entry was not needed for the FREQUENCY
function in this example, it was needed for the ABS and IF functions to work.

 

[Student]

Have you tried this? SUMPRODUCT does not output any frequencies in
this case.

 

[Enz]

My apologies for not sending the original formula Shane, I should have
but it was originally a convoluded countifs.... taking differences of
sums, which was much more complex than it needed to be.

The SUMPRODUCTS you suggested

=SUMPRODUCT(--(ABS(D249)<5%))
=SUMPRODUCT((ABS(D249)>5%)*(ABS(D249)<=7%))
=SUMPRODUCT(--(ABS(D249)>10%))

works perfectly. thanks for your help. I did try the FREQUENCY but
had issues due to the negative percentages.

Again thanks!

 

[Student]

Ignore this. I overlooked coercion.