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

#### andy72685

##### Board Regular
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.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
"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.

"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().

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?

Last edited:

Replies
6
Views
409
Replies
8
Views
347
Replies
2
Views
193
Replies
5
Views
333
Replies
4
Views
218

1,217,763
Messages
6,138,459
Members
450,139
Latest member
myexcel202424

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back