Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Ignoring blank cells while locating common text value

I hope that I have been able to make this clear enough

1. First thing I am trying to do. I have a column of cells that have
multiple values, some with text and some with no values at all. I want to be
able to display in A1 the most commonly occurring text in cells C1:C15, and
be able to display in B1 the number of times that A1 occurs in the same
range. Below are the formulas that I am using. There are two problems that I
am running into: First, the formula returns a #NA error if any of the cells
in the range are left blank. Second, the formula counts the spaces or zeros,
so if there are more blanks than the word €œamber€ then A1 returns €œ € and B1
returns the corresponding number.

A1
=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))

B1
=COUNTIF(C1:C16,A1)

2. Second thing I am trying to do. In A2 I want to display the second most
commonly occurring text in the range, with its corresponding count in cell
B2, and the third most in A3 and B3, etc

Illustration:

C1 Amber
C2 Red
C3
C4
C5
C6 Red

Desired result:

A1 "Red" B1 "2"
A2 "Amber" B2 "1"

Results with forumla as posted

A1 " " B1 "3"

Any help would be greatly appreciated

Thanks in advance
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Ignoring blank cells while locating common text value

You need a helper column of formulas: insert a blank column D, and in cell
D1, enter the formula

=IF(AND(C1<"",C1<"
"),IF(COUNTIF($C$1:$C$15,C1)=COUNTIF($C1:$C$15,C1) ,COUNTIF($C$1:$C$15,C1)+ROW()/100,""),"")

and copy down to D15. Then in cell A1, enter the formula

=INDEX($C$1:$C$15,MATCH(LARGE(D:D,ROW()),D:D,FALSE ))

and copy down for as far as you want. Keep the formulas in B:B the same.

HTH,
Bernie
MS Exel MVP



"jumpmaster_france" wrote in
message ...
I hope that I have been able to make this clear enough

1. First thing I am trying to do. I have a column of cells that have
multiple values, some with text and some with no values at all. I want to
be
able to display in A1 the most commonly occurring text in cells C1:C15,
and
be able to display in B1 the number of times that A1 occurs in the same
range. Below are the formulas that I am using. There are two problems that
I
am running into: First, the formula returns a #NA error if any of the
cells
in the range are left blank. Second, the formula counts the spaces or
zeros,
so if there are more blanks than the word "amber" then A1 returns " " and
B1
returns the corresponding number.

A1
=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))

B1
=COUNTIF(C1:C16,A1)

2. Second thing I am trying to do. In A2 I want to display the second most
commonly occurring text in the range, with it's corresponding count in
cell
B2, and the third most in A3 and B3, etc

Illustration:

C1 Amber
C2 Red
C3
C4
C5
C6 Red

Desired result:

A1 "Red" B1 "2"
A2 "Amber" B2 "1"

Results with forumla as posted

A1 " " B1 "3"

Any help would be greatly appreciated

Thanks in advance



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
Ignoring Blank Cells Reefaman Excel Worksheet Functions 2 June 11th 06 05:43 PM
30 Day Moving Average Ignoring Blank Cells ethatch Excel Worksheet Functions 2 January 17th 06 10:37 AM
Help with ignoring blank cells Darren Excel Discussion (Misc queries) 1 November 19th 05 08:48 PM
Locating first blank cell Mike W New Users to Excel 3 August 25th 05 09:02 PM
geomean ignoring blank cells and chars Stan Altshuller Excel Worksheet Functions 1 January 12th 05 10:21 PM


All times are GMT +1. The time now is 07:18 AM.

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

About Us

"It's about Microsoft Excel"