Inconsistent values - Quattro Pro 8

I am a teacher who has been using the @IF, @COUNTIF and @SUM functions to 
convert from letter grades, do an average and reconvert to a letter grade 
for report card purposes.  In the reconversion process, using the @IF 
function, I am using the <> signs plus #AND# to set parameters.  It seems

that rows with the same numerical average can have inconsistent results when 
reconverting to a letter grade.  The formula I am using is as follows: 
@IF(AC1>90,"A+",@IF(AC1>83#AND#AC1<90,"A",@IF(AC1&g
t;79#AND#AC1<84,"A-",@IF(AC1>76#AND#AC1<80,"B+",@IF
(AC1>73#AND#AC1<77,"B",@IF(AC1>69#AND#AC1<74,"B-"
;,@IF(AC1>66#AND#AC1<70,"C+",@IF(AC1>63#AND#AC1<67,"C
",@IF(AC1>59#AND#AC1<64,"C-",@IF(AC1>56#AND#AC1<60,&q
uot;D+",@IF(AC1>53#AND#AC1<57,"D",@IF(AC1>49#AND#AC1<
54,"D-","R")))))))))))). 
If the numerical average is 73, in one instance Quattro Pro 8 will convert 
the number to a "B" and in the other instance it will convert the same

number to a "B-" which it is supposed to do.  I have tried clearing
the cell 
and re-entering the formula, but I still get the same result.  I originally 
thought that the <> signs meant less than and greater than, but then I 
wondered if they maybe meant less and equal to and greater than and equal 
to.  The fact that two results come from the same number seem to disprove 
that theory.

Any suggestions?

Thanks 

Such deep nesting of @IFs is often fraught with troubles and headaches. 
  For your type of application (grades), using @VLOOKUP in conjunction 
with a grade table is a much simpler method, i.e.:

Grade table (column A lists the minimum score required for the grade):
    A  B
1  0  R
2  50 D-
3  54 D
....
13 91 A+

Grade formula:  @VLOOKUP(AC1,$A$1..$B$13,1)

The advantage of a table lookup function is not only the simplicity of 
design (and maintenance), but also the relative ease with which certain 
logic errors that can easily creep into a nested @IF structure can be 
prevented.  It requires a bit of scrutiny, for example, to discover that 
your @IF formula does not properly handle the value of 90 because it 
lets it "drop through" the logic grid to come out as an "R"
(instead of 
an "A").

Now to your primary question:  inconsistency of results from your 
formula.  Unless you round your numerical averages to integers, they 
will most likely be values with a fractional component.  If your 
relevant cells are formatted to display only the integer portion of the 
average, then what APPEARS to be a "73" could actually be 73.1 or 72.8

or 73 flat.  Thus, any fractional number between 73.0 and 73.49 would, 
according to the logic in your @IF formula, be worth a "B" -- thereby

creating the APPEARANCE of an error when in fact the output is 
consistent with your formula logic.  Again, what creates the potential 
problem is the degree of accuracy required in a nested @IF structure, 
which in your case could be better managed with @VLOOKUP.  You just need 
to enter the appropriate MINIMUM value for each respective grade in the 
lookup table -- all fractional scores will simply be handled implicitly.

Cheers,
Uli


mn wrote:
> I am a teacher who has been using the @IF, @COUNTIF and @SUM functions to 
> convert from letter grades, do an average and reconvert to a letter grade 
> for report card purposes.  In the reconversion process, using the @IF 
> function, I am using the <> signs plus #AND# to set parameters.  It
seems 
> that rows with the same numerical average can have inconsistent results
when 
> reconverting to a letter grade.  The formula I am using is as follows: 
>
@IF(AC1>90,"A+",@IF(AC1>83#AND#AC1<90,"A",@IF(AC1&g
t;79#AND#AC1<84,"A-",@IF(AC1>76#AND#AC1<80,"B+",@IF
(AC1>73#AND#AC1<77,"B",@IF(AC1>69#AND#AC1<74,"B-"
;,@IF(AC1>66#AND#AC1<70,"C+",@IF(AC1>63#AND#AC1<67,"C
",@IF(AC1>59#AND#AC1<64,"C-",@IF(AC1>56#AND#AC1<60,&q
uot;D+",@IF(AC1>53#AND#AC1<57,"D",@IF(AC1>49#AND#AC1<
54,"D-","R")))))))))))). 
> If the numerical average is 73, in one instance Quattro Pro 8 will convert

> the number to a "B" and in the other instance it will convert the
same 
> number to a "B-" which it is supposed to do.  I have tried
clearing the cell 
> and re-entering the formula, but I still get the same result.  I originally

> thought that the <> signs meant less than and greater than, but then
I 
> wondered if they maybe meant less and equal to and greater than and equal 
> to.  The fact that two results come from the same number seem to disprove 
> that theory.
> 
> Any suggestions?
> 
> Thanks

Likely, when you are calculating your average, depending on the 
divisor, you may have some a result that could be something like 
73.00000002 or some thing like that. That would be enough to throw it 
into the next grade group as you have defined the formula.

Also, your formula doesn't provide for the cases of when the grade 
equals the "border" between two grades, e.g. a grade of 90 is what? 
Your formula says that it has to be > than 90 to be an A+, but less 
than a 90 is A, but the value of 90 is undefined.

I would suggest instead of this long formula, you look at using a 
VLOOKUP table to get your letter grades. A partial table example:
NumGradeLetGrade
76 B+
79 A-
84 A
90 A+

Flesh it out to have all your grades, select the entire table and name 
it "grade_table", then where you want your letter grade, your formula

would be @VLOOKUP(AC1,grade_table,1)

Note that the table needs to have the lowest value at the top of the 
first column and then ascend to the highest. A grade of 92 will return 
an A+, as will a grade of 90. A grade of 83.784 will return a grade of 
A-

-- 
Jim Latham

Well, it is simpler once you realize that data manipulation and data 
display are separate issues.  When you "set the column characteristics 
as fixed with no decimal places" (as I suspected), you're merely setting 
the DISPLAY property, which has no affect on the underlying numerical 
value.  If you move to the target cell and press F2 followed by F9 
(without pressing ENTER), you will see the actual numerical value in the 
data entry line (above the column letters).  More importantly, your @IF 
formula does not properly handle the fractional intervals between 
grades, because it assumes you're only dealing with integers, which is 
more the exception than the rule when dealing with averages.

Cheers,
Uli


mn wrote:
> Hi Uli.
> 
> Uli,
> Thanks for your suggestion, but trying that way and trying the way the help

> screen says I should do not work no matter how I work with the formula. 
> Obviously I am doing something wrong.  In my other approach, using the @IF

> function, I set the column characteristics as "fixed" with no
decimal 
> places.  Because I get the inconsistent entries, I have to assume that
doing 
> this does not instruct the software to round or calculate without using 
> decimals.  It seems nothing is simple.
> 
> I appreciate your help.
> Mark
> "Uli" <nojunkmail@spamless.com> wrote in message
news:45e8f3ad_2@cnews...
>> Such deep nesting of @IFs is often fraught with troubles and headaches.

>> For your type of application (grades), using @VLOOKUP in conjunction
with 
>> a grade table is a much simpler method, i.e.:
>>
>> Grade table (column A lists the minimum score required for the grade):
>>    A  B
>> 1  0  R
>> 2  50 D-
>> 3  54 D
>> ....
>> 13 91 A+
>>
>> Grade formula:  @VLOOKUP(AC1,$A$1..$B$13,1)
>>
>> The advantage of a table lookup function is not only the simplicity of

>> design (and maintenance), but also the relative ease with which certain

>> logic errors that can easily creep into a nested @IF structure can be 
>> prevented.  It requires a bit of scrutiny, for example, to discover
that 
>> your @IF formula does not properly handle the value of 90 because it
lets 
>> it "drop through" the logic grid to come out as an
"R" (instead of an 
>> "A").
>>
>> Now to your primary question:  inconsistency of results from your
formula. 
>> Unless you round your numerical averages to integers, they will most 
>> likely be values with a fractional component.  If your relevant cells
are 
>> formatted to display only the integer portion of the average, then what

>> APPEARS to be a "73" could actually be 73.1 or 72.8 or 73
flat.  Thus, any 
>> fractional number between 73.0 and 73.49 would, according to the logic
in 
>> your @IF formula, be worth a "B" -- thereby creating the
APPEARANCE of an 
>> error when in fact the output is consistent with your formula logic. 
>> Again, what creates the potential problem is the degree of accuracy 
>> required in a nested @IF structure, which in your case could be better

>> managed with @VLOOKUP.  You just need to enter the appropriate MINIMUM

>> value for each respective grade in the lookup table -- all fractional 
>> scores will simply be handled implicitly.
>>
>> Cheers,
>> Uli
>>
>>
>> mn wrote:
>>> I am a teacher who has been using the @IF, @COUNTIF and @SUM
functions to 
>>> convert from letter grades, do an average and reconvert to a letter
grade 
>>> for report card purposes.  In the reconversion process, using the
@IF 
>>> function, I am using the <> signs plus #AND# to set
parameters.  It seems 
>>> that rows with the same numerical average can have inconsistent
results 
>>> when reconverting to a letter grade.  The formula I am using is as

>>> follows: 
>>>
@IF(AC1>90,"A+",@IF(AC1>83#AND#AC1<90,"A",@IF(AC1&g
t;79#AND#AC1<84,"A-",@IF(AC1>76#AND#AC1<80,"B+",@IF
(AC1>73#AND#AC1<77,"B",@IF(AC1>69#AND#AC1<74,"B-"
;,@IF(AC1>66#AND#AC1<70,"C+",@IF(AC1>63#AND#AC1<67,"C
",@IF(AC1>59#AND#AC1<64,"C-",@IF(AC1>56#AND#AC1<60,&q
uot;D+",@IF(AC1>53#AND#AC1<57,"D",@IF(AC1>49#AND#AC1<
54,"D-","R")))))))))))). 
>>> If the numerical average is 73, in one instance Quattro Pro 8 will

>>> convert the number to a "B" and in the other instance it
will convert the 
>>> same number to a "B-" which it is supposed to do.  I have
tried clearing 
>>> the cell and re-entering the formula, but I still get the same
result.  I 
>>> originally thought that the <> signs meant less than and
greater than, 
>>> but then I wondered if they maybe meant less and equal to and
greater 
>>> than and equal to.  The fact that two results come from the same
number 
>>> seem to disprove that theory.
>>>
>>> Any suggestions?
>>>
>>> Thanks
> 
> 
> 

Jim,
Thanks for your suggestion, but trying that way and trying the way the help 
screen says I should do not work no matter how I work with the formula. 
Obviously I am doing something wrong.  In my other approach, using the @IF 
function, I set the column characteristics as "fixed" with no decimal

places.  Because I get the inconsistent entries, I have to assume that doing 
this does not instruct the software to round or calculate without using 
decimals.  It seems nothing is simple.

I appreciate your help.
Mark

"Jim Latham" <nospam@nospam.com> wrote in message 
news:VA.000032a4.3e91ec41@nospam.com...
>
> Likely, when you are calculating your average, depending on the
> divisor, you may have some a result that could be something like
> 73.00000002 or some thing like that. That would be enough to throw it
> into the next grade group as you have defined the formula.
>
> Also, your formula doesn't provide for the cases of when the grade
> equals the "border" between two grades, e.g. a grade of 90 is
what?
> Your formula says that it has to be > than 90 to be an A+, but less
> than a 90 is A, but the value of 90 is undefined.
>
> I would suggest instead of this long formula, you look at using a
> VLOOKUP table to get your letter grades. A partial table example:
> NumGradeLetGrade
> 76 B+
> 79 A-
> 84 A
> 90 A+
>
> Flesh it out to have all your grades, select the entire table and name
> it "grade_table", then where you want your letter grade, your
formula
> would be @VLOOKUP(AC1,grade_table,1)
>
> Note that the table needs to have the lowest value at the top of the
> first column and then ascend to the highest. A grade of 92 will return
> an A+, as will a grade of 90. A grade of 83.784 will return a grade of
> A-
>
> -- 
> Jim Latham
>
>