Hello, I am using excel 2000 and 1

[sksaini]

Hello,

I am using excel 2000 and have a range of cells from A1:E1, which needs to be compared with values in A2:E2 and then count the result.

A1:E1 is- 21,24,18,19,25
A2:E2 is- 20,20,20,20,20

The formula I have is-

=COUNTIF(A1:E1,">=A2:E2&quot, which returns 3.

The above formula worked perfect.

Now the problem is- the values in A2:E2 change in between and the above formula does not work.

A1:E1 is- 21,24,18,19,25
A2:E2 is- 20,20,20,25,25

Can someone please help to count the number of times the values in A1:E1 are greater than the values in A2:E2?

Thanks a lot.

~S

 

[KenWright]

=SUM(--(A1:E1>=A2:E2))

Array entered - CTYRL+SHIFT+ENTER

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[sksaini]

Thanks Ken. It worked perfect.

Can you please explain the function of -- in the formula?

Thanks.

~S

 

[KenWright]

=SUM(--(A1:E1>=A2:E2)) sets up an array such as:-

A1=A2
B1=B2
C1=C2
D1=D2
E1=E2

This returns the following (Example only):-

TRUE
TRUE
FALSE
FALSE
TRUE

This you can coerce into numeric format, and in numeric format, TRUE = 1, and FALSE = 0.

To coerce into numeric format you simply use --, which means negative negative or minus minus which is the same as positive, and is simply a nice short way of telling Excel that you want to treat the result of the formula as numeric.

Regards
Ken................



----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Adnane]

Very interesting Ken.

Thanks and have a star

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[KenWright]

Thanks Adnane, much appreciated.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------