#1   Report Post  
Old November 21st 05, 07:29 PM posted to microsoft.public.excel.worksheet.functions
Daniel Bonallack
 
Posts: n/a
Default Formula help

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel




  #2   Report Post  
Old November 21st 05, 07:43 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Formula help

Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
That formula counts the number of non-blank, unique items.

Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel



  #3   Report Post  
Old November 21st 05, 07:51 PM posted to microsoft.public.excel.worksheet.functions
Daniel Bonallack
 
Posts: n/a
Default Formula help

Hi Ron

Thanks for offering a solution, but I'm not sure how to apply it. Maybe my
example wasn't clear, but I wasn't sure why you have A1:A10 - I'm trying to
test across the row A11, then copy that formula down to 12,000 rows.

Hope that clarifies.
Daniel


"Ron Coderre" wrote:

Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
That formula counts the number of non-blank, unique items.

Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel



  #4   Report Post  
Old November 21st 05, 07:58 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Formula help

Um...OK..You want this:
E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&""))


Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

Hi Ron

Thanks for offering a solution, but I'm not sure how to apply it. Maybe my
example wasn't clear, but I wasn't sure why you have A1:A10 - I'm trying to
test across the row A11, then copy that formula down to 12,000 rows.

Hope that clarifies.
Daniel


"Ron Coderre" wrote:

Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
That formula counts the number of non-blank, unique items.

Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel



  #5   Report Post  
Old November 21st 05, 08:03 PM posted to microsoft.public.excel.worksheet.functions
Daniel Bonallack
 
Posts: n/a
Default Formula help

This works perfectly - sorry, I now see how I could have just made the column
to row adjustment myself.

I confess, I don't really understand the formula, but I guess that doesn't
matter - thanks very much for providing a solution.

Daniel


"Ron Coderre" wrote:

Um...OK..You want this:
E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&""))


Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

Hi Ron

Thanks for offering a solution, but I'm not sure how to apply it. Maybe my
example wasn't clear, but I wasn't sure why you have A1:A10 - I'm trying to
test across the row A11, then copy that formula down to 12,000 rows.

Hope that clarifies.
Daniel


"Ron Coderre" wrote:

Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
That formula counts the number of non-blank, unique items.

Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel





  #6   Report Post  
Old November 21st 05, 08:39 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Formula help

See if this helps with the understanding:

Using this data:
A1: DUCK
B1: DUCK
C1: (blank)
D1: CAT

and the formula =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&""))

The (A11<"") part tests if each cell in A11 is not blank. Each
non-blank returns a 1, each blank returns a zero. (Actually, it returns TRUE
and FALSE, but Excel converts them to 1's and 0's)
Consequently, the example data would return this array: 1,1,0,1

Next, the COUNTIF(A11,A11&"") section counts how many times it finds
each cell's value in the range A11.
Our data would return the array: 2,2,1,1

So the resulting array divisions would match the items from the first array
with the items in the second array to get: 1/2, 1/2, 0/2, 1/1

The SUMPRODUCT function adds all of those values and is only really
necessary in this example to force Excel to perform array calculations. In
this case, the total equals 2 unique items.

You could skip the SUMPRODUCT function and use this formula instead:
=SUM((A11<"")/COUNTIF(A11,A11&""))
....but you'd need to confirm that array formula by holding down the [Ctrl]
and [Shift] keys when you press [Enter]. For many people, that's too obscure
a combination to remember only occaissionally. Hence, I generally prefer the
SUMPRODUCT function approach.

I hope that helps?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

This works perfectly - sorry, I now see how I could have just made the column
to row adjustment myself.

I confess, I don't really understand the formula, but I guess that doesn't
matter - thanks very much for providing a solution.

Daniel


"Ron Coderre" wrote:

Um...OK..You want this:
E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&""))


Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

Hi Ron

Thanks for offering a solution, but I'm not sure how to apply it. Maybe my
example wasn't clear, but I wasn't sure why you have A1:A10 - I'm trying to
test across the row A11, then copy that formula down to 12,000 rows.

Hope that clarifies.
Daniel


"Ron Coderre" wrote:

Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
That formula counts the number of non-blank, unique items.

Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel



  #7   Report Post  
Old November 21st 05, 08:56 PM posted to microsoft.public.excel.worksheet.functions
Daniel Bonallack
 
Posts: n/a
Default Formula help

That's really clever - thanks for taking the time to explain.

As you've probably guessed, the actual example I'm applying this to isn't
about ducks and cats. My problem is that it's not actually A1, B1, C1, and
D1 that I'm testing, but rather G1, M1, S1 and Z1.
Can the same formula work across this range (to put the answer in AA1)?

Thanks again - if you've moved on to help others and feel this thread is at
close, I'll quite understand

Daniel

"Ron Coderre" wrote:

See if this helps with the understanding:

Using this data:
A1: DUCK
B1: DUCK
C1: (blank)
D1: CAT

and the formula =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&""))

The (A11<"") part tests if each cell in A11 is not blank. Each
non-blank returns a 1, each blank returns a zero. (Actually, it returns TRUE
and FALSE, but Excel converts them to 1's and 0's)
Consequently, the example data would return this array: 1,1,0,1

Next, the COUNTIF(A11,A11&"") section counts how many times it finds
each cell's value in the range A11.
Our data would return the array: 2,2,1,1

So the resulting array divisions would match the items from the first array
with the items in the second array to get: 1/2, 1/2, 0/2, 1/1

The SUMPRODUCT function adds all of those values and is only really
necessary in this example to force Excel to perform array calculations. In
this case, the total equals 2 unique items.

You could skip the SUMPRODUCT function and use this formula instead:
=SUM((A11<"")/COUNTIF(A11,A11&""))
...but you'd need to confirm that array formula by holding down the [Ctrl]
and [Shift] keys when you press [Enter]. For many people, that's too obscure
a combination to remember only occaissionally. Hence, I generally prefer the
SUMPRODUCT function approach.

I hope that helps?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

This works perfectly - sorry, I now see how I could have just made the column
to row adjustment myself.

I confess, I don't really understand the formula, but I guess that doesn't
matter - thanks very much for providing a solution.

Daniel


"Ron Coderre" wrote:

Um...OK..You want this:
E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&""))


Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

Hi Ron

Thanks for offering a solution, but I'm not sure how to apply it. Maybe my
example wasn't clear, but I wasn't sure why you have A1:A10 - I'm trying to
test across the row A11, then copy that formula down to 12,000 rows.

Hope that clarifies.
Daniel


"Ron Coderre" wrote:

Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
That formula counts the number of non-blank, unique items.

Does that help?

***********
Regards,
Ron


"Daniel Bonallack" wrote:

I have a text value in cells A11. Let's say that the values read across
the row:
A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck"

I want a formula in cell E1 that will return "No" in this case, meaning that
the items are not all same.

The bit that makes it too hard for me is that there may be blanks, and the
blanks should not be counted. So:
A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E.

Thanks very much for any help.
Daniel





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF formula? meris Excel Worksheet Functions 1 September 6th 05 07:14 AM
writing a formula for a colored value aaronwexler New Users to Excel 11 September 1st 05 03:11 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM


All times are GMT +1. The time now is 12:30 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017