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

andy72685

Board Regular
Joined
Oct 20, 2016
Messages
72
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
"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

Well-known Member
Joined
Mar 2, 2014
Messages
3,058
Office Version
  1. 2010
Platform
  1. Windows
"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

Board Regular
Joined
Oct 20, 2016
Messages
72
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:

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,358
Members
418,498
Latest member
nattynat

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top