Which function find numbers not same.

  • Thread starter Thread starter Cactus [ÏÉÈËÇò]
  • Start date Start date
C

Cactus [ÏÉÈËÇò]

A range has a lot of numbers, I want to know how much not same numbers is
occur.

Without VBA code.
because VBA very slow.
 
Frank Kabel

That function so nice.
But what's it meaning?

COUNTIF(A1:A100,A1:A100) always zero.

And how use "+ - * /" for array?




Thanks
 
Hi
tryhighlighting this part int eh formula bar and press F9 to see the result
of this array :-)
 
=SUMPRODUCT({1,1,1,0.5,0.333333333333333,1,0.5;0.5,1,1,0.5,0.333333333333333
,1,0.5;1,0.5,0.333333333333333,1,1,1,1})

I see, the array sum is

occur * result = sum
1 x 1 = 1,
2 x 0.5 = 1,
3 x 0.333 = 1,
.....




Frank Kabel said:
Hi
tryhighlighting this part int eh formula bar and press F9 to see the result
of this array :-)



--
Regards
Frank Kabel
Frankfurt, Germany
Frank Kabel

That function so nice.
But what's it meaning?

COUNTIF(A1:A100,A1:A100) always zero.

And how use "+ - * /" for array?




Thanks
Hi
try
=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

--
Regards
Frank Kabel
Frankfurt, Germany

Cactus [ÏÉÈËÇò] wrote:
A range has a lot of numbers, I want to know how much not same
numbers is occur.

Without VBA code.
because VBA very slow.
Click to expand...
Click to expand...
Click to expand...
 
Hi
and as you see this adds a lot of '1' for each unique entry :-)

--
Regards
Frank Kabel
Frankfurt, Germany
=SUMPRODUCT({1,1,1,0.5,0.333333333333333,1,0.5;0.5,1,1,0.5,0.333333333333333
,1,0.5;1,0.5,0.333333333333333,1,1,1,1})

I see, the array sum is

occur * result = sum
1 x 1 = 1,
2 x 0.5 = 1,
3 x 0.333 = 1,
....




Frank Kabel said:
Hi
tryhighlighting this part int eh formula bar and press F9 to see the
result of this array :-)



--
Regards
Frank Kabel
Frankfurt, Germany
Frank Kabel

That function so nice.
But what's it meaning?

COUNTIF(A1:A100,A1:A100) always zero.

And how use "+ - * /" for array?




Thanks

Hi
try
=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

--
Regards
Frank Kabel
Frankfurt, Germany

Cactus [ÏÉÈËÇò] wrote:
A range has a lot of numbers, I want to know how much not same
numbers is occur.

Without VBA code.
because VBA very slow.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top