Logical Formula

**ross carr** · 09-26-2011, 08:08 PM

Hi,

I have what is probably a very easy problem to fix.

I have in cell Column A Numbers like the following

1002.00
1002.01
1002.10
1014.00
1029.00
1029.10

I want to put in a Logical formula in Column B to show me which cells in Column A have .01 at the end of the number.

I have entered the following formula in cell B1

=IF(A1="?????01","YES","NO")

However this is returning “NO” for every cell which I know to be incorrect as B2 should return “YES”.. I’ve checked to ensure too that all the numbers in Column A are in fact numbers. It is giving me a green tick in the corner of the formula cell B2 but no information on the potential error.

I also want to find cells in Column A which have .00 at the end of the number but I’m coming up with the same issue. I’m thinking for the .00 search though that maybe excel is not recognising the zero’s after the decimal point. I have put the following formual in cell C1. Is there a way for excel to recognise the zero's after the decimal point?

=IF(A1="?????00","YES","NO")

Does anyone have any idea why these logical formula is not working for me?

Your help is greatly appreciated.

**shg** · 09-26-2011, 08:17 PM

=round(mod(a1, 0.1), 6)=0.01

**teylyn** · 09-26-2011, 08:20 PM

Hello,

you can use the MOD() function

To show a Yes for .00 use
=IF(MOD(A1,1),"no","yes")

To show a Yes for .01 only, use
=IF(ROUND(MOD(A1,1),2)=0.01,"yes","no")

cheers,

**teylyn** · 09-26-2011, 08:21 PM

slow fingers ....

**ross carr** · 09-26-2011, 09:41 PM

Thanks so much teylyn.

I really appreciate you getting back to me. I tried clicking on the reputation icon on the top right of your posts but it didn't seem to do much.

Thanks again.

Cathal

**ross carr** · 09-26-2011, 09:45 PM

Thanks aswell Shg.

**ross carr** · 09-26-2011, 10:01 PM

One last query.

What was incorrect about the formula I first had? Should the wildcard (? or *) only be used for text or something?

=IF(A1="?????01","YES","NO")

Also - if I want to search for projects that have the first decimal point being 1-9 is there an easy way I can do this? eg. 1002.10, 1002.20 to 1002.90

Thanks again.

**ross carr** · 09-26-2011, 10:03 PM

And lastly (and I could probably find out how to do this myself so apologies) how do I show a problem as being solved? thanks again. Cathal

**Killer17** · 09-26-2011, 10:53 PM

And lastly (and I could probably find out how to do this myself so apologies) how do I show a problem as being solved? thanks again. Cathal

Click edit button on your first post,
Then click on Go advanced
Click the tab under title Select SOLVED

**ross carr** · 09-26-2011, 11:23 PM

Thanks Killer17.

Can anyone help with the following:

What was incorrect about the formula I first had? Should the wildcard (? or *) only be used for text or something?

=IF(A1="?????01","YES","NO")

Also - if I want to search for projects that have the first decimal point being 1-9 is there an easy way I can do this? eg. 1002.10, 1002.20 to 1002.90

Thanks again.

**teylyn** · 09-26-2011, 11:31 PM

Hello,

The comparison in your formula will always be False, because A1 is a number and it is compared with a text string. What you could do instead is

=IF(RIGHT(TEXT(A1,"0.00"),2)="01","yes","no")

The formula formats the number with two decimal points (regardless of what is actually showing in the cell) and converts it into a text string. Then the rightmost two characters are inspected and compared with the text "01"

This way, text is compared with text.

cheers,

**ross carr** · 09-27-2011, 12:50 AM

Thanks again teylyn.

You've been more than helpful.

If you have time I had one final query in relation to multiple searches

In my initial query I was searching for all the cells that had .01 as the two numbers after the decimal point.

I was wondering is there a quick way to search for cells with the number after the decimal point going from .10 to .90.
Eg. I want to find numbers like 1220.10, 1220.50,1220.90 but not 1220.00 and 1220.01.

Thanks again in advance. This is a fantastic source of information.

**teylyn** · 09-27-2011, 06:34 AM

Well, there are several ways to skin that cat.

If I were to do it, I'd probably add a column to the data table. With the number column A, I would use this formula in an additional column called "Decimals"

=RIGHT(TEXT(A2,"0.00"),2)

This will extract the decimal points and present them as text.

Next, I would build a pivot table, with the Decimals field in the rows and a count of the numbers in the values.

The pivot table can then be filtered to show only the Decimals I'm interested in. The filter options vary between Excel versions.

See attached. (Saved in XL 2003 format). I have set the filter not to display the 00 and the 70 decimals in the pivot table.

cheers,