When comparing +2 to +2

[Lars]

Hi guys,

I have a sheet form making a deviation table for a magnetic compass.

Column D holds single digit values of deviation. I need not only
negative numbers to have a sign, but also the positive ones, like +2.

So I have formatted those cells as custom +0;-0;0 wich does the job
fine. Furthe into the sheet I have a large grid of 19*36 cells where I
want a little x in all cells that are equal to the ones in the D
column.

It works fine with negative numbers, and with zeros. But not with the
positive ones.

The formula is simply =IF($D5=S$2;"x";"") and onwards. In this
particular case the S2 cell has the value +5. I have tried it
formatted as text and as a similar custom as my D cells.

Why would that not work?

Lars
Stockholm

 

[Dave Peterson]

First, are you sure that both cells are numeric--not text that look like
numbers?

Second, are you sure that the values are what you expect? Since you're
formatting the cells with no decimal places, maybe you're comparing 2.1 to 2.2.

=if(int($d5)=int(s$2);"x";"")

May work.

or something like:
=if(abs($d5-s$2)<0.0001;"x";"")

(if the difference between the numbers is pretty small, then treat them as
equal.

 

[Lars]

First, are you sure that both cells are numeric--not text that look like
numbers?

Yes numeric, like I said;

.................

Second, are you sure that the values are what you expect? Since you're
formatting the cells with no decimal places, maybe you're comparing 2.1 to 2.2.

=if(int($d5)=int(s$2);"x";"")

No they are integers. The values are arrived at in another sheet,
first as decimal numbers and then converted in another coulumn to
single digit integers. These are the ones I link into my D column.

And anyway, I have 36 such numbers, arrived at in the same manner.
Most of them are negatives, and they work fine.

Lars
Stockholm

 

[Dave Peterson]

And if you use:

=count(d5;s2)

What is returned?

 

[Lars]

And if you use:

=count(d5;s2)

What is returned?

1

Lars
Stockholm

 

[Dave Peterson]

That means one of them is not a number.

=isnumber(d5)
and
=isnumber(s2)

will help you find the one that's wrong.

Then reformat that cell as General. And reenter the value.

 

[Lars]

=isnumber(d5)
and
=isnumber(s2)

will help you find the one that's wrong.

Then reformat that cell as General. And reenter the value.

Actually both of them were formatted as custom +0;-0;0;

But I reformatted them to General and then back again to custom, after
wich I agian filled in their contents. Now it works beautifully.

Thank you!


Lars
Stockholm

 

[Dave Peterson]

You probably could have gotten by with just reentering the values. Those number
formats would have worked fine.

 

[srpavar]

Can u please tell me how to post a query, i have done it before now i
forgot? Please help me.

 

[Gord Dibben]

You just posted a query.


Gord Dibben MS Excel MVP