Simple IF statement won't work.

[harg7769]

I'm having some problems with a very simple IF statement on a file of
data I imported from anopther application. The statement is based on
two columns, speed limit(column B), and actual speed (column C)

The statement is

=if(c5>b5,"Speeding", "")

Very simple, but it reports everything as False, ie returns a blank
cell. I had a look at the formats and the cells are all number cells.
I can do basic arithmetic on the cells without any problems, and I can
even get the statement to work on new columns within the worksheet.
Unfortuantely there are two many rows to re-type it all.

OK, found a solution, but I may as well finish this. In adjacent
columns, I typed = c5*1 and =b5*1, and then made the IF statement look
at them, and it works.

Any suggestions as to why this would work, and yet the original
figures don't?

regards

Andrew H

 

[bene]

It seems to happen a lot with data that is brought over from other
databases. The easiest solution that I've found is to do what you have
done (create a column multiplying by 0), then doing a copy, paste
special/values onto the original column.

 

[Jonathan Rynd]

It seems to happen a lot with data that is brought over from other
databases.

Another way to fix it is to select a blank cell, copy, select all the cells
that need to be fixed, Paste Special, Add.

 

[Ron Rosenfeld]

Any suggestions as to why this would work, and yet the original
figures don't?

The original speed limit figures are text.

You can frequently perform mathematical operations on TEXT as Excel will change
it to a number. But Excel doesn't do the conversions for logical tests.

In addition to other suggestions, you could also try:

=if((c5>(b5*1),"Speeding", "")

To check this out, see what you get with the formula =ISTEXT(B5)





--ron

 

[Anthos]

or you could try this on the column with the problem...

Sub FixRangeValues()
For Each c In Selection
c.Value = Format(c, "00")
Next
End Sub

It does happen during imports from other databases as mentioned above.