I have a column where I am calculating the growht rate of a number and
formatting the reults to show a % with no decimal places. And in this column
I am trying to find out which % values occurs the most.

In my column 1 is the raw numerical data.

In my column 2, I am calculating the % growth of the data in column 1. I
have formatted the column 2 data to display % with no decimal places. So my
column 2 data looks as follows

15%
15%
12%
17%
17%
19%
9%
15%
9%
11%

When I try to use the function COUNTIF(B4:B14, 15%), I get a value of 0.
When I hardcode the % values in the cell B4 - B14 and then apply the COUNTIF
formula I get an accurate value of 3.

Can anyone help me determine how I can apply the COUNTIF function to my %
formual cell range.

Thanks.

Roy.

So these values are calculated, so most likely they are not exactly 15%
but something like 0.151 etc. You can either go to toolsoptionscalculation
then check precision as displayed

or round your calculated formula to the nearest percent

=ROUND(your_formula,2)

I would personally go for the latter method



Regards,


Peo Sjoblom



Cesar wrote:
I have a column where I am calculating the growht rate of a number and
formatting the reults to show a % with no decimal places. And in this column
I am trying to find out which % values occurs the most.

In my column 1 is the raw numerical data.

In my column 2, I am calculating the % growth of the data in column 1. I
have formatted the column 2 data to display % with no decimal places. So my
column 2 data looks as follows

15%
15%
12%
17%
17%
19%
9%
15%
9%
11%

When I try to use the function COUNTIF(B4:B14, 15%), I get a value of 0.
When I hardcode the % values in the cell B4 - B14 and then apply the COUNTIF
formula I get an accurate value of 3.

Can anyone help me determine how I can apply the COUNTIF function to my %
formual cell range.

Thanks.

Roy.

Thank You Peo. The round function works like a charm. Thanks so much for
your help.

"Peo Sjoblom" wrote:

So these values are calculated, so most likely they are not exactly 15%
but something like 0.151 etc. You can either go to toolsoptionscalculation
then check precision as displayed

or round your calculated formula to the nearest percent

=ROUND(your_formula,2)

I would personally go for the latter method



Regards,


Peo Sjoblom



Cesar wrote:
I have a column where I am calculating the growht rate of a number and
formatting the reults to show a % with no decimal places. And in this column
I am trying to find out which % values occurs the most.

In my column 1 is the raw numerical data.

In my column 2, I am calculating the % growth of the data in column 1. I
have formatted the column 2 data to display % with no decimal places. So my
column 2 data looks as follows

15%
15%
12%
17%
17%
19%
9%
15%
9%
11%

When I try to use the function COUNTIF(B4:B14, 15%), I get a value of 0.
When I hardcode the % values in the cell B4 - B14 and then apply the COUNTIF
formula I get an accurate value of 3.

Can anyone help me determine how I can apply the COUNTIF function to my %
formual cell range.

Thanks.

Roy.

When you format a cell you merely change what is DISPLAYED not what is
STORED.
So 0.153 formatted as a percent (that just means multiplied by 100) with no
decimals show 15% but still has the stored value of 0.153. So
COUNTIF(B3:B14,15%) will not find it

Let say your percentage figures are computed using something like
=(A19-A18)/A18
Replace that by =ROUND((A19-A18)/A18,2) and format as percent
The ROUND function actually makes the 0.153 become 0.15 which displays a 15%
Now the COUNTIF will work.

Another way put number 1%, 2%, ..... 50% ....99% in E1 down
Select all the cell in F beside these, say F1:F100
Type =FREQUENCY(B1:B1000,E1:E99) and commit with SHIFT+CTRL+ENTER
Now you see the frequency of each percentage range.

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cesar" wrote in message
...
I have a column where I am calculating the growht rate of a number and
formatting the reults to show a % with no decimal places. And in this
column
I am trying to find out which % values occurs the most.

In my column 1 is the raw numerical data.

In my column 2, I am calculating the % growth of the data in column 1. I
have formatted the column 2 data to display % with no decimal places. So
my
column 2 data looks as follows

15%
15%
12%
17%
17%
19%
9%
15%
9%
11%

When I try to use the function COUNTIF(B4:B14, 15%), I get a value of 0.
When I hardcode the % values in the cell B4 - B14 and then apply the
COUNTIF
formula I get an accurate value of 3.

Can anyone help me determine how I can apply the COUNTIF function to my %
formual cell range.

Thanks.

Roy.

=COUNTIF(B4:B14,.15)
--
Brevity is the soul of wit.


"Cesar" wrote:

I have a column where I am calculating the growht rate of a number and
formatting the reults to show a % with no decimal places. And in this column
I am trying to find out which % values occurs the most.

In my column 1 is the raw numerical data.

In my column 2, I am calculating the % growth of the data in column 1. I
have formatted the column 2 data to display % with no decimal places. So my
column 2 data looks as follows

15%
15%
12%
17%
17%
19%
9%
15%
9%
11%

When I try to use the function COUNTIF(B4:B14, 15%), I get a value of 0.
When I hardcode the % values in the cell B4 - B14 and then apply the COUNTIF
formula I get an accurate value of 3.

Can anyone help me determine how I can apply the COUNTIF function to my %
formual cell range.

Thanks.

Roy.

Try rounding the results to two decimal places (and formatted as a percent).
(Instead of =148/1000, use =round(148/1000,2) ). Otherwise you'll get a
mismatch between the exact value in the countif and the approximate value
that you're displaying.
(If you need to retain the exact value, you could use two countif functions
to count the number of values within a range:
=countif(range,"=0.145")-countif(range,"=0.155") ).

"Cesar" wrote:

I have a column where I am calculating the growht rate of a number and
formatting the reults to show a % with no decimal places. And in this column
I am trying to find out which % values occurs the most.

In my column 1 is the raw numerical data.

In my column 2, I am calculating the % growth of the data in column 1. I
have formatted the column 2 data to display % with no decimal places. So my
column 2 data looks as follows

15%
15%
12%
17%
17%
19%
9%
15%
9%
11%

When I try to use the function COUNTIF(B4:B14, 15%), I get a value of 0.
When I hardcode the % values in the cell B4 - B14 and then apply the COUNTIF
formula I get an accurate value of 3.

Can anyone help me determine how I can apply the COUNTIF function to my %
formual cell range.

Thanks.

Roy.