I am helping a friend with some data analysis in Excel. Here's how our data looks like: Car producer | Classification | Prices from 9 different vendors in 9 columns AUDI | C | 100 200 300 400 500 600 700 800 900 AUDI | C | 100 900 800...

I have this in Sheet1: and in Sheet2 I want to lookup and filter the colored data, and the result would be: Goal: to return the list of colored cells Filtering (lookup) conditions: 1. Return the colored cell (copy/paste the exact cell content) from columns J to V from Sheet1...

Is there a more efficient approach to this formula? =IF(A1="Texas",1,)&IF(A1="Washington",2,)&IF(A1="Kansas",3,)&IF(A1="California",3,) Simple question, but I'm sure there is a better way then the one I've currently got...

I have a table in which in one column I have values that are assigned to special models of hardware. The table looks like this: We need to find an average of three of the highest numbers for each model in that table, for example: We have a model 22PFx4109,...

I'm currently using Sum Product to rank year over year data from largest to smallest. My formula in Column Z is SUMPRODUCT(--(ABS($Y$5:$Y$165)>ABS(Y5)))+1 using the data in column Y. The year over year data is in columns W and X. I want to write a formula that does the same thing...

This question is an exact duplicate of: Copy cell from a coloumn if value is starting with #excel #formula [duplicate] 3 answers I have 3 columns of data in Excel spreadsheet, column 1 is Business Unit, column 2 is item_ID and column 3 is Cost. Column 2 and 3...

so I am trying to turn this formula into VBA code: =COUNTIFS('Report'!$J:$J,"=In Force") However, I have acquired the column in the 'Report' file that I will be extracting the data from. The column number it stores in variable ColNum. I need to ensure that it extracts data from the column...

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...

I have a huge amount of people in my excel sheet and I want to split them by country with excel coding, here is an example of my data: Country | Name UK | Tom Austria | Bobsky UK | Ralf Germany | Badolf Germany | Schwartz UK | Andy...

Normally, where the values in the column of a lookup array are unique there is only a need to match the value in the last dynamic data validated list with the value in the relevant column of the lookup array to provide the range of values for the next dynamic...

Given the following data in Excel, how can I write a query to: a) Show the number of unique (one time) logins only and b) Exclude "admin" user logins in the count The result should be 3. The data: admin admin admin admin admin admin admin admin admin admin jdoe...

I have a sheet with the following demo data (yeah the content is german don't mind that) And I need a formula which will search for the criteria in A1 and B1 and returns the respective value out of the matrix E3:M8 For example Search criteria is: X and 2...

=SUMIFS(C1:C5,A1:A5-B1:B5,">5") Is the above formula workable in excel Column A and B contain dates Column C contains quantity What i need is When Column A minus Column B greater than 5 then sum all the quantity I understand this is able to do with creating a new column to get...

I am experimenting with the excel functions. I am trying to obtain the sum of a range of cells (A3:F3). However, I only want the first three months that are numeric from the last month. So given the table below. 1| ----- A -------------- B -------------- C------------ D ------------- E...

I have a long column of data (15000 values) that simplified looks like this: A B C D 1 lorem pellen Vestibulum 2 epsum tesque pretium 3 Morbi vel convallis 4 fermentum tellus nibh 5 Interdum molestie Vi .. 15000 Then I have a second table: A B C TYPE...

I have successfully managed to calculate the number of days in a specific month between two dates. However I need to calculate those days as work days within the formula. For example. | ------------- A -------------------- B --------------- C | 1 ------- 11/12/2014 ----- 17/03/2015 ----- 01/03/2015 | 2 A...

I have an Excel spreadsheet of the form: A,B X,1 X,5 Y,4 Y,11 X,7 Z,1 I would like to get the maximum value of column B for each distinct value of column A - how can I do it via pure Excel formula? Desired result (order is irrelevant): A,B Y,11...

I am using Excel from Office 2010. I am trying to get values from a specific row (or, eventually, rows) in the table, found by running a search. I can successfully identify the row in the table that I want to output the data from, but the INDEX function is...

I'm looking for a way to return a specific cell value from a range based on an initial desired value. It looks like this: DesiredValue typed in cell: | 125| Search Range: line1 RangeA|RangeB line2 1 | 50 line3 51 | 100 line4 101 | 500 line5 501 | 1500...

This is a very basic Excel problem. I have a number that I'm starting with, 1000, in cell B2. The number I'd like to increase to is 135,000, located in B40. How would I go about using a formula to increase from 1000 to 135,000 in a way that would...

I need to compare two dates to see if they are equal from a list of dates in a column. Eg. Sheet 1 Column A Date 1 Date 2 Date 3 Date 4 Sheet 2 : Row F: Date 5 I want to see if date 5 is equal to...

I have a table that uses following formula to calculate +357 to display "NO" or "YES" =IF(AND(TODAY()<M3+357),"NO","YES") My problem is; I want this cell to display nothing when the reference "M3" cell is blank. Help would be greatly appreciated....

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...

First stackoverflow question, so hopefully I've done this correctly! I've had a good look on various sites ranging from Mr XL to Chandoo, and a good look on here too, and I've not come up with a solution yet. I've got a spreadsheet which tracks contracts, but I need to...

Age Qualification 19 Higher Secondary School 24 Graduate 25 Post Graduate or above 21 Graduate 17 Higher Secondary School I am trying to get Minimum age of graduate student which is 21. The incorrect formula i used is =MIN(IF($B$2:$B$6="Graduate",$A$2:$A$6)) which gives 17 as min value....

I have a table that is pulling thousands of rows of data from a very large sheet. Some of the columns in the table are getting their data from every 5th row on that large sheet. In order to speed up the process of creating the cell references, I used...

I'm trying to rank values and have managed to work out how to sort ties. My data looks at the total number of entries, ranks based on that and if there is a tie it looks to the next column of values to sort them out. However, I have two...

I have Inventory data that is in the following format: Column D | Column E | Column F Pack Qty | Pack Price | Total Qty This is followed by multiple rows with various numerical values, with the odd blank row. To calculate the stock value of any particular product/line,...

I have this table in a Excel sheet: I would like to create a Pivot Table to have this outcome: Do you guys have any idea how to do it? Thank you!...

I require an excel formula to check if any words are being duplicated in any column in a row. My sample row is below columns separated by a ','. Column No: E221, F221, G221, H221 Column Text: Sam, John/Sam/Smith, Smith, Kyle Above Sam & Smith names are being repeated so...

I have a formula that I'm using in Excel to determine what the payback of a value is after "X" of months. But I'd like to modify the formula to display 0 or $0.00 if the result of the formula is less than $0.00 The current formula is =C2-(C2*(G2/24)) But...

I need a formula that can find the date in a cell next to another that has the first number in the column. In column A I have dates and in column B onward I have stock prices. The dates goes back to 1990 daily, but not all the stock...

Hi all I have been trying to fin out how to use AND and OR together on the same formula. I'm trying to resolve the following issue : The person will get a discount only if they are working on the Cerrovial project or if they are working on the...

Update Below -- 6/4 I have two worksheets in excel. One is a list of donors with their check#/amount/ Donor ID ( "donations" worksheet) and the other is a copy of the accounting info with Donor ID/check#/amounts (quickbooks worksheet). Quickbooks does not have the DonorID's filled in yet. The issue...

I composed the next Worksheet in order to count my Workhours. Other solutions proposed over the internet would not work, since the Workhours amount per day was not fixed at first. The problem makes its appearrance sometimes when the "Total Work Hours" equal the "Normal Work Hours". When i try...

Ok so i have been trying to do this and i pretty much keep on failing :p Basically what i want to do is take the date 10/1/2013 and turn it into 201310. I tried to concatenate using left and right. tried to make it into text using left and...

I have some cells with multiple names in them as so: I have some more cells below where I need to count how many times each name appears in the column For example, in this column Ben should appear 4 times and Matt should appear 4 times. How should I...

I've been trying to solve an issue for a while now and I'm just not getting the results I need. I have a spreadsheet that captures labor hours at a mechanic shop and I want to find out how many hours of labor per mechanic. The system uses open work-orders...

I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

I have developed a worksheet for my office work. I have also developed the formula that will be used in it. However, the formula is quite long and i want to be shortened. Here is the detail for the formula. =IF( AND( [Date]>=DATE(2014,7,1), [Date]<=DATE(2014,7,31), [Description]="Diesel Oil"), [Value Ex. Sales Tax]*'Tax...

------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith--------------08/08/2014------------01/01/2015 3---John--------------Smith--------------08/11/2014------------17/11/2014 4---John--------------Smith--------------06/06/2014------------23/12/2014 5---Abel--------------Jones--------------14/05/2014------------29/04/2015...

When I enter the following formula in a cell it returns TRUE, but when I enter this into a new conditional formatting rule, it tells me I have an error. This is the formula: =ISERROR(MATCH(Table2[Website];Table13;0))...

I use filters a lot. Filter > contains option But it is slow because everytime i need to do several clicks. So, my question is, is there a shortcut to open filter, drop down, contains window?...

I have Excel Sheet with some data in it. Link will get you to the spreadsheet. I need to build a report that extract information from each months worksheet. All the worksheet is under same workbook. It can be done with VLOOKUP, INDIRECT and COUNTIFS but I am still new...

A B C 1 Fruit Color Meat <- Column Header 2 Banana Red Pork 3 Apple Black Chicken 4 Orange White Beef From the table1 above to table2 below: A B 1 Name What? <- Column Header 2 Banana Fruit <- Formula should return these values, based on table...

I am having troubles getting Excel to calculate a formula using IF and OR. I have a spreadsheet where I need the information from K6*I6 to be nested. I need it to differentiate the percentage that's listed in cell K (it will be either 12.5% or 25%) and then calculate...

To explain it in the easiest way possible: | 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | ... needs to look like: | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8...

I've 2 columns A and B. A contains names and B contains the count of those names. Ex: let A1 have value "fruits". Then B1 must have value 1 because this is first time "fruits" is present in A. Next A2 has "flowers", and B2 will have 1 against "flowers"....

For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result...

I have been working on a spread sheet for work for about a week and a half now and have been stuck in the same place. I am working on something for payroll purpose. So A1 would have an employee name as would the rest of the column. B2 would...

I am attempting to accomplish a look-up in Excel and I can't seem to find anything about it online. Admittedly, I may not be searching for the answer in the right way. So if this is redundant and has already been answered I apologize upfront. I'll attempt to describe my...

I have googled for hours, not being able to find a solution to what I need/want. I have an Excel sheet where I want to sum the values in one column based on the criteria that either one of two columns should have a specific value in it. For instance...

I have a table in excel with two columns [RunningTotal] and [Change]. I have a formula like this for [RunningTotal] =IFERROR(OFFSET([@RunningTotal];-1;0);100)+[@Change] Its a table with two columns, one for a running total and the other for the change. The IFERROR is for the first row since it cannot be offset...

Here is what i exactly need to do. I have an excel workbook with many sheets. I need to work on two sheets- Attributes_Master and Domain_Details. Both have a field in commmon - Domain but not in same order. I need to add a hyperlink in Domain column of Attributes_master...

What I'm looking for is a formula that will do the following: (I'll be using the first row for this example): Firstly check cells E:J for cells containing a 5. If all cells contain a 5 it will check the person's name and then add this to the box in...

I'm trying to count the number of activities each organization has done in my dataset. Right now, each row represents a single organization's list of activities. The following formula accurately finds the number of activities per organization: =IF(COUNTA(A1:H1)=5,"yes") However, I now need to group organizations by amount of activities (ex:...

Here is my current formula I use to pull the latest live date for a customer out of a web query. {=LARGE( IF(Table_owssvr_1[HQ Name]=B1,1,0)* IF(ISNUMBER(Table_owssvr_1[Live Date]),Table_owssvr_1[Live Date],0), 1)} B1 is the name of the customer. HQ name is column A and contains customer names. This formula will give me the...

I am trying to work out the ranking of top 3 users at different organisations and have the data presented horizontally for each user so it can be inputted into our email system to personalise emails. I am able to create a ranking vertically but I am not sure how...

I want to color the interior of a cell according to it's content, however when I'm accessing its value I'm always getting '1.0', the value is calculated. Colorisation code : def _colorizeTop10RejetsSheet(self): """Colore les position de la page "Top 10 Rejets" """ start_position = (5, 12) last_line = 47 for...

So I have some rows of data and some columns with dates. As you can see on the image below. I want the sum of the week for each row - but the tricky thing is that not every week is 5 days, so there might be weeks with 3...

This is a fairly simply formula that I need syntax for. I do not know the syntax that Excel will accept. Formula: I want to return a value of "0" if the target cell has a value of "2" or less. If the value is greater than "2" I want...

I have a dataset as below: I want to apply conditional formatting that will format a cell if the value in that cell is not 100, 50 or 0 (ie I want it to pick up A5 and format it) I'm new to conditional formatting and haven't been able to...

I'm having no luck being able to do sumifs or countifs on a cell range that is linked to an MS SQL server 2008R2 view when using the date column as a criteria. If it is done on a native table link they works fine but it appears that the...

I have a table like below ID StarDate EndDate 123 6/5/2015 15:54 6/12/2015 15:54 124 6/6/2015 15:54 6/8/2015 15:54 125 6/7/2015 15:54 6/9/2015 15:54 126 6/8/2015 15:54 6/11/2015 15:54 127 6/9/2015 15:54 6/15/2015 15:54 I want to get the total count of aging without using any additional column 0-30 =COUNTIFS(A:A,">124",A:A,"<127",TODAY()-B2:B6,"<=30")...

I have used this array formula to find true/false. The formula is only considering the text in the first cell {=isnumber(search(G2:G7,A1))} ...

I have the following data structure: As you see in column J, I am trying to merge data into one column from columns A & C & E & G. I am using this formula: =IF(ROW()<=COUNTA($A:$A);INDEX($A:$C;ROW();COLUMN(A1));INDEX($A:$C;ROW()-COUNTA($A:$A)+1;COLUMN(C1))) and I get the values in column K as you see. Currently this formula...

I can't quit figure out what I am doing wrong here, I know how to do the formula in multiple steps but can not figure out how to do it in one step all together. So what I am trying to do here is in column A there is numbers...

Here is what I am trying to do, I have a sheet that has a list of names with no duplicates that varies in length. I would like to have either with a formula or vba sub, to have the next row copy the original list of names and offset...

There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

I am trying to create a dynamic table - I have tried a Pivot Table, but cannot get it to work. So I thought that maybe it could be done with an IF-statement, but that did not work for me neither. Basically, I have 2 tables, 1 table containing the...

As the title states, I am trying to count across cell ranges on a separate sheet but cant seem to get it to work. Any help would be greatly appreciated. =COUNTIFS(MONTH(original!A2:A58),"1",original!D2:D58,"=East") ...

What formula would I use to do a sum of the contents of a range of cells, provided that the contents of an adjacent cell equal another cell. The example I could give is, if I have a set of invoices which quote a purchase order, I want to see...

I have one example table with the following data in Sheet1 with the following random data ------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith--------------08/08/2014------------01/01/2015 3---John--------------Smith--------------08/11/2014------------17/11/2014 4---John--------------Smith--------------06/06/2014------------23/12/2014...

How would I go about turning the excel formula =B4/B5 into vba code? So far, this is my code which does not work of course: ActiveCell.FormulaR1C1 = "=B4/B5" As you can tell, I am not very familiar with R1C1. If needed, I have created a function Col_Letter() which takes a...

I have been using =IFERROR(INDEX($F$13:$F$1353, MATCH(0,COUNTIF($X$12:X12, $F$13:$F$1353), 0)),"") and it work perfectly in order to bring unique values from a column with repeated values. However I use a filter on the repeated values column "F" and when I deselect any item from the "F" filtered column I would like to...

I am running the following Sub, which inside other two subs: Sub SalvaDadosLogin() 1: ThisWorkbook.Worksheets("Registro").range("a11") = loginComdinheiro 2: ThisWorkbook.Worksheets("Registro").range("a12") = senhaComdinheiro 3: ThisWorkbook.Worksheets("Registro").range("a14") = salvaSenhaComdinheio 4: ThisWorkbook.Save End Sub Sometimes when I run this sub my VBE starts executing a user defined function (after running the line 1). Other times...

I'm trying to write a linear regression function that dynamically references columns, can handle #N/A values, and will function as additional rows are added over time. Here is a sample dataset: Date Value 1 Value 2 1/2/1991 #N/A #N/A 2/4/2002 276.36 346.31 1/7/2003 252 350 1/21/2004 232 345.5 1/6/2005 257...

I have my Excel formula found below does this: Scenario: I have three products, product A, product B and product C. I want to be able quickly to calculate the actual revenue. The data structure is in the image. My top-most row is month. For each month I have four...

In Microsoft Excel I want to have a pivot table that uses a calculated field for a row. I would like the calculated Row to exlude the current year. So I want two summary rows one with current year and one without. The attachment in orange shows my desired result....

I am normally well versed in Excel but I am having a blank on this one. I have a bunch of formulas eg: VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 13, FALSE)+3162 VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 22, FALSE)+18 VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 31, FALSE) VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 40, FALSE)+8.6 and so on What I need is...

I have 2 sheets in a workbook, one "Summary", the other "Enrolments". In "Enrolments" I have a list of individual entries, outlining what classes a student has enrolled in, all identified by their ID number and course code, for example: A B 1. ID Course Code 2. 123456 225.885 3....

Here is the formula I am trying to run: =IF((FIND("(",A24)-1),LEFT(A24,FIND("(",A24)-1),A24) I thought that the last A24 meant that the cell value would be copied if the IF condition is false, but it is not being copied. How would one make that happen? Thanks! ...

I needed some help with Excel functions as I am not really sure how to achieve this. Basically, this picture shows my data structure: I am aware that I can use Index& Match excel functions together for columns but I am not sure if it is possible use Index&Match together...

I have a Macro in my Excel and this Macro writes a formula to one cell in my Excel sheet. The code of my Macro that does this is like below: Range("F10").Select ActiveCell.FormulaR1C1 = "=SUMIF('1'!C2,C7,'1'!C[2])" and the Formula produced for the F10 cell is like below: =SUMIF('1'!$B:$B,$G:$G,'1'!H:H) Can someone help...

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...

I have a very large excel function which has worked for me in the past (we have been using this spreadsheet for over a year), but now it is not working. Here is a subsection of the formula =sum(A1,B2,C3,D4,E5,F6,G7... ZX529,ZY827,ZZ912) Why am I having this issue?...

Is there any function in Excel (2010) which decode unicode to ascii text? One column in my sheet contains word/sentences in Portuguese. For Example: Esse Jean Ã© feio nÃ© Should become Esse Jean é feio né Ã© - This letter in the text is not Portuguese, is there any function...

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...

I'm trying to conditionally format a column so the minimum, unique value is highlighted. So, for instance if the column looked like: 1 1 2 3 2 would be the minimum of the unique values. So, I'm trying to conditionally format the cell containing the number 2 in this example....

I really need help with the following situation . I'm trying to use a vlookup or index that I believe is really advance for me , unfortunately I don't have the knowledge how to attached an excel file here so I will add a image I have a huge data...

Friends, I need to list all the unique entities (items) in a Excel spreadsheet, how to? E.g. **List1** 1 1 1.1 2 a a aa b c c And I need a certain function that returns: **Entities** 1 1.1 2 a aa b c Then I can count the occurence...

I have the following excel table ------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith-------------10/09/2014------------24/11/2014 3---John--------------Smith--------------20/11/2014------------31/01/2015 (Occasionally I have duplicate names on a spread sheet). I am creating a formula which determines the number of days between the earliest start date and latest end date from two date...

For example: On my first tab Last visit, I have columns, Customer Name, Customer # and Scheduled date On my second tab, All visits I have Customer Name, Customer #, and Visit Dates. Now, this tab shows all of the visit dates (even the last one that is on Last...

I've been having a lot of trouble lately with this and haven't been able to find a solution yet. Here's my problem: I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables I have to find the "sell price" in the first table. In order to do this I...

Can Not Get My VLookUp In Excel To Return The Requested Data I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet. All the formatting is "General" =VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE) It just keeps returning me #N/A...

I have a excel spreadsheet that has been exported from a kendo-ui grid. I have zero experience with excel and I need help turning this data into a excel chart. the attached spreadsheet is what i am working with. Every subdivision name is its own series with (sqft,price) points. is...

I'm working on an excel project that helps visualising blood work in generating a chart for each single value. As I'm trying to prevent empty values in charts I added two sheets that actually manage the data ("#data") and charts ("#charts"). In "data" one enters values and dates, in "charts"...

I have a curious question about using the "OR" in an excel SUMIF. Basically, in a SUMIF(A:A,"Cat" OR "Dog", C1:C10) How can I use the OR function in an excel SUMIF function?...

I am using the following formula: =IF(ISERROR(LOOKUP(2^15;SEARCH(MID(A1;1;9);$D$1:$D$100)));"No";"Yes") this is working perfectly! Question: I want to search within columns $D$1:$E$100 and not only one column D. How can I modify this to search in two columns?...

In an excel sheet I am trying to retrieve how many entries of a given name occur within a month. I have the date with my month stored in N48 My spreadsheet would look like: B AE Test 06/04/15 Testing 06/05/15 Test 06/08/05 Name2 06/04/15 Name2 06/02/15 So far I...