Counting unique values in a list generated with the OFFSET functio

[mikelee101]

Hello,
I'm curious about something, and was wondering if anyone could shed some
light on it for me. I have a list that I wanted to count unique values from.
The list is generated using a formula that contains the OFFSET function.
The list is text with no blanks. The formula that I used to count the unique
values is

=SUM(IF(FREQUENCY(MATCH(C5:C14,C5:C14,0),MATCH(C5:C14,C5:C14,0)),1,0))

(I used it both as and not as an array formula)

It has always worked in the past for me, but this time returned #N/A. After
much putzing around and playing with it, I found that the above formula
always returns that error when the list you are counting contains formulas
with the OFFSET function in them. That confused me, because I couldn't
figure out why that would be the case. When I use the formula auditing
toolbar and showed the calculation steps, the last step is:

SUM({1;1;1;0;0;1;0;1;0;0;0})

which evaluates to #N/A.

I was able to get the formula working in my spreadsheet by replacing the
OFFSETs in my list generating formulas with a combination of VLOOKUP and
HLOOKUP, but I'm still perplexed by this not working based on the function
used to retrieve the values in the list. If anyone has heard of this or has
any ideas why it might work that way, I'd love to know.

Thanks to all.

Excel 2003, WinXPPro SP2

 

[T. Valko]

So, you're using OFFSET as a lookup formula? What's your OFFSET formula look
like?

There are better ways to do lookups without using the volatile OFFSET.
L/V/HLOOKUP, INDEX/MATCH. Sometimes you can even use SUMIF and/or SUMPRODUCT
when you're dealing with numbers.

 

[mikelee101]

Yes, I realize there are other ways to do the lookup, but OFFSET is sometimes
easier when you are trying to retrieve something from a particular position
in a list when the list changes frequently. That having been said, I used a
combination of VLOOKUP, HLOOKUP and CHOOSE to get my spreadsheet working.
What I'm curious about is why the particular formula in the original post
fails.

Here's an example. Open a new workbook. In A1 through A10, enter:

a
b
c
b
d
a
e
d
c
d

Then, in B1 enter the formula =A1, and drag it down to B10. Then, in C1,
enter =OFFSET($A$1,ROW()-1,0) and fill that down to C10. You should then
have 3 identical lists.

Then, in A12, enter the following:

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0)),1,0))

You will get 5, which is the number of unique values in the list above.
Take A12 and drag it across to C12. In A12, you will have 5. In B12, you
will have 5. In C12, you will have #N/A.

If you use the Evaluate Formula option from the Formula Auditing toolbar,
you'll see that each of the 3 functions (A12, B12 and C12) all evaluate down
to

=SUM({1;1;1;0;1;0;1;0;0;0;0})

The difference is that C12 evaluates that as #N/A, where the other two
evaluate it to 5. My question is, why? It doesn't make any sense to me that
the method of retrieving the values in the list should have any impact on the
way that the list is treated, but that seems to be the case.

Thanks again.

Mike

--
Mike Lee
McKinney,TX USA

So, you're using OFFSET as a lookup formula? What's your OFFSET formula look
like?

There are better ways to do lookups without using the volatile OFFSET.
L/V/HLOOKUP, INDEX/MATCH. Sometimes you can even use SUMIF and/or SUMPRODUCT
when you're dealing with numbers.

 

[T. Valko]

Hmmm...

I see what you mean. I don't know why that is. It obviously has something to
do with OFFSET. I tried many variations of your formula and they returned
either #N/A or 0.

However, try this formula:

=SUMPRODUCT((C1:C10<>"")/COUNTIF(C1:C10,C1:C10&""))

As an added bonus, it accounts for (excludes counting) empty/blank cells.

--
Biff
Microsoft Excel MVP

Yes, I realize there are other ways to do the lookup, but OFFSET is
sometimes
easier when you are trying to retrieve something from a particular
position
in a list when the list changes frequently. That having been said, I used
a
combination of VLOOKUP, HLOOKUP and CHOOSE to get my spreadsheet working.
What I'm curious about is why the particular formula in the original post
fails.

Here's an example. Open a new workbook. In A1 through A10, enter:

a
b
c
b
d
a
e
d
c
d

Then, in B1 enter the formula =A1, and drag it down to B10. Then, in C1,
enter =OFFSET($A$1,ROW()-1,0) and fill that down to C10. You should then
have 3 identical lists.

Then, in A12, enter the following:

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0)),1,0))

You will get 5, which is the number of unique values in the list above.
Take A12 and drag it across to C12. In A12, you will have 5. In B12, you
will have 5. In C12, you will have #N/A.

If you use the Evaluate Formula option from the Formula Auditing toolbar,
you'll see that each of the 3 functions (A12, B12 and C12) all evaluate
down
to

=SUM({1;1;1;0;1;0;1;0;0;0;0})

The difference is that C12 evaluates that as #N/A, where the other two
evaluate it to 5. My question is, why? It doesn't make any sense to me
that
the method of retrieving the values in the list should have any impact on
the
way that the list is treated, but that seems to be the case.

Thanks again.

Mike