Calculation problem?
 

I have a spreadsheet that uses the following calculation:-

'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100'

The problem i have is that while H2 is constant, H85 can change depending on
how many rows their are?
I am trying to use a terminating value such a 'end' so it knows that when i
reach 'end' it uses the previous row reference to replace what is currently
'H85'.
Is it possible?

Hope this makes sense.

Unfortunatley i've tried that, it distorts the figures as it tries to
calculate the % for the whole table not just to were the data ends.

"Bob Phillips" wrote:

Why not just use

=(COUNTIF(Calc!H2:H65536,"<15"))/(COUNTIF(Calc!H2:H65536,"0"))*100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dave" wrote in message
...
I have a spreadsheet that uses the following calculation:-

'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100'

The problem i have is that while H2 is constant, H85 can change depending
on
how many rows their are?
I am trying to use a terminating value such a 'end' so it knows that when
i
reach 'end' it uses the previous row reference to replace what is
currently
'H85'.
Is it possible?

Hope this makes sense.

Hi,

=(COUNTIF(Calc!H:H,"<15"))/(COUNTIF(Calc!H:H,"0"))*100

I have a question about your formula. It calculates the percentage of the
number of cells that are less than 15 (this INCLUDES those that are negative
as well as those that are equal to zero) relative to the number of cells that
are greater than zero (this EXCLUDES those that are negative or zero). So
you can end up with greater than 100% for your result, if there are a lot of
negative or zero values (unless that's how you wnt the result or ALL values
are greater than zero; in the latter case, the logical function in the
denominator is unnecessary - you can just use "COUNT(H:H)")

Of course you know your problem better than others. Nonetheless, how about
the following formula?

=((COUNTIF(Calc!H:H,"<"&15)-COUNTIF(Calc!H:H,"<="&0))/COUNTIF(Calc!H:H,""&0))*100

This will calculate the percentage of values that are 0 AND <15 relative to
those that are 0 (so all zero and negative values are totally ignored).

Regards,
B. R. Ramachandran

"dave" wrote:

I have a spreadsheet that uses the following calculation:-

'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100'

The problem i have is that while H2 is constant, H85 can change depending on
how many rows their are?
I am trying to use a terminating value such a 'end' so it knows that when i
reach 'end' it uses the previous row reference to replace what is currently
'H85'.
Is it possible?

Hope this makes sense.

Why not just use

=(COUNTIF(Calc!H2:H65536,"<15"))/(COUNTIF(Calc!H2:H65536,"0"))*100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dave" wrote in message
...
I have a spreadsheet that uses the following calculation:-

'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100'

The problem i have is that while H2 is constant, H85 can change depending
on
how many rows their are?
I am trying to use a terminating value such a 'end' so it knows that when
i
reach 'end' it uses the previous row reference to replace what is
currently
'H85'.
Is it possible?

Hope this makes sense.

That's odd but it doesn't for me. Do you have zeroes or some formula in
there?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dave" wrote in message
...
Unfortunatley i've tried that, it distorts the figures as it tries to
calculate the % for the whole table not just to were the data ends.

"Bob Phillips" wrote:

Why not just use

=(COUNTIF(Calc!H2:H65536,"<15"))/(COUNTIF(Calc!H2:H65536,"0"))*100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dave" wrote in message
...
I have a spreadsheet that uses the following calculation:-

'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100'

The problem i have is that while H2 is constant, H85 can change
depending
on
how many rows their are?
I am trying to use a terminating value such a 'end' so it knows that
when
i
reach 'end' it uses the previous row reference to replace what is
currently
'H85'.
Is it possible?

Hope this makes sense.

Cheers for that, i'll give it ago and see what happens. Thanks for the help

"B. R.Ramachandran" wrote:

Hi,

=(COUNTIF(Calc!H:H,"<15"))/(COUNTIF(Calc!H:H,"0"))*100

I have a question about your formula. It calculates the percentage of the
number of cells that are less than 15 (this INCLUDES those that are negative
as well as those that are equal to zero) relative to the number of cells that
are greater than zero (this EXCLUDES those that are negative or zero). So
you can end up with greater than 100% for your result, if there are a lot of
negative or zero values (unless that's how you wnt the result or ALL values
are greater than zero; in the latter case, the logical function in the
denominator is unnecessary - you can just use "COUNT(H:H)")

Of course you know your problem better than others. Nonetheless, how about
the following formula?

=((COUNTIF(Calc!H:H,"<"&15)-COUNTIF(Calc!H:H,"<="&0))/COUNTIF(Calc!H:H,""&0))*100

This will calculate the percentage of values that are 0 AND <15 relative to
those that are 0 (so all zero and negative values are totally ignored).

Regards,
B. R. Ramachandran

"dave" wrote:

I have a spreadsheet that uses the following calculation:-

'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100'

The problem i have is that while H2 is constant, H85 can change depending on
how many rows their are?
I am trying to use a terminating value such a 'end' so it knows that when i
reach 'end' it uses the previous row reference to replace what is currently
'H85'.
Is it possible?

Hope this makes sense.