Logical test IF a decimal was typed in a cell
 

I'm wondering if there is a way to create a logical test to find out if a
decimal point was typed in a cell.

"16" would be false.
"16." would be true.

=if(a1 contains a decimal,a1,else use other formula)

Answer: Logical test IF a decimal was typed in a cell
 

1. Select the cell where you want to apply the formula.
2. Type the following formula in the formula bar:
   
   
   Formula:

   ---

   =IF(FIND(".",A1)0,[b]A1[/b],"Use other formula") 


   ---

3. Press Enter.

Explanation:

The FIND function searches for a specific character or text within a cell and returns the starting position of that character or text. In this case, we are searching for a decimal point in cell A1.

If the FIND function returns a value greater than 0, it means that a decimal point was found in cell A1. In that case, the IF function returns the value of cell A1 (which contains the decimal point) in bold.

If the FIND function returns a value of 0, it means that a decimal point was not found in cell A1. In that case, the IF function returns the text "Use other formula" (you can replace this with any other formula or text that you want to use).

Note: This formula assumes that the cell contains only one decimal point. If the cell contains multiple decimal points, the formula will return an error.

Logical test IF a decimal was typed in a cell
 

One try: =IF(ISNUMBER(SEARCH(".",A1)),"do this","do that"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jheath.bc" wrote:
I'm wondering if there is a way to create a logical test to find out if a
decimal point was typed in a cell.

"16" would be false.
"16." would be true.

=if(a1 contains a decimal,a1,else use other formula)

Logical test IF a decimal was typed in a cell
 

Looks promising, but it didn't work. This was the original formula I was
trying to test for. It may seem odd, but it allows me to enter
feet-Inches-Sixteenths in the following format FFIISS. 160112 returns the
value of 16' 1-3/4". It is an entry format used in the US wood truss
industry.
=INT(B4/10000)+(MOD(INT(B4/100),100)+MOD(B4,100)/16)/12

The entry format doesn't use a decimal, but it would be nice if I could
enter "16." for 16 feet instead of 160000. Hence the test for a decimal point.

Here is the formula including your suggestion. Perhaps I didn't get it quite
right.
=IF(ISNUMBER(SEARCH(".",B3)),B3,INT(B3/10000)+(MOD(INT(B3/100),100)+MOD(B3,100)/16)/12)
Thanks!

"Max" wrote:

One try: =IF(ISNUMBER(SEARCH(".",A1)),"do this","do that"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jheath.bc" wrote:
I'm wondering if there is a way to create a logical test to find out if a
decimal point was typed in a cell.

"16" would be false.
"16." would be true.

=if(a1 contains a decimal,a1,else use other formula)

Logical test IF a decimal was typed in a cell
 

Maybe you could enter say, an "f" instead as an identifier?
So if you enter in B3: 16f
think you could try this in say, C3:
=IF(ISNUMBER(SEARCH("f",B3)),LEFT(B3,SEARCH("f",B3 )-1)+0,INT(B3/10000)+(MOD(INT(B3/100),100)+MOD(B3,100)/16)/12)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jheath.bc" wrote:
Looks promising, but it didn't work. This was the original formula I was
trying to test for. It may seem odd, but it allows me to enter
feet-Inches-Sixteenths in the following format FFIISS. 160112 returns the
value of 16' 1-3/4". It is an entry format used in the US wood truss
industry.
=INT(B4/10000)+(MOD(INT(B4/100),100)+MOD(B4,100)/16)/12

The entry format doesn't use a decimal, but it would be nice if I could
enter "16." for 16 feet instead of 160000. Hence the test for a decimal point.

Here is the formula including your suggestion. Perhaps I didn't get it quite
right.
=IF(ISNUMBER(SEARCH(".",B3)),B3,INT(B3/10000)+(MOD(INT(B3/100),100)+MOD(B3,100)/16)/12)
Thanks!

Logical test IF a decimal was typed in a cell
 

On Wed, 25 Jun 2008 16:10:00 -0700, jheath.bc
wrote:

The entry format doesn't use a decimal, but it would be nice if I could
enter "16." for 16 feet instead of 160000. Hence the test for a decimal point.

The only way I know of to differentiate 16 and 16. during data entry would be
if the 16. were entered as text. One way to do that would be to precede the
entry with a single quote, so you would enter '16.

I'm not sure if this would be enough quicker and error-free compared with
entering 160000 to make it worthwhile.
--ron

Logical test IF a decimal was typed in a cell
 

If the cell contains a number, it stores the same whether you type in 16 or
16. or 16.0 or 16.000000

If you want to distinguish what you've typed in, it will have to be text,
not a number.
--
David Biddulph

"jheath.bc" wrote in message
...
I'm wondering if there is a way to create a logical test to find out if a
decimal point was typed in a cell.

"16" would be false.
"16." would be true.

=if(a1 contains a decimal,a1,else use other formula)

Logical test IF a decimal was typed in a cell
 

Thanks Max! It works for me, and preserves the numeric value for further
calculation. I did change the letter to "d" as in decimal ;)
I appreciate your help, and the other replies as well.
Thanks
Jon Heath

"Max" wrote:

Maybe you could enter say, an "f" instead as an identifier?
So if you enter in B3: 16f
think you could try this in say, C3:
=IF(ISNUMBER(SEARCH("f",B3)),LEFT(B3,SEARCH("f",B3 )-1)+0,INT(B3/10000)+(MOD(INT(B3/100),100)+MOD(B3,100)/16)/12)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jheath.bc" wrote:
Looks promising, but it didn't work. This was the original formula I was
trying to test for. It may seem odd, but it allows me to enter
feet-Inches-Sixteenths in the following format FFIISS. 160112 returns the
value of 16' 1-3/4". It is an entry format used in the US wood truss
industry.
=INT(B4/10000)+(MOD(INT(B4/100),100)+MOD(B4,100)/16)/12

The entry format doesn't use a decimal, but it would be nice if I could
enter "16." for 16 feet instead of 160000. Hence the test for a decimal point.

Here is the formula including your suggestion. Perhaps I didn't get it quite
right.
=IF(ISNUMBER(SEARCH(".",B3)),B3,INT(B3/10000)+(MOD(INT(B3/100),100)+MOD(B3,100)/16)/12)
Thanks!

Logical test IF a decimal was typed in a cell
 

Welcome, Jon. Thanks for feeding back here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jheath.bc" wrote in message
...
Thanks Max! It works for me, and preserves the numeric value for further
calculation. I did change the letter to "d" as in decimal ;)
I appreciate your help, and the other replies as well.
Thanks
Jon Heath