"--" (was "DCOUNT Unique Values")

[Wilba]

Magic! Thanks a bunch.

Would you mind talking me through this a little?

Let me see what I can understand. You're comparing the value in each A
cell with the two reference dates.

The "--" looks like a double negation - what is it doing?

OK, I'll answer my own question. It looks like the "--" is doing the
equivalent of DATEVALUE. In the real thing I'm comparing dates directly so I
don't need it anyway. But I can't find any documentation on the "--" ... any
clues?

Does the "*" operator give you a logical "and"?

It's just multiplying 1s and 0s, so in effect, yes.

If the dates are within range you take the values from the corresponding B
cells and give them as both parameters in FREQUENCY. I don't understand
what FREQUENCY gives you when data = bins.

I think I understand this now. You get an array of the number of occurences
of each unique value in the B cells.

Then you're taking the reciprocals and counting them.

Which seems like a perverse way to do it. I prefer to use
SUM(IF(FREQUENCY(...)>0,1)), not because it works any better, but because I
can come back in a month and understand what I'm doing.

I think I'll be able to understand this if I know what "--" is doing, but
more importantly what FREQUENCY does when data = bins.

I think I do now.

Thanks!

Thanks me! :-D

 

[Bob Phillips]

But surely, you are now back to getting 3 as a result, not 2, which I
thought was the whole objective of the post?

The -- is just coercing the date string into a numeric value, which is all
that the underlying value of a date is. If you put =--"20-oct-2007" in a
cell, you get 39375.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Wilba]

But surely, you are now back to getting 3 as a result, not 2, which I
thought was the whole objective of the post?

No. With the start date in A8 and the end date in B8, I get the same result
with these two formulae -

=COUNT(1/
FREQUENCY(IF((A2:A6>=A8)*(A2:A6<=B8),B2:B6),
IF((A2:A6>=A8)*(A2:A6<=B8),B2:B6))
)

=SUM(IF(
FREQUENCY(IF((A2:A6>=A8)*(A2:A6<=B8),B2:B6),
IF((A2:A6>=A8)*(A2:A6<=B8),B2:B6))

0,1))

The COUNT(1/... thing is just too unintuitive for me to be able to quickly
debug it in the future.

The -- is just coercing the date string into a numeric value, which is all
that the underlying value of a date is. If you put =--"20-oct-2007" in a
cell, you get 39375.

Right. The first "-" gives you -39375 and the second one gives you 39375, so
it _is_ just double negation, not a special operator. DATEVALUE would be a
more explicit way to do it.

In the real thing (and the examples above) I don't need to use anything like
that because I'm comparing dates directly. I only put date strings in the
original post to make the example as simple as possible, but on reflection
that was a bad idea.

Thanks a lot for your help Bob.