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

Wilba wrote:
Bob Phillips wrote:

=COUNT(1/FREQUENCY(
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6),
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6)))

which is an array formula ...

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


Wilba wrote:

I'm using DCOUNT to count a subset of records in a table, and it's doing
exactly what I want, but now I want to count only the records with a
unique value in one of the columns. Like -

A B C D
1 Date Account
2 01-Oct 123
3 02-Oct 456
4 03-Oct 123
5 03-Oct 456
6 04-Oct 789
7
8 Date Date Account
9 =02-Oct <=03-Oct =DCOUNT(A1:B6,,A8:C9)

I want to get D9 to show me 2 instead of 3. I assume there is nothing I
can put in C9 to make DCOUNT work that way, so I expect if it's possible
it would have to be done differently. That's fine as long as that will
give me the unique count for a subset of the data (as defined for DCOUNT
by the text in A9 and B9).

Any ideas? Thanks!

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

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" wrote in message
...
Wilba wrote:
Bob Phillips wrote:

=COUNT(1/FREQUENCY(
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6),
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6)))

which is an array formula ...

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


Wilba wrote:

I'm using DCOUNT to count a subset of records in a table, and it's
doing exactly what I want, but now I want to count only the records
with a unique value in one of the columns. Like -

A B C D
1 Date Account
2 01-Oct 123
3 02-Oct 456
4 03-Oct 123
5 03-Oct 456
6 04-Oct 789
7
8 Date Date Account
9 =02-Oct <=03-Oct =DCOUNT(A1:B6,,A8:C9)

I want to get D9 to show me 2 instead of 3. I assume there is nothing I
can put in C9 to make DCOUNT work that way, so I expect if it's
possible it would have to be done differently. That's fine as long as
that will give me the unique count for a subset of the data (as defined
for DCOUNT by the text in A9 and B9).

Any ideas? Thanks!

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

Bob Phillips wrote:

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.


Wilba wrote:
Wilba wrote:
Bob Phillips wrote:

=COUNT(1/FREQUENCY(
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6),
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6)))

which is an array formula ...

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


Wilba wrote:

I'm using DCOUNT to count a subset of records in a table, and it's
doing exactly what I want, but now I want to count only the records
with a unique value in one of the columns. Like -

A B C D
1 Date Account
2 01-Oct 123
3 02-Oct 456
4 03-Oct 123
5 03-Oct 456
6 04-Oct 789
7
8 Date Date Account
9 =02-Oct <=03-Oct =DCOUNT(A1:B6,,A8:C9)

I want to get D9 to show me 2 instead of 3. I assume there is nothing
I can put in C9 to make DCOUNT work that way, so I expect if it's
possible it would have to be done differently. That's fine as long as
that will give me the unique count for a subset of the data (as
defined for DCOUNT by the text in A9 and B9).

Any ideas? Thanks!