Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function to use? Trying to ref a cell based on another. | New Users to Excel | |||
Add certain cells based on a function | Excel Worksheet Functions | |||
Sum function based on format | Excel Worksheet Functions | |||
function based on result of same function | Excel Discussion (Misc queries) | |||
Old, LOTUS 1-2-3 based function | Excel Worksheet Functions |