Tag Info

Hot answers tagged

9

Highlight the first and last values along with the blank cells, this is your range. Now you can go to the ribbon at Home - Editing - Fill and select series... It should predict your step value for you: results: Or, you can do the same and specify your own step value.


3

Assuming your data is in columns A to C, starting at row 2 then you can use this "array formula" in D2 =SUM(IF(FREQUENCY(IF(B$2:B$16<=B2,IF(C$2:C$16>=B2,MATCH(A$2:A$16,A$2:A$16,0))),ROW(A$2:A$16)-ROW(A$2)+1),1)) confirmed with CTRL+SHIFT+ENTER and copied down the column Explanation: This is a common technique used to get a count of different values ...


3

The easiest and fastest way to do this is to create a Microsoft Word document and use the Mail Merge feature to read your Excel spreadsheet. It took me about three minutes to create a template file that would meet your needs. Every student ends up on an individual page and you can print all of the reports at once. In addition, you never have to change the ...


2

Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Long, st As String, J As Long, k As Long Dim CH As String If Intersect(Range("A:C"), Target) Is Nothing Then Exit Sub CH = Chr(1) I = Target.Row J = Cells(Rows.Count, 1).End(xlUp).Row st = Cells(I, 1).Text ...


2

My suggestion is a combination of list and vlookup() in a separate cell. As with most things in Excel, there is probably more than one way of doing it, but this is the first thing that comes to mind. I.E. limit answer to text in a defined list using data validation. To the right of each item, specify the number you want to translate to. Then, in a ...


2

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.


2

Credit to http://superuser.com/users/166431/michael-field via Easiest way to open CSV with commas in Excel, the client needs to uncheck 'Use system separators' in their Excel advanced options for the 'sep=,' command to work.


2

The simplest way I know (I'm sure there are others) of doing this is to utilise the free Excel addin from Microsoft, PowerQuery. You set up each data table and then merge queries of all tables to produce the master. As you change the data tables, a simple refresh of the master table will update it and include everything you need. UPDATE: I'm rather rusty ...


1

If you are only putting names in columns V,Y,AB,AE,AH and AK then you don't need those cells in the COUNT function (names won't be counted by COUNT function, it only counts numbers), so you can reduce the size of the formula by removing those....and as you are returning text values you can simply concatenate a "null string" to the end of the formula to ...


1

Add a new sheet named roomtype to the workbook and in column A enter the room numbers. In column B enter the 1 or 0 for each room. Then go back to the sheet where you want the formula. =VLOOKUP(A3,roomtype!A:B,2,0) where A3 is the cell that has the entered room number. The first parameter to VLOOKUP is a value specifying what value to look up. The ...


1

Your question is very unclear, but let's assume the data looks like this A B C D Adam 21 Bill Bill 22 Dick Carl 23 Carl Dick 24 Adam And you want to take the value in column C, find it in column A and return the value in Column B. This is vlookup D1 =VLOOKUP(C1,$A$1:$B$4,2,0) D2 =VLOOKUP(C2,$A$1:$B$4,2,0) D3 =VLOOKUP(C3,$A$1:$B$4,2,0) D4 ...


1

Steven, here is one possibility if you don't want to go the macro route. Have your master list of titles as a named range with the unique number next to it (e.g., A. Approaches to Sustainable Agriculture, B. Economic Sanctions in U.S. Foreign Policy, A1. Trials of the Century). Make sure you have enabled autocomplete in cell (in backstage view, Options, ...


1

You need to create a master list containing all possible values (e.g. 1 to 20 in your case) and look for the corresponding values from the original list: - fill numbers 1 - 20 in A2 to A21 - in column B put formula: =if(iserror(match(A2,<range1!column1>,0)),"",A2) - formula in column C: =iferror(vlookup(A2,<range1!columns1:2>,2,false),"") ...


1

Two problems.  (Or one problem with two heads.)  AVERAGEIFS help says: Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. Well, that’s a typo: “sum_range” should be “average_range”.  But, anyway, that’s your problem: your criteria range is 1×∞ (an ...


1

Open any excel file and choose the "save as" option. When you are in the save as window, below the "File name" and "Save As Type" options, there is an option that reads "Tools" with a small drop down arrow next to it. Click on this drop down arrow and then from the dropdown list, choose "General Options". In the window that pops up next, un-check the box ...


1

You can use SUMPRODUCT as long as you have the right syntax, like this =SUMPRODUCT((AH6:AS36="A")*AG6:AG36) in fact SUM also works but you would need to "array enter" if you use SUM, i.e. =SUM((AH6:AS36="A")*AG6:AG36) confirmed with CTRL+SHIFT+ENTER



Only top voted, non community-wiki answers of a minimum length are eligible