I have a workbook program that has been working beautifully, but I
found a bug and I do not know how to fix it.

I have a column that contains a formula - the formula uses a ranking
function to rank groups based on their score in a contest, which is
calcujlated from the numbers of several judges.

Whken the numbers from the judges are fully received, the column (I
will call it column R) determines if the group is in the 1st division,
or if not high enough, the 2nd division, etc.

The formula in Column S counts the number of goups in Division 1. I
had been doing this calculation with a simple comparison formula in
column S (If R=1,1,0). at the bottom of column S, I had been totalling
the number of groups by counting the number of 1's, and comparing
that to the number of 1's in other columns etc. This total is then
referenced in a VBA formula that formats a printout with the right
number of rows division 1, division 2, etc. for for a neat display for
the announcer to use at the conclusion of the contest.

It all works great EXCEPT I found today that if all of the judge's
scores are not received in total - if some are late coming in - the
ranking produces a non-numerical output in column R. Column S now
tries to compare Column R to 1 and since the variables are different,
it generates an error - a #VALUE! error. When the macro runs to try to
read the column totals, the whole program crashes.

Is there any way to structure a formula so that cells in column S can
look at the comparable cell in column R, and if the data in the cell in
R is not numeric, generate a Zero in column S - and if it is numeric,
do a math comparison on the number data - something like this;

If (ISNUMBER(R) if not a number, but instead is an error, generate
a zero in column S.
If it is a number, check the number to see if it is a 1 - then if so,
place a 1 in column S? There are several other things I do with the
number in column S, but going into it any deeper would just add
confusion, I am afraid.

I have been trying to figure out a way to do this, but I am at a loss.
Any help would be appreciated.

Many thanks in advance

=IF(ISNUMBER(R2),IF(R2=1,1,0),0)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
oups.com...
I have a workbook program that has been working beautifully, but I
found a bug and I do not know how to fix it.

I have a column that contains a formula - the formula uses a ranking
function to rank groups based on their score in a contest, which is
calcujlated from the numbers of several judges.

Whken the numbers from the judges are fully received, the column (I
will call it column R) determines if the group is in the 1st division,
or if not high enough, the 2nd division, etc.

The formula in Column S counts the number of goups in Division 1. I
had been doing this calculation with a simple comparison formula in
column S (If R=1,1,0). at the bottom of column S, I had been totalling
the number of groups by counting the number of 1's, and comparing
that to the number of 1's in other columns etc. This total is then
referenced in a VBA formula that formats a printout with the right
number of rows division 1, division 2, etc. for for a neat display for
the announcer to use at the conclusion of the contest.

It all works great EXCEPT I found today that if all of the judge's
scores are not received in total - if some are late coming in - the
ranking produces a non-numerical output in column R. Column S now
tries to compare Column R to 1 and since the variables are different,
it generates an error - a #VALUE! error. When the macro runs to try to
read the column totals, the whole program crashes.

Is there any way to structure a formula so that cells in column S can
look at the comparable cell in column R, and if the data in the cell in
R is not numeric, generate a Zero in column S - and if it is numeric,
do a math comparison on the number data - something like this;

If (ISNUMBER(R) if not a number, but instead is an error, generate
a zero in column S.
If it is a number, check the number to see if it is a 1 - then if so,
place a 1 in column S? There are several other things I do with the
number in column S, but going into it any deeper would just add
confusion, I am afraid.

I have been trying to figure out a way to do this, but I am at a loss.
Any help would be appreciated.

Many thanks in advance

Thanks very much. I eally appreciate the help. It works perfectly.
Best wishes from South Florida.


Bob Phillips wrote:
=IF(ISNUMBER(R2),IF(R2=1,1,0),0)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
oups.com...
I have a workbook program that has been working beautifully, but I
found a bug and I do not know how to fix it.

I have a column that contains a formula - the formula uses a ranking
function to rank groups based on their score in a contest, which is
calcujlated from the numbers of several judges.

Whken the numbers from the judges are fully received, the column (I
will call it column R) determines if the group is in the 1st division,
or if not high enough, the 2nd division, etc.

The formula in Column S counts the number of goups in Division 1. I
had been doing this calculation with a simple comparison formula in
column S (If R=1,1,0). at the bottom of column S, I had been totalling
the number of groups by counting the number of 1's, and comparing
that to the number of 1's in other columns etc. This total is then
referenced in a VBA formula that formats a printout with the right
number of rows division 1, division 2, etc. for for a neat display for
the announcer to use at the conclusion of the contest.

It all works great EXCEPT I found today that if all of the judge's
scores are not received in total - if some are late coming in - the
ranking produces a non-numerical output in column R. Column S now
tries to compare Column R to 1 and since the variables are different,
it generates an error - a #VALUE! error. When the macro runs to try to
read the column totals, the whole program crashes.

Is there any way to structure a formula so that cells in column S can
look at the comparable cell in column R, and if the data in the cell in
R is not numeric, generate a Zero in column S - and if it is numeric,
do a math comparison on the number data - something like this;

If (ISNUMBER(R) if not a number, but instead is an error, generate
a zero in column S.
If it is a number, check the number to see if it is a 1 - then if so,
place a 1 in column S? There are several other things I do with the
number in column S, but going into it any deeper would just add
confusion, I am afraid.

I have been trying to figure out a way to do this, but I am at a loss.
Any help would be appreciated.

Many thanks in advance