Excel Quick Question

[paulrockliffe]

I'm doing something very complicated. As a result I have a column that contains numbers that are either x.0 or x.5 I want to Count the number of cells that contain numbers that are in one of these formats. I know about COUNTIF, but I don't know how to express the criteria I'm after.

Anyone know?

[ewenm]

Edit: I'd have a second column with =IF(MOD(A1,0.5)=0,0,1) and then sum that column

Edited by ewenm on Wednesday 31st March 16:47

[paulrockliffe]

Hello Ewan, cheers for that, can you just give me a quick run-down of what that formula does exactly? This could be the difference between being able to run tonight or not!

[ewenm]

paulrockliffe said:

Hello Ewan, cheers for that, can you just give me a quick run-down of what that formula does exactly? This could be the difference between being able to run tonight or not!

Changed it.

The MOD function allows you to ask if a value is divisible by another value exactly.

So MOD(A1,0.5) returns 0 if A1 is a multiple of 0.5.

The IF statement then assigns 0 to all values that are exactly divisible by 0.5 and 1 to any that aren't. I'd then sum that second column.

[Liszt]

except 1/0.5 =2

[paulrockliffe]

Ok, but my other column has numbers in it that are either x.0 or x.5, both of which would always give multiples of 0.5?

[paulrockliffe]

MOD(A1,0.2) does work though

[ewenm]

Liszt said:

except 1/0.5 =2

Yes, but MOD(1,0.5) = 0

paulrockliffe said:

MOD(A1,0.2) does work though

This or 1.

Anyway, I can't read. You want to know how many x.0 you have and how many x.5?

If so change the IF to =IF(MOD(A1,1)=0,0,1), so seeing if the cell is exactly divisible by 1. Summing that second column will give you the number of x.5.

Edited by ewenm on Wednesday 31st March 16:56

[Liszt]

If your column of results is in C have
D1 as =C1-TRUNC(C1,0)
E1 as =COUNTIF(E1,0)
F1 as =COUNTIF(E1,0.5)

[paulrockliffe]

Yeah, I just did MOD(A1,0.2) as that was the first number I could think of that 1 was divisible by and 0.5 wasn't. Works perfectly, just need to translate my list of numbers into the correct decision on whether to SUM or not.