Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignoring Blank Cells | Excel Worksheet Functions | |||
30 Day Moving Average Ignoring Blank Cells | Excel Worksheet Functions | |||
Help with ignoring blank cells | Excel Discussion (Misc queries) | |||
Locating first blank cell | New Users to Excel | |||
geomean ignoring blank cells and chars | Excel Worksheet Functions |