FAQ Database Discussion Community


Calculate the conditional median value?

excel,excel-formula,worksheet-function,median,array-formulas
I have the following values: Type Value A 5 B 6 A 3 B 7 A 9 A 11 I want to calculate the median value for Type A, and ignore values for Type B. How would I accomplish this, as there is no MEDIANIF function in Microsoft Excel? ...

How to remove substring that is in another column in Excel?

excel-formula,worksheet-function
I want to delete text part from current column. This text part is presented in another column. To be clear here is the image: So I want to delete Model number from Title column....

Matching columns between sheets and transfering names

excel,vba,excel-formula,worksheet-function
I'm trying to transfer values with macro in Excel for my upcoming billiards tournament playoff table. For example let's say I have column A with names {A1,A2,A3,A4} and every name is on a different row. Then I have column B in the same sheet with according values {1,4,3,2}. Now I...

Runtime error 1004. Need to fetch value from another sheet in same Excel file

excel-vba,excel-formula,excel-2010,vlookup,worksheet-function
Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow CurrStr = UCase(Range("A" & iter).Value) result = Application.WorksheetFunction.VLookup(CurrStr, Sheets("CustAR").Range("A2:A2499"), 1, False) '=IF(ISERROR(VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)),"NotFound",VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)) Next...

COUNTIF with OR Statement

excel,excel-formula,worksheet-function,excel-2013,countif
I'm trying to count the number of times two statements occur. The problem is that if the first statement occurs then it won't check for the second. Maybe if easier if I explain like this: If statement1= true then count = 1 and move next else if statement2= true count...

Multiplying column values in Google Spreadsheet with ability to add/delete rows

google-spreadsheet,spreadsheet,worksheet-function
I have an invoice, where there are two columns: Rate (Column H) and Quantity (Column I). And there is Amount (Column J). I need a formula so that it would automatically multiply Rate and Quantity and show it in Amount. The number of rows would be changing (added or deleted),...

Count frequencies if values of other column are equal to specific text

excel,excel-formula,worksheet-function,countif
City Job London Webdesigner London Webdesigner Tokio Lawyer Tokio Teacher Buenos Aires Manager London Athlet Rome Doctor London Webdesigner I'm looking for a way to count the frequency of Webdesigner if "City" is equal to London (in same row). The result of the formula should be 3....

Modifying an existing time formula

excel,if-statement,time,excel-formula,worksheet-function
I have the following formula that I need adjusting and not sure what to do: =IF(TIME(8,0,0)-A1=30/1440,TIME(8,0,0),A1) If I enter 7:30 or 8:00 in cell A1 a value of 8:00 is returned. What I want to do now is also add 8:30. Which means when I enter 7:30, 8:00 or 8:30...

Detecting what value was in a cell prior to a change

excel-vba,worksheet-function
Is there a way to detect what a cell use to contain before it was changed. I'm trying to perform actions based on what the previous value was in a cell. I know there's Worksheet_Change but the Target it uses is the new value.

If the row contains specific text highlight row by row where two columns are the same

excel,excel-formula,worksheet-function,conditional-formatting,countif
I currently use conditional formatting on columns M and N on a spreadsheet to fill duplicate number values using the formula: =COUNTIF($M1:$N1,M1)>1 I was asked by my boss if I could only conditionally format columns M and N if a specific text name in column G appears on that same...

Conditional count

excel,excel-formula,worksheet-function,countif
I want functions which will: add +1 to cell I3 for every W, L, T, IP and R in cells from F8 to F10, so now it has to be 3 because cells F8, F9 and F10 are populated: If F11 were populated also, then it has to be 4....

Discrepancy between results of EXACT function and Conditional Formatting

excel,excel-formula,worksheet-function,conditional-formatting,exact-match
I'm observing an apparent discrepancy between the results of the EXACT function and the conditional formatting tool. Two cells in Column B have the same apparent text (about 30-40 words). But the "highlight cells" tool doesn't recognize them as duplicate values. Comparing the two cells with the EXACT function returns...

HTML code as text in Excel

excel,if-statement,escaping,excel-formula,worksheet-function
I have an Excel file that will be saved as an .csv file for importation into an email automation system. In a function I need to return a piece of HTML code as text. =IF(A2<0,"CHECKMARK CODE",A2) The "CHECKMARK CODE" needs to be replaced by: > < span style="font-size:16px">& #10003;</span>. For...

Excel: how to merge duplicate rows of a column , leaving the rest of column data as it is

excel,excel-formula,worksheet-function
My Excel will be populated using query now the below is the data set which is coming! COL A COL B COL C COL D Claims on Public Sector Entities in Pakistan 0% 0 Claims on Public Sector Entities in Pakistan 1 20% 0 Claims on Public Sector Entities in...

Formula that will search cells in table to find a word

excel,search,excel-formula,cell,worksheet-function
I'm looking for a formula that will allow me to look for a value in a table and return any information if the value exists. Either True/False or a number where it starts (like search formula). The problem is that I will be looking for a word Apple and in...

Change a full column of such as F01U123456 to F.01U.123.456 [closed]

string,excel,excel-vba,excel-formula,worksheet-function
Is it possible to change a full column of such as F01U123456 into F.01U.123.456 with an Excel formula, or would it require a macro?

Combine multiple VLOOKUPs

excel,if-statement,excel-formula,vlookup,worksheet-function
How would I combine these together into one?: =IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,FALSE),"No Match") =IFERROR(VLOOKUP(B3:B12,Sheet2!D:E,2,FALSE),"No Match") =IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,FALSE),"No Match") These are just three but eventually I would need 12 in total to be joined as these are searching team names and there are 12 teams in total....

Store an integer in a cell and use that to set the number of column gaps

excel,if-statement,excel-formula,offset,worksheet-function
I'm creating a revenue model on a simple spreadsheet. On the top, I have a variable cell (C5) that is the "number of weeks between transactions". Below that, I have a simple weekly revenue model. Problem: What I would like is to be able to tweak the fields in yellow...

Conditionally assign unique values

excel,if-statement,excel-formula,max,worksheet-function
I have data, for example: John Doe MD 1 Ben Doe PA Cal Doe MD 1 Drum Doe PA Egg Doe NP Fun Doe MD 1 So everywhere there's an MD I have an IF condition assigning the number 1. But I want to isolate and pull all the names...

Return highest number value in column cell plus a related row cell data

excel,excel-formula,max,concatenation,worksheet-function
|A B C D --------|--------------------------------- 1 |YEAR NAME UNITS SOLD 2 |2011 Jon 99 55 3 |2012 Jon 70 22 4 |2013 Jon 65 34 5 |2014 Jon 78 44 I would like to know how to find the year Jon sold the most (in this case the 55...

Match pattern of a list of words and highlight the found results

excel,excel-formula,worksheet-function
I want Excel to look up which row in column1 contains those strings in column2. When found, highlight those rows in column1. Then I can group those rows together and use them for something else. I have a big data set so I need this to be automated. Column1 Column2...

Count how often a value in a cell in one specific column is not the same as the value in the same row of a specific other column

excel,sum,excel-formula,worksheet-function,countif
Both ColumnA and ColumnB are blank except for O and R (individually). How can I count the number of times a row contains O in ColumnA and R in the same row of ColumnB together with a row that contains R in ColumnA and O in the same row of...

Filter information in two columns

excel,excel-formula,match,worksheet-function,input-filtering
I need a hint is solving Excel task. I have two columns with data (let's say column A contains a list of people I have met in May; column B a list of people I am planning to meet in June). Is there a function in Excel which will compare...

Separate some values by brackets and decrease a number from a column to another column

excel,excel-vba,excel-formula,worksheet-function,substitute
I have an input with these values in different columns: First column Second Column Third column 72 54,72 35,43 63 63,70,74 41 39 32,39,49 51 From this table I need an output like this: First_output Second_output Third_output 50 54,35,43 [50,54][54][35,43] 50 70,74,41 [50,70,74][70,74][41] 50 32,49,51 [50,32,49][32,49][51] First_output I made it...

IF statement containing four conditions but single answer (or blank)

excel,if-statement,nested,excel-formula,worksheet-function
I am trying to get an IF statement containing four conditions to result in one answer (or blank). Here is my formula: =IF($L2=1,IF(AND($H2>=50,$O2>=5,$O2<8),"A: Priority 2","")) ...

Fixed formula when inserting rows

excel,excel-formula,worksheet-function,sumifs,excel-indirect
I am using the following formula in an Excel sheet, that performs the SUM in a range of cells from another sheet (ITEMS CONTROL): =SUMIF('ITEMS CONTROL'!$B$2:$B$20000,B$1,'ITEMS CONTROL'!$C$2:$C$20000) My issue is that when inserting rows in the sheet ITEMS CONTROL, the formula changes as follows: After inserting 18 rows: =SUMIF('ITEMS CONTROL'!$B$20:$B$20018,B$1,'ITEMS...

Select adjacent data based on values in cells

excel,if-statement,integer,excel-formula,worksheet-function
I have a giant Excel file that contains a time column and a data column. In two new columns I'd like to pull the time and the corresponding data (same row) when a new second begins based on the time column's value. The number of rows per second is not...

Excel: search for information

excel,worksheet-function
Guys I need to find specific information in text, and write them down in column. So, I have Column L with Long Description, and I have Column M with words that I need to find in Long Description. When word has been found write that word in Column N at...

How to filter rows that have specific character at the end in Excel?

excel-formula,worksheet-function
I want to get rows that have "/" character at the end of Image column value. So, to make it clear here is the image: What formula I have to use to filter rows that way? ...

Combined COUNTIF for several columns

excel,excel-formula,worksheet-function,countif
We have a table with values like this: I need a formula for cell D1 to count rows that have in column A value "one" and column B is not empty. For example: in first row A1==one but B1 is empty; (that doesn't count) in second row A2==one and B2...

INDEX/MATCH, or another function?

excel,excel-formula,match,vlookup,worksheet-function
I have on Sheet1 18 columns: N a list where an Order Number can be selected O a Line number (as the orders have multiple line numbers associated with them) P, Q and R, I want to pull over the data associated with the Order Number and Line number entered....

VLOOKUP query help required - count from a range

excel,excel-formula,vlookup,worksheet-function,countif
I'm trying to build a spreadsheet to help automate points scoring for an office F1 fantasy league we have. I've attached an example data set, but basically I need to search a range, then count how many times the constructor appears in the numbered positions (discounting R, D as they've...

Differences between two columns [closed]

excel,if-statement,excel-formula,worksheet-function,countif
I have such Excel data: I want to find records in ColumnA which do not exist in ColumnB....

Writing a MATCH or LOOKUP statement with a precondition

excel,if-statement,excel-formula,match,worksheet-function
I am looking to write a statement with the following conditions: If A1="apples" then match B1 in Sheet4 column C If a match is found return the value in F1 If no match then return the value of F2. The formula will be written in D1....

INDEX/MATCH for closest value to a certain date

excel,date,excel-formula,worksheet-function,array-formulas
In sheet "Dividends" I have a table with dividends sorted by daily dates. E.g, columns A and B contain the following entries: 6/14/2015 6/13/2015 6/12/2015 0.045 6/11/2015 6/10/2015 This means that the stock paid a dividend of 0.045 on 6/12/2015. In another sheet "AdjClose", I have a table with weekly...

Select value from cell having “today's” date in the same row

excel,date,excel-formula,vlookup,worksheet-function
I have two columns: Date and Price. I want to pick the value of the cell in the same row as today's date: I want to show "today's" price, so supposing "today" is 12 Feb, then for the above example today's price should show 18. How can I do that...

Match Function in Specific Column Excel VBA

excel,vba,excel-vba,match,worksheet-function
I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration. Say that the number of the column is given by colnumvar. The...

Move rows based on criteria within string

excel,vba,excel-vba,excel-formula,worksheet-function
I have a table in Excel with this format: the first column is Name and the second one Phone with phone number format XX YYYYYZZZZ (e.g. 12 981152989). XX is the state code, YYYYY the first part of number, and ZZZZ the second part of number. I have to do...

Identify all instances of a set where any one instance of the set is paired with a specific value

excel,excel-formula,worksheet-function,conditional-formatting,countif
I am trying to highlight data by row based on an a ref number. The system I pull the report from creates a new event and thus new row item for each action. So every time it breaches or is achieved a new line is created and I am only...

Excel formula for working with multiples

excel-formula,rounding,worksheet-function
I need a formula on Excel which would deduct the multiple of 20 from a given number. For example: 2 would give you 18, 10 would give 10, 23 would give 17, 118 would give 2, 321 would give 19....

How to export data from a PivotTable to .csv in a specific format?

excel,excel-formula,pivot-table,export-to-csv,worksheet-function
I have a worksheet with 65000 rows. The first cell in each row is from a list of 1,500 values. For example: PartNumber1|Application1 PartNumber1|Application2 PartNumber1|Application3 PartNumber2|application1 Partnumber2|Application2 PartNumber3|Application1 PartNumber3|Application2 PartNumber3|Application3 Etc... I need to get an output like the following PartNumber1,Application1,application2,application3 PartNumber2,Application1,Application2 PartNumber3,Application1,Application2,Application3 ....etc Now, in Excel, I can create...

Lookup a Value and Display Column Header

excel,if-statement,excel-formula,lookup,worksheet-function
aaa bbb ccc D 1 5 9 # (aaa, bbb or ccc) 2 6 10 3 7 11 4 8 12 I am trying to find a formula (to go in D2) so that the number matches its column header (aaa, bbb or ccc). For example if I put 5...

Compare 2 set of cells and count

excel-formula,worksheet-function
I'm trying to make something of a pattern recognize counting cell and calculate their probability of occurrence. Set 1 is from a database that looks like this (consists of 6+1 columns): ABABAB 1 CACACA 2 CACACA 2 CACACA 2 CACACA 1 ABABAB 1 Set 2 is what I input manually...

Replace “Lunch Out, Lunch In” with duration in formula to give Time Out/In difference excluding Lunch

excel,excel-formula,rounding,worksheet-function,time-format
I currently have the following formula to track my work schedule. Time is put down in traditional format ie. 8:00 am, and is then totaled to show hours worked as a whole number 8, for 8 hrs worked: =IF(D2="","",ROUND(IF(OR(C2="",B2=""),(D2-A2),(B2-A2)+(D2-C2))*24*4,0)/4) A=Time In , B=Lunch Out , C=Lunch In , D=Time Out....

Show number sign at the right side of number using cell formatting

excel,formatting,excel-formula,worksheet-function,conditional-formatting
I need to create formatting for numbers while keeping their actual value: If a negative is number entered, the minus sign needs to appear on the right-hand side of the number. If a positive number is entered, the plus sign needs to be on the right-hand side of the number...

Formula to increment by volume and issue number, each in sets of three

excel,integer,excel-formula,worksheet-function,modulus
I have journal articles which contain three issues per volume. Is there a formula to increment 1.1, 1.2, 1.3 and then jump to 2.1, 2.2, 2.3?...

Insert characters into string in different locations

string,excel,excel-vba,text,worksheet-function
I have a string which looks like this 20141007023617. And I would like to make it look like this 2014-10-07T02:36:17Z. The T and Z are very important for me. So I would like to insert them as well. So a recap, A1 holds this string 20141007023617 B1 should hold this...

COUNTIF combined with OR

excel,excel-formula,worksheet-function,countif
I want a formula for count how many True/Yes/Si are in the range. If A1 is not empty i want to count. I don't know how to put the 3 parameters in the formula =IF(A1="";"";COUNTIF(A2:A5;"TRUE")) <- I need to add Yes and Si. Edit: I forgot to say that I...

IF statement to show if ALL cells in a row have the same value

excel,excel-formula,worksheet-function,countif
I want to write a formula in Excel to determine if EVERY cell in a row has the same value. There are five cells, for example: C2:G2 that I need to filter out only the ones with the same value, so if only one out of the five cells has...

Find the nearest set of coordinates in Excel

excel,worksheet-function
There are two tables, each with a coordinate pair: longitude and latitude, in decimal degrees (i.e, 37.23222, -121,3433). How do I match table A with its nearest coordinate pair in table B?

Using Wildcards to Extract Text from Cell String Containing a Specific Value/Character

string,excel,extract,wildcard,worksheet-function
In a cell I have a string of text that contains both a Contact Name and Company Name (example in cell A2: John X. Doe, XYZ Inc.). I have successfully created a formula that uses the "_" (underscore character) that connects Company Name with ending Company Name Suffixes (i.e., _Inc.,...