Ignoring Blank Cells/commonly Occuring Text
Feb 14, 2007
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"
View 10 Replies
ADVERTISEMENT
Aug 10, 2009
I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example:
Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price
Gloves | $4.59 | $5.00 |$6.00 | $3.56
Hats | $5.00 | | | $4.59
In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price.
My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is:
=IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP 3","MYCOMPANY")))
This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes.
So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?
View 10 Replies
View Related
Feb 5, 2010
I am trying to return the criteria MET, NOT MET & EXCEEDS. This works when each cell contains a number, but when one is blank the formula below returns "EXCEEDS" I'm guessing this is because is sees a blank as zero.
=IF(BN102<$BJ102,"NOT MET",IF(BN102>$BJ102,"EXCEEDS",IF(BN102=$BJ102,"MET")))
I've tried using this alternative but it still returns the same.
=IF(BO102<$BJ102,"NOT MET",IF(BO102>$BJ102,"EXCEEDS",IF(BO102=$BJ102,"MET",IF(ISBLANK(B102)," ",""))))
Basically I need to know how to make excel ignore the blank cell and not see it as zero thus returning "EXCEEDS". Although the cell values are integers they are derived from another cell using the following formula, I'm not sure if this makes any difference
=IF(ISERROR(VLOOKUP(BO11,$BH$63:$BI$87,2,FALSE)),"",(VLOOKUP(BO11,$BH$63:$BI$87,2,FALSE)))
View 3 Replies
View Related
Mar 25, 2012
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
View 4 Replies
View Related
Oct 2, 2007
I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.
View 8 Replies
View Related
Aug 18, 2014
I have created a drop down cell based on the cells in the first row of a particular table. Now when i select a particular cell from the drop down i want to fetch all the values in the respective column of the selected cell on to a different group of cells. While fetching the data to those group of cells, i want to ignore all the null, blank cells. All this actions shall be automated and shall happen simultaneously on selecting the drop down.
View 1 Replies
View Related
Sep 5, 2009
Using formula rather than VB, I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell. To elaborate, I have sheets that contain the days of each month and I need to exclude for other calculation purposes, the number of days (cells) where no entry of data has been input at the beginning of a month, NOT after the first data entry. ie. September has 30 days.
The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry. I have looked at COUNT functions but cannot find an solution. Perhaps it's not possible using basic formula?
View 4 Replies
View Related
Oct 24, 2011
I am trying to get a rolling 8wk avg of a large group of data. I am trying to take the avg of 8 vlookups:
=AVERAGE(VLOOKUP($A$9,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-7,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-14,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-21,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-28,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-35,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-42,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-49,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE))
However, in some cases, the cell to be looked-up may be blank. Using the formula above, the result of these vlookups is "0". I want to take the average of these vlookups excluding the blanks from the 'Raw Data' sheet.
View 6 Replies
View Related
Dec 28, 2007
I have a workbook which has data in column A. I also have data in column I. What im trying to do is copy and paste the data from column I to A but if the cell in column I is blank I need that ignored and the data thats in column A at pres kept.
View 9 Replies
View Related
Sep 30, 2009
I am trying to add up wins and loses for individuals on a team. 6 players
There are 3 games per match and one match a week for 15 weeks. One sheet per week.
The players are inputted randomly each week. And the scores are entered after the match.
The following is a part of the formula that I am using but it returns a #NA because the cells in the weeks not played yet are blank, players and scores. Is there a better way to do this or a way I can ignore the blank cells until they are entered. Thanks in advance.
=SUM(LOOKUP($A$17,'Week 1'!$C$20:$D$23,'Week 1'!$E$20:$E$23),LOOKUP($A$17,'Week 2'!$C$20:$D$23,'Week 2'!$E$20:$E$23),etc.)
A17 is the players name, 1st range is where the players name would be entered, 2nd range is where the score is entered.
View 9 Replies
View Related
Oct 2, 2007
i have a list which is populated with data from another sheet.. if there is no data in the corresponding cell on the other sheet then the cell is blank.. i've tried using:
=OFFSET($Z$2,0,0,MATCH(REPT("z",255),$Z:$Z))
but it doesn't see the blank cells as blank i.e. no data in them as they contain formula's.. (even although there are no values populated) - can anyone offer any help in relation to this???
View 9 Replies
View Related
Apr 30, 2010
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
View 10 Replies
View Related
May 30, 2014
I have a spreadsheet where i do need to count cells values that are formatted as text but i do need to ignore the real text in some cells, in this formula i do need to add values depending of the adjacent cell.
I have attached a example : New Microsoft Excel Worksheet.xlsx‎
View 8 Replies
View Related
Apr 23, 2009
I'm trying to figure out if an employee is in probation period or not. I have this formula in S3 =IF(TODAY()-R3>90, "No", "Yes"). I need to leave S3 empty if no value in R3 (R3 contain hiring date)
View 2 Replies
View Related
Jul 25, 2006
I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.
View 13 Replies
View Related
Jun 15, 2009
I have a column with with Employee ID numbers, with anywhere from 2-5 blanks rows in between them. In another sheet I want a column that returns these ID numbers without the blank rows in between. All of the ID's begin with "N", for example:
I have:
N33333
(blank row)
(blank row)
N22222
(blank row)
(blank row)
(blank row)
N66666
I want:
N33333
N22222
N66666
View 9 Replies
View Related
Jun 20, 2014
So in Cell K12 there is a rather large formula (I condensed it for this example).
This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7
So based on the formula in K12 I need Cell J12 to Display the answer.
if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.
The problem comes in when a Cell is N/O (Not Observed)
How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.
I know in a simple formula I can add <>0 to the formula to ignore zeros. How to write it into a bigger formula such as in K12.
For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.
View 14 Replies
View Related
Feb 4, 2010
I've read dozens of threads on how to have conditional formatting ignore blank cells but I am not understanding how to make it happen myself...
Logic: Highlight the cells that are >=10 but ignore the blank cells that have functions in them
View 3 Replies
View Related
Aug 13, 2012
I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
View 4 Replies
View Related
Aug 1, 2012
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
[URL]
View 4 Replies
View Related
Jun 14, 2008
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).
I would like a comma or full stop and then a space between each item merged (cell A5).
The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.
Only where all 4 cells are blank, will I need cell 'A5' to indicate this.
The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.
View 9 Replies
View Related
Mar 31, 2009
I have the following formula but I want to ensure that the sum ignores any text so I don't get #VALUE! returned. I've tried including ISERROR but doesn't seem to be working correctly. The formula is: =IF(H19+H20+H21+H22+H23+H24+H25+H26+H27+H28>0,SUM(H19:H28),"")
View 3 Replies
View Related
Jul 18, 2013
If I have a row of data but ccasionally text is involved then I get the Error Message #valueI know if I enter sum(A1:A6) it works but if the values I wnat to add up arent next to each other it wont work i.e. I want sum(A1+C1+E1+L1+X1) but if any of the cells contain text I get the value error
View 8 Replies
View Related
Jun 4, 2007
As simple as this sounds, I can't seem to make this work. I am looking for code for the following:
I would like to enter the word "Empty" into a cell if the cell is blank. The
range can be variable. The only columns that contain an empty cell are
columns B or C which can have any number of rows.
View 8 Replies
View Related
Dec 9, 2008
I have this macro that does text-to-columns based on delimiting with spaces.
It seems to work fine but the first time I use it when I open my spreadsheet it comes up with "Do you want to replace the contents of the destination cells?".The answer to this is always yes, is there a little bit of code I can poke into this macro to ignore this warning?
View 3 Replies
View Related
Jan 28, 2009
My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!
View 3 Replies
View Related
Oct 3, 2012
I have a column of cells, some contain text and some do not. The cells which contain txt or are left blank could change on a daily basis.
I'd like Excel to look at the column of cells which contain the data and populate the information into a list on another worksheet without the blank cells, can I do this?
How it looks now: Sheet 1, cells A1:A6
Henry
blank
blank
James
blank
Bob
What would be sweet: Summary data on Sheet 2, cells A1:A3
Henry
James
Bob
View 4 Replies
View Related
Jan 3, 2008
I'm using the the Texttocolumns method in a Macro, and when it runs across a cell without any data I get "Run time error '1004' No data was selected to parse". How would I get passed this? I would like it to just remain a blank range without the error. Here's the code I'm using:
Range("F38").Select
Selection.TextToColumns Destination:=Range("F38"), DataType:=xlFixedWidth, _
FieldInfo:= Array(Array(0, 1), Array(5, 1), Array(13, 1), Array(21, 1), Array(25, 1)), _
TrailingMinusNumbers:=True
View 2 Replies
View Related
Jun 14, 2009
I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies.
for example
aa
bb
ww
xx
zz
rr
gg
hh
ii
jj
would become
aa bb
ww xx zz
rr
gg hh ii jj
I have over 30000 lines so doing it by hand is not an option.
View 9 Replies
View Related
Aug 7, 2008
When choosing ascending or decending, is it possible to always keep rows with the designated blank cells at the bottom? Trying to prevent the blank cells from popping on the top when i filter by a certain field.
View 4 Replies
View Related
