Why would an 'If' statement evaluate a letter as greater than a number?

[andy72685]

I have a sheet that I'm working on in my excel file that cannot have blank cells. If there is ever a point at which there is no viable data to enter, I place the letter 'A' in the cell. I use A instead of zero in order to keep the system averaging correctly. I also have a number of 'Helper Columns' that compare columns C&D, columns E&F, etc. In those columns I have the following formulas

1st column:
=IF(C2 < D2,C2,0)

2nd column
=IF(C2 = D2,C2,0)

3rd column
=IF(C2 > D2,C2,0)

This repeats five more times in columns, and travels down to row 11, to compare C11 and D11, etc. If the letter A is in cell C5, and the cell to which it is being compared has a value of 80, the helper columns will place 0 in the less than and equal to, but will place A in the greater than column. If A is present in a cell, it should either not be able to compare (apples and oranges) or it should say this is not greater than that and should put 0 in the cell. Is there a way to confirm that whenever A is entered, 0 will be the result of the if?

It is probably worth noting that the formula in D (F, H, J, L, AND N) to which it would be compared is '=ROUND(AVERAGE(SDRUFCS),0)'.

Any help would be appreciated.
Thank you.

 

[SpillerBD]

"A" will evaluate to its code value of 65.
If you want to compare the numeric value of "A" which should be zero since it is not a number, use VALUE() to get that relationship.

 

[joeu2004]

"A" will evaluate to its code value of 65. If you want to compare the numeric value of "A" which should be zero since it is not a number, use VALUE() to get that relationship.

It has nothing to do with the ASCII code. Excel treats all text as greater than any numeric value.

And using VALUE(C2) returns a #VALUE error if C2 is text like "A".

Instead, Andy might use N(C2) or SUM(C2).

IMHO, the N() function is better to use, since that its purpose. But it used to be difficult to find the help page for N().

 

[andy72685]

I just looked up the N() function. If I understand what I saw there and here, I would need to change the formula to

=IF(N(C2) < D2,C2,0)

Is that correct, or do I need to place the function elsewhere also?