If i use text to columns using comma as a separator, it does not give me the desired results when I have a text like "FAIRFIELD I&S - E PEORIA, IL". I want this display as "FAIRFIELD I&S - E PEORIA, IL". But if i use this feature, I get "FAIRFIELD I&S - E PEORIA" in one column and "IL" in another column. Ideally I want it to show "FAIRFIELD I&S - E PEORIA, IL" in a single column.
View 1 RepliesI am using countif formula to count number of times each name appears in a row, but when I use the filter opeion and select a specific name, and highlight the whole row, the 'count' at bottom right of excel screen does not show same count as the outcome of countif. why is this so?Shouldnt they both be the same?
View 4 Replies View RelatedI have a cell that has a comma separated value that is 354 fields long. As such, if I use the Text To Columns feature to split the data at each column, I lose several columns (because excel cannot have that many columns).
How can I break the data at the comma, but have it list in rows instead?
Is there a way to use the =SUM(--:--) feature, but to bypass any cells that contain text instead of numbers that are in the range?
I'm currently using this formula:
=SUM(N(I31)+N(L31)+N(O31)+N(R31)+N(U31)+N(X31)+N(AA31)+N(AD31))
...to only add up the number cells, as next to each cell, there is a column that has text (the reason for the "N(--:--)" is because depending on criteria, some of these cells print blank).
I just added 50 more columns that need to be added to the formula listed above and I will need to repeat this a dozen times. It will be much easier (and tidier) to be able to do something like this:
=SUM(N(I31:CL31)EXCLUDING CELLS THAT CONTAIN TEXT.
I have a file with a bunch of columns in it. Columns C and D have names in them. I want to filter on these two columns where the results show all rows where the selected name is in both.
Example, If I filter on Column C first on 2 specific names (Joe and Chris), it will not show the results in column D if that name was aligned with a different name in column C (Mark and Steve). I need all rows to come back.
Column C
Column D
Joe
Joe
Chris
Chris
Joe
Chris
Joe
Mark
Joe
Steve
Chris
On 18 worksheets I intend to use variations of the formula below to read ColsD:G and ColsK:N to ascertain whether the values are less then or equal (
View 9 Replies View RelatedI am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
Excel 2010, I have the following list (showing part of it):
Name
T-Shirt Sizes
Nathan
S
[Code]....
The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.
Thus, I've been trying to use the IF statement with the following logic but to as no avail
IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell
and so on for the other sizes...kind of like a counter for a for-do-loop.
I Have 20:00-04:00 as text in a cell, what i need is to take the 04:00 and show only if its after 00:00 and before 07:00 and show it as time value. This is where I'm up to so far but i don't know how to ask it for > 00:00 and < 07:00.
View 4 Replies View RelatedI would like to write a formula to have a result in a cell that gives me the maximum value of a column, but smaller than another cell.
For example:
C1 = Maximum value in column A1:A20, but smaller than B1.
I have around 500 csv files, each with four columns of data. However, the first column does not have a heading. Is there any way I can give the column name to the first column in each of 500 csv files? Here is one of the csv files:
name1 name2 name3
1774.77 4.15 29.28
17810.45 6.2450.36
18257.86 16.9169.11
18326.46 20.1 18.93
1841.48 20.29 0.95
1850.84 20.45 0.79
2031.49 3.44 8.86
2040.06 3.41 -0.87
2050.02 3.37 -1.17
2060.91 3.51 4.15
20711.35 2.77 -21.08
2085.23 3.51 26.71
2090.02 3.51 0
2100 3.5 -0.28
2110.06 3.33 -4.86
21229.25 6.91 107.51
I am creating for the pupils at my school. Comparing the grades they were predicted and have achieved I would like to give them a score.
I have already achieved this but it means wasting 3 columns for each subject to work it out. The ambition is to do all the working out and assign a point score value in 1 column.
The spreadsheet it attached if anyone would be kind enough to have a look and see if there may be a formula which will do what I need.
I have multiple sheets in excel file. Every sheet having same information for different areas. e.g. The bug was reported. One sheet might have answer in YES and other sheet might have No. I need to get answer YES if even one of sheet contains YES for the field.
View 9 Replies View RelatedWhen using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?
I’m looking for a formula that will give me a figure against the name in column A (name) while looking picking out a specific word within the column B, say "alarm"
So it reads
Alan Ingram 1
David walker 3
And so on...
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
I'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."
I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.
Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.
I need a formula (or formulas) to give me the "coordinates" (Column & Row location) of a certain value in a large rectangular range. Intuitively this should be doable with a smart combination of VLOOKUP, HLOOKUP, INDEX, MATCH, ADDRESS and/or CELL functions (maybe in the form of "array formulas") but I can't seem to find the way...
Here is an example of what I need:
I have a large rectangular range (A10:F500), full of numerical values. Then in cell A1 I have one of the (many) values contained in the range, let's say, 3.14159. I need two formulas that give me the following:
a) A formula in cell B1 that tells me in which COLUMN of the range the value 3.14159 is located
a) A formula in cell C1 that tells me in which ROW of the range the value 3.14159 is located
If the range consisted of a single column (or a single row), this would be an easy INDEX and MATCH combination.... but I'm dealing with a *rectangular* range here...
Attached is a spreadsheet that contains a list of Football teams (F) with corrasponding numbers next to them (E). In column A is a list of footballers and column B has a corrasponding number. WHat I want to be able to do is type in the footballers names in A and type a number next to them in B, in column C it would read off the corrasponding Team number and populate it with the team name. In the attached example I'll manually list the fist teams in B, i need a formula todo this for me. Probably quite simple.
View 2 Replies View RelatedHow do I paste a text paragraph into Excel and give each individual word into its own cell?
View 6 Replies View RelatedI am using Excel 2010 and am looking for a formula that can solve this matix in column below. The yellow is where I would like the answers.
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] ........
This is what it would look like when the formula were applied
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] .........
I am trying to created a spreadsheet for work where I have created to validation drop down boxes, one each box has been selected i want it to return back with the correct answer in the 3rd column.
below are the 3 colums. i have created a validation for column 1 and 2 but when selected i want the final box to = column 3 ie. >=9, =2
120%
12
>=2
130%
13
>=2
140%
[code].....
I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below
Red
Green
Blue
Orange
Yellow
Result
2
4
3
6
1
[code]...
I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?
I just want to know if there is anyway to disable the help feature in Microsoft Excel?
I have to administer some exams.
Here is what I am looking for:
I have several groups of data separated by a couple of blank lines. For each group of data I would like to sum the rows in column F. I do not want to use the subtotal feature but rather the autosum feature for each section. It is important that the formula is actually entered so if I add additional rows of data the formula will keep calculating. So at the end of each data the formula is as follows =sum()
Does VB have the WEEKNUM feature ?, I can use WEEKNUM in an Excel sheet ok, but it is not recognised in VB ... I have installed both the Analysis tool packs, the one for VB as well.
View 8 Replies View RelatedI'm trying to enter relatively simple drug calculation formulas in Excel where the result is appended with the proper units to administer. For example, if I'm calculating for units of regular insulin with a formula such as:
=0.75*f2
I'd like the cell to make the above calculation, and append it with "Units Reg Insulin" or something similar. Thus far, I've just been putting the units as text in adjacent cells but the result looks a bit messy.
Is this possible? I'm using Excel 2007 and the Help index does not seem to offer much guidance about this.