IF/Greater Than question

[Skip77]

I am trying to build a function that will first use a LEFT command to isolate numbers from text in the output column (CSV file from database), than use the IF command (greater than or less than) to out put a YES or NO to a separate column depending on if the number is over 2 on the column containing the LEFT function. It seems that the IF command is being thrown off by the LEFT function as it is returning a YES for every cell, regardless of the number value. I have tried pasting the LEFT output in a third column using paste special-value, then convert to number to no avail. As I am sure you can tell, I am a "fledgling" excel user and would greatly appreciate any help. Is there a way to use an IF function on a cell containing a function (in this case the LEFT)? Better still, can the IF and LEFT be nested together?

Thank You,

Skip

 

[njimack]

Welcome to the board.

LEFT returns TEXT, not a number. To convert to a number, try adding 0, e.g
=LEFT(A1)+0

 

[T. Valko]

I am trying to build a function that will first use a LEFT command to isolate numbers from text in the output column (CSV file from database), than use the IF command (greater than or less than) to out put a YES or NO to a separate column depending on if the number is over 2 on the column containing the LEFT function. It seems that the IF command is being thrown off by the LEFT function as it is returning a YES for every cell, regardless of the number value. I have tried pasting the LEFT output in a third column using paste special-value, then convert to number to no avail. As I am sure you can tell, I am a "fledgling" excel user and would greatly appreciate any help. Is there a way to use an IF function on a cell containing a function (in this case the LEFT)? Better still, can the IF and LEFT be nested together?

Thank You,

Skip

It would have helped if you had posted some sample data.

My guess is that the LEFT function returns TEXT even if it looks like a numeric value and this is causing you problems. For example:

A1 = 123xxx

=LEFT(A1,3)

Retruns the TEXT STRING 123.

To convert it to a numeric value, the NUMBER 123...

=--LEFT(A1,3)

 

[Skip77]

Thank you for your help-unfortunately I am still getting the false returns.

I cant attach a file so I thought I would try to re-create what I am running into:

Column Name: Risk Assessment LEFT Function Greater than 2
2-JW 2 YES
3-KL 3 YES
1-SR 1 YES

The Risk Assessment column is data straight from the database. Unfortunately, we have to use a text box to record the assessment rather than a drop down that would not allow text.

The LEFT function column is where I inserted my LEFT command which reads:

=LEFT(Table1[[#This Row],[Risk Assessment]], 1)

The Greater Than 2 Column contains the IF statement, which reads:

=IF(Table1[[#This Row],[LEFT Function]]>2, "Yes", "No")

In all cases, regardless of cell content, this is returning a Yes

I tried to place two dashes in front of the LEFT:

=--LEFT((Table1[[#This Row],[Risk Assessment]], 1) and received a VALUE error

I am not sure how to add zero to the LEFT command. I tried

=LEFT(Table1[[#This Row],[Risk Assessment]], 1+0) which seemed to work but still out put a Yes in the Greater Than 2 column.

I checked on the Home tab and the data is output from the database as General. Interestingly, the Risk Assessment column (raw data) is output as General, but the IF function works (ie-it returns an accurate Yes/No based on value greater than 2) for all cells except those with text(for which it returns a Yes regardless of value-hence the reason for trying to remove text using LEFT).

I appreciate any help/time you can lend

 

[T. Valko]

Thank you for your help-unfortunately I am still getting the false returns.

I cant attach a file so I thought I would try to re-create what I am running into:

Column Name: Risk Assessment LEFT Function Greater than 2
2-JW 2 YES
3-KL 3 YES
1-SR 1 YES

The Risk Assessment column is data straight from the database. Unfortunately, we have to use a text box to record the assessment rather than a drop down that would not allow text.

The LEFT function column is where I inserted my LEFT command which reads:

=LEFT(Table1[[#This Row],[Risk Assessment]], 1)

The Greater Than 2 Column contains the IF statement, which reads:

=IF(Table1[[#This Row],[LEFT Function]]>2, "Yes", "No")

In all cases, regardless of cell content, this is returning a Yes

I tried to place two dashes in front of the LEFT:

=--LEFT((Table1[[#This Row],[Risk Assessment]], 1) and received a VALUE error

I am not sure how to add zero to the LEFT command. I tried

=LEFT(Table1[[#This Row],[Risk Assessment]], 1+0) which seemed to work but still out put a Yes in the Greater Than 2 column.

I checked on the Home tab and the data is output from the database as General. Interestingly, the Risk Assessment column (raw data) is output as General, but the IF function works (ie-it returns an accurate Yes/No based on value greater than 2) for all cells except those with text(for which it returns a Yes regardless of value-hence the reason for trying to remove text using LEFT).

I appreciate any help/time you can lend

Let's assume this formula is in cell A1:

=LEFT(Table1[[#This Row],[Risk Assessment]], 1)

What result do you get from this formula:

=CODE(A1)

 

[Skip77]

Using the CODE function returns a number between 48 and 51:

0 = 48
1 (with or without text) = 49
2 (with or without text) = 50
3 = 51

 

[T. Valko]

Using the CODE function returns a number between 48 and 51:

0 = 48
1 (with or without text) = 49
2 (with or without text) = 50
3 = 51

Ok, well, I don't know why you'd get an error. I thought that maybe the string started with a space character or some other whitespace character but if the CODE function returns those values then that's not the problem.

As you can see from this screencap that formula does work:

At this point I'm stumped!