Comparing multiple numbers - I am in agony

[Warhawk]

I have to compare 6 columns of numbers that have various integers
including 0s and sometimes null data. I need to know if all the data
is the same in the even there is data

Column A B C D E F G
25 25 25 25 25 True
25 100 25 0 100 25 False
0 0 0 0 0 True

I hope that my illustration makes sense. I know this is probably easy
for so many of you but I am just such an Excel Noob. I tried
=A=B=C=D=E=F but it always returns False.

Please help and I will help an old lady cross the road!

-Mark O'Brien


Government's view of the economy could be summed up in a few short phrases: If it moves, tax it. If it keeps moving, regulate it. And if it stops moving, subsidise it.
Ronald Reagan

 

[edessary]

Try this

=IF(COUNTIF(A1:E1,F1)=4,TRUE,FALSE)

 

[edessary]

Sorry that will not work, I took another look at your question and
noticed that I had read it wrong. Will any of the columns always have
a number?

 

[Warhawk]

On 9 Feb 2006 07:43:23 -0800, (e-mail address removed) wrote:

Unfortunately there are no columns that I can count on to have a
value. You got me thinkning about the count functions, like
counta or countnull...

Sorry that will not work, I took another look at your question and
noticed that I had read it wrong. Will any of the columns always have
a number?

Government's view of the economy could be summed up in a few short phrases: If it moves, tax it. If it keeps moving, regulate it. And if it stops moving, subsidise it.
Ronald Reagan

 

[Guest]

FOrtunately MIN() and MAX() ignore blanks:


In G1 put:
=(MIN(A1:F1)=MAX(A1:F1)) and copy down

 

[bplumhoff]

Hello Mark,

I suggest to take a UDF:

Type into G1:
=not_null_identical(A1:F1)
and copy down as far as necessary.

Insert a VBA module with this code:
Option Explicit

Function not_null_identical(r As Range) As Boolean
Dim b_empty As Boolean, rc As Range, dv As Double

b_empty = True 'start condition: range is empty

For Each rc In r
If Not IsEmpty(rc) Then
If b_empty Then
b_empty = False
dv = rc.Value
Else
If rc.Value <> dv Then
not_null_identical = False
Exit Function
End If
End If
End If
Next rc

not_null_identical = True

End Function

HTH,
Bernd

 

[Dave Peterson]

This formula:

=SUMPRODUCT((A1:F1<>"")/COUNTIF(A1:F1,A1:F1&""))
Will return 0 if all the cells look empty.

It'll return the count of unique values in that range (ignoring the cells that
look empty).

So

=if(SUMPRODUCT((A1:F1<>"")/COUNTIF(A1:F1,A1:F1&""))<=1,
"all the same or no data","mixture")

 

[Warhawk]

I want to thank you all! My problems are a thing of the past thanks
to your accute skills and generous natures!

I have to compare 6 columns of numbers that have various integers
including 0s and sometimes null data. I need to know if all the data
is the same in the even there is data

Column A B C D E F G
25 25 25 25 25 True
25 100 25 0 100 25 False
0 0 0 0 0 True

I hope that my illustration makes sense. I know this is probably easy
for so many of you but I am just such an Excel Noob. I tried
=A=B=C=D=E=F but it always returns False.

Please help and I will help an old lady cross the road!

-Mark O'Brien


Government's view of the economy could be summed up in a few short phrases: If it moves, tax it. If it keeps moving, regulate it. And if it stops moving, subsidise it.
Ronald Reagan

Government's view of the economy could be summed up in a few short phrases: If it moves, tax it. If it keeps moving, regulate it. And if it stops moving, subsidise it.
Ronald Reagan