IF Function based on right value

  • Thread starter Thread starter MIKE F
  • Start date Start date
M

MIKE F

I am trying to write an if formula based on the following.

=IF(RIGHT($A1,3)="001",$D1,"0")

I have successfully written that formula for all values that end in the A
column with "001" thru "009" and "011" & "012". The problem I am having is
with "010". It always retunns 0 into my formula cell.
Please help!
 
Hi Mike
Your formula works ok, ie: =IF(RIGHT($A1,3)="010",$D1,"0"), just for
testing, delete with the delete button the number that finishes with 010 and
retype it by hand, maybe there is empty space before or after the number.Are
you importing this information from the internet ?. I can't see why it
does'nt work.
HTH
John
 
I had previously checked the formula to make aure that i was using "0" as
opposed to "O".
I even tried to apply the formula in a new workbook to make sure by copying
your formula and got the same results. It always returns "0" into my formula
cell. When i click on the reference cell (A1) when it ends in the "010"
value it drops that last value (0) from the formula bar. Could this be the
problem?
 
If it is dropped, Excel will evaluate ?01, iso 010. Iow, if your number was
1234010, and the last 0 is dropped, Excel will evaluate 401, and of course
return 0. That would depend on the number of digits in your number? Excel
handles 15 digits only. On the other hand, it would then do the same with
the other numbers as well? What is the exact number that you are evaulating?
 
i am referencing a 9 digit number. they are all formated as follows:
123456.xxx where xxx can be 001 thru 012. The formula works for all values
other than the "010". In the formula bar for the referenced "A" column cell
it drops the last zero even though the sheet is formatted and displays as
123456.010

Thanks.
 
sorry for ask, the number 123456.xxx shows decimals after de "."? or

if yes in the case of 123456.010 the right(a1,3) will be .01 and not 010.

that is the reason of the 0 as answer.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"MIKE F" escreveu:
 
And that's the solution to your problem. Decimals will only evaluate to .01,
and not to .010. I suggest you change your formula to read =IF(RIGHT(A1,2)=
"01", or else =IF(RIGHT(A1,3)=".01".
 
That took care of it. Thanks for the help.

Marcelo said:
sorry for ask, the number 123456.xxx shows decimals after de "."? or

if yes in the case of 123456.010 the right(a1,3) will be .01 and not 010.

that is the reason of the 0 as answer.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"MIKE F" escreveu:
Click to expand...
 
thanks for the feedback
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"MIKE F" escreveu:
 
Back
Top