IF Function based on right value
 

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!

IF Function based on right value
 

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

"MIKE F" wrote in message
...
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!

IF Function based on right value
 

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

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?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

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.

"Kassie" wrote:

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?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

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:

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.

"Kassie" wrote:

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?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

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

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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.

"Kassie" wrote:

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?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

That took care of it. Thanks for the help.

"Marcelo" wrote:

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:

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.

"Kassie" wrote:

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?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

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



"MIKE F" escreveu:

That took care of it. Thanks for the help.

"Marcelo" wrote:

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:

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.

"Kassie" wrote:

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?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail


"MIKE F" wrote:

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!

IF Function based on right value
 

try this

=IF(RIGHT(TEXT(A1,"0.000"),3)="010",D1,0)




On Jan 27, 12:00*am, MIKE F wrote:
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.

"Kassie" wrote:
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?

--
Hth

Kassie Kasselman
Change xxx to hotmail

"MIKE F" wrote:

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?
"Kassie" wrote:

=if(RIGHT(A1,3)="010",$D1,"0") works for me?

Sure you did not use an O, iso a 0?
--
Hth

Kassie Kasselman
Change xxx to hotmail

"MIKE F" wrote:

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!