Test 2 numbers

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hello,

two number in (say) a1 & b1

In another cell If the numbers are different how can i get Excel to return
1 or if the numbers are the same 2


d
 
I should have mentioned that if you use DELTA and get a name error then

Tools|Addins and check the analysis toolpak

re-enter the formula

Mike
 
Hello,

two number in (say) a1 & b1

In another cell If the numbers are different how can i get Excel to return
1 or if the numbers are the same 2


d
Click to expand...

=1+(A1=B1)
--ron
 
Ron,

I had a mindset about changing false/true to 0 or 1 and then adding the 1
the OP wanted and failed to realise the +1 would do both jobs.

Thanks for pointing that out.

Mike
 
Hi,

One advantage in using COUNTIF is that it will work for Text, Numbers, or
Dates

One advantage in using =1+(A1=A2) or DELTA is that the cells don't need to
be adjactent, but both of these don't work for text.
 
One advantage in using =1+(A1=A2) or DELTA is that the cells don't need to
be adjactent, but both of these don't work for text.
Click to expand...

I'm not sure about your English. Maybe the difference between GB and the
colonies?

When you write "both of these don't work for text", some would take that to
mean that "neither" of these equations work for text.

I believe it would be useful to point out that the first equation works for
text or numbers, and the second only works for numbers.

And to be complete, the first is not case-sensitive when applied to text. A
case-sensitive variation, which would work for both text and numbers, would be:

=exact(a1,a2)+1

--ron
 
Shane,

You missed a major disadvantage of DELTA is that it can only take 2
arguments and would become more trouble than it's worth with the fiddling
about you'd have to do when trying to evaluate more than 2. The only reason I
offered it was because th OP specified 2 numbers.

We'll disagree about whether =(A1=B1)+1 can handle text, I remain of the
opinion it can :)

Mike
 
Hi all

We don't have to disagree on whether the first one will work with text, I
was a little fast on the draw, it does work for text.

And I like the additional point about the limits of DELTA. But I was glad
to see someone find a use for DELTA, I sure there are others but it usually
seems a little longer that other solutions.

Cheers,
Shane Devenshire
 
Back
Top