|
ShimaTetsuo posted:Yes, pivot tables will do what you want. If it didn't come out correctly on one example, it may have to do with some bad labeling on your part, like different spellings (or extra spaces) for the same logical category in column A, or confusion between numbers being recognized as actual numbers or as strings. In my example Column B would be the ID column and Column A would be the column to be summed based on column B. Idk what exactly was up with my small set of test data, but it seems to be corrected I guess. It still makes me nervous though using a large dataset that I can't easily proof.
|
#
?
Apr 13, 2014 17:04
|
|
|
|
| # ? May 11, 2024 13:52 |
|
|
Tots posted:This should be easy, but I can only find solutions if I want to define the criteria manually which I can't. Have you tried =sumifs()
|
|
#
?
Apr 13, 2014 22:21
|
|
|
Tots posted:In my example Column B would be the ID column and Column A would be the column to be summed based on column B. Idk what exactly was up with my small set of test data, but it seems to be corrected I guess. It still makes me nervous though using a large dataset that I can't easily proof. Ok, I misread but the point still stands (in reverse): do you expect the sum for B=5 to be 46? Because depending on how you import it, it may keep "02" as a string and you will get 44 instead.
|
|
#
?
Apr 14, 2014 01:07
|
|
|
edit: deleted, I just figured this out.
greasyhands fucked around with this message at 21:30 on Apr 14, 2014 |
|
#
?
Apr 14, 2014 21:11
|
|
|
ShimaTetsuo posted:Ok, I misread but the point still stands (in reverse): do you expect the sum for B=5 to be 46? Because depending on how you import it, it may keep "02" as a string and you will get 44 instead. I see what you're saying. Those are just numbers I made up. The real dataset I'm using is normalized.
|
|
#
?
Apr 14, 2014 21:52
|
|
|
That is some pretty timely thread activity! I have to (have to) make a pivot table. I hate pivot tables though and like a jerk refused to learn their ins and outs because my first experience with them was lame. My initial instinct was to use a collection linked to a dynamically data validated change event with keen conditional formatting and some already written VBA to create tables and graphs etc., so how can I still get that same usability from a pivot table because I'm bad at them  does anyone have a really in depth pivot table tutorial that goes beyond the basics?
|
|
#
?
Apr 17, 2014 03:48
|
|
|
FAN OF NICKELBACK posted:That is some pretty timely thread activity! I have to (have to) make a pivot table. I hate pivot tables though and like a jerk refused to learn their ins and outs because my first experience with them was lame. ExcelIsFun on YouTube has tons of videos. Here's one on slicers and Pivot Tables https://www.youtube.com/watch?v=zgt7SdrYJqg
|
|
#
?
Apr 17, 2014 04:10
|
|
|
Question: I am putting this formula into a spreadsheet: =($H$110-H2)^2 How do I make it so that the "H2" part changes the "2" to the row the formula finds itself in? Thanks in advance.
|
|
#
?
Apr 23, 2014 02:21
|
|
|
This seems to work. =($H$110-(INDIRECT(CONCATENATE("H",ROW()))))^2
|
|
#
?
Apr 23, 2014 03:07
|
|
|
Toe Rag posted:This seems to work. It does! Thank you!
|
|
#
?
Apr 23, 2014 03:37
|
|
|
Kraus posted:Question: You shouldn't have to make any change. The $ in a cell address prevents it from changing. So the $H$110 will always point to the same cell no matter where you copy and paste it to. H2 will change columns and rows as your paste cell moves columns and rows. So if you initially have the formula in A1 and paste it into B2 the resulting formula would be =($H$110-I3)^2 because you moved right 1 column and down 1 row.
|
|
#
?
Apr 23, 2014 22:22
|
|
|
Bar chart question. Let's say I had custom colouring to each bar. One's red. One's blue, etc. All custom colouring. Is there any way to make Excel "remember" my bar chart colouring if the data changes and the categories shift positions? I'm asking because I have to re-colour my bar charts whenever the data changes. Just wondering if there was a means of avoiding this.
|
|
#
?
Apr 27, 2014 23:26
|
|
|
melon cat posted:Bar chart question. You could do it with a macro. Here's an example of someone looping through the series (lines on a line graph, or your bars). http://stackoverflow.com/questions/21165581/vba-looping-through-all-series-within-all-charts You could modify that to include lines checking the series name and if it matches "X" make the bar/line blue. Here's the series object members from MSDN. http://msdn.microsoft.com/en-us/library/office/ff840677%28v=office.15%29.aspx
|
|
#
?
Apr 28, 2014 21:49
|
|
|
Say you've got a dataset that looks like;code:
|
|
#
?
May 1, 2014 16:01
|
|
|
Total Meatlove posted:Say you've got a dataset that looks like; You can add another series to the graph which consists of only the first five points, add a trendline and set the "forward" option to however many periods, then hide the markers from the new series. Alternatively, use the TREND array function which allows you to set "known_y's" and "known_x's" to the first five points, then set "new_x's" to all of your x's, then just plot the result.
|
|
#
?
May 1, 2014 17:39
|
|
|
Old James posted:You could do it with a macro. Total Meatlove posted:Say you've got a dataset that looks like;
|
|
#
?
May 2, 2014 02:50
|
|
|
melon cat posted:Wow. It's a lot more involved than I anticipated, but I'll give it a shot. Thanks! Sorry, just realized another option is to create a separate data table which then uses lookups to populate the series values. That way the series always has the same name and just the values change. Example below would always keep Hat as series 1 (which you could color Red) and then update the dates and sales amounts as the underlying data changes. code:
|
|
#
?
May 2, 2014 16:59
|
|
|
Problem: ROW 1 - text1 ROW 2 - text2 I want this information merged in a cell with the "-" as seperator like this: text1-text2 How can I do this ?
|
|
#
?
May 7, 2014 09:13
|
|
|
Assuming cells are A1 and A2: =A1&"-"&A2.
|
|
#
?
May 7, 2014 09:39
|
|
|
Ragingsheep posted:Assuming cells are A1 and A2: =A1&"-"&A2. I figured it out. liddl ninja fucked around with this message at 11:36 on May 7, 2014 |
|
#
?
May 7, 2014 10:03
|
|
|
Until the last month or so, when I wanted a new line inside a cell alt+enter would do it. Now that no longer works for some reason. Anyone know of why this would be, or another way to insert a linebreak? Copying from another cell with a linebreak and re-writing the data is getting old.
|
|
#
?
May 10, 2014 23:50
|
|
|
Xenoborg posted:Until the last month or so, when I wanted a new line inside a cell alt+enter would do it. Now that no longer works for some reason. Anyone know of why this would be, or another way to insert a linebreak? Copying from another cell with a linebreak and re-writing the data is getting old. Not sure why that happened, but this will work. ="Line 1"&char(10)&"Line 2"
|
|
#
?
May 12, 2014 01:51
|
|
|
I've got a tricky Excel problem that might or might not have a solution. I'm copying in a long list of blocks of cells from an HTML table that our database program outputs. I want to sort the BLOCKS of cells by the contents of the 2nd half of one of the cells in each block-- that is, the cell has "NAME (INITIALS)" and I need to sort by the "(INITIALS)" portion only (and then use a secondary sort on a different cell's contents). I have no idea how one goes about causing an entire block of cells, 9 rows deep, to be sorted like this. Is there a way?
|
|
#
?
May 23, 2014 18:34
|
|
|
I have 2 columns of data, column A has items and column B has quantities. There are many many duplicates. I want a function to sum all of the quantities of each item, IE the find total number of "item1". Using =SUMIF(A3:A200,"Item1",B3:B200)I get close, but it only counts how many times item1 appears in column A.
|
|
#
?
May 24, 2014 19:48
|
|
|
2 problems that I'm trying to determine whether they can be solved via Excel: 1) Take a list of 6 random numbers. Divide them as evenly as possible while keeping the contents whole. For instance: code:2) Take a numbered list, make it a dropdown list in a cell, then have it call back to one of many lists similar to issue 1). I've spent 4 hours trying to even determine whether this can be done without macros or not, and I'd settle for "Yes, this can be done" or "no, it can't be done with/without macros" at this point. Professor Moriarty fucked around with this message at 00:33 on May 25, 2014 |
|
#
?
May 24, 2014 23:35
|
|
|
I have the following table and I want to give a certain number and have excel match it with the closest number that is larger that my specified number and then return the value in the second column. code:edit: got it. Ragingsheep fucked around with this message at 01:21 on May 27, 2014 |
|
#
?
May 27, 2014 00:51
|
|
|
There are a bunch of full on paragraphs of data in a spreadsheet I work with. Control F works OK, but since this keeps getting added to/edited I decided to build a userform as a search engine. I know I can create an array for the "tempstring = replace" bits, but it's not my primary concern right now. I also like seeing everything at a glance since I'm not done adding/editing that portion. Since I have little to no search engine programming experience I'm wondering if anyone has any other suggestions for the below code. It does a pretty good job, but I'd really like to just generally make it as solid as possible (and hopefully pick up some new tactics along the way). Think of the raw data as three columns: "Project Name," "Project Details" and "Project Updates" Any suggestions? code:
|
|
#
?
May 27, 2014 23:01
|
|
|
Given a mean and a standard devation, is there an easy way to generate a normal distribution graph in Excel? The only way I did it is the following pants-on-head retarded technique: 1) use the mean and standard deviation to generate a fake distribution of data across the cells 2) use the fake data to generate the graph There HAS to be a less rear end-backwards way, but an hour of googling could not uncover it for me.
|
|
#
?
May 28, 2014 05:01
|
|
|
I didn't completely follow everything you were trying to do. But have you considered using the .Find method in your code? It is the CTRL+F function. You can use that with your search terms and loop through to get the list of cells that contain the words and from that then modify those cells as you see fit. http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx
|
|
#
?
May 28, 2014 17:33
|
|
|
FieryBalrog posted:Given a mean and a standard devation, is there an easy way to generate a normal distribution graph in Excel? This is basically what I do: http://exceluser.com/excel_dashboards/normalcurve.htm Old James posted:http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx I'm not sure how to do what I currently have with .find without a "do while" and creating error trapping. I try to avoid all that because I always end up creating more points of failure (I'm more determined than capable :/ ). If you've got a good direction for me to go with though, let me know. If I can figure that out it would probably be faster than looping as much as I am. Also, sometimes I still get wonky matches due to a word or two being repeated a lot in the notes in a less relevant topic -- though I usually only have to try once or twice to find the right match (even if I didn't know that's what I was looking for). Here's what the code currently does for reference: -Gets rid of duplicate user-entered keywords, punctuation, superfluous spacing and standardizes common contractions so that if a paragraph has "Can't" and you use "Can not" in your keywords it will still recognize the match. -Retains the cell address of best matches (adding each time it finds a better one) in order of worst to best, so that if I want to create a button to flip through all the matches in a meaningful order, I can.
|
|
#
?
May 28, 2014 18:26
|
|
|
How would I go about averaging together column values based on another column's values?code:
|
|
#
?
Jun 2, 2014 07:42
|
|
|
Armchair Calvinist posted:How would I go about averaging together column values based on another column's values? averageif or averageifs should work fine.
|
|
#
?
Jun 2, 2014 07:51
|
|
|
Darth TNT posted:averageif or averageifs should work fine. Thanks! Just confirming that I'm not crazy, haha.
|
|
#
?
Jun 2, 2014 08:18
|
|
|
I get a report every week that looks like this:code:Is it possible to have excel move the data for each student into a single row, rather than having them in a row per class?
|
|
#
?
Jun 2, 2014 16:15
|
|
|
Zaffy posted:I get a report every week that looks like this: What do you want in that single row? A pivot table can definitely consolidate that, or it's possible to build some simple sumifs in another sheet to summarize the data for you.
|
|
#
?
Jun 2, 2014 16:57
|
|
|
Another question! How would I go about doing this? code:
|
|
#
?
Jun 3, 2014 02:31
|
|
|
Armchair Calvinist posted:Another question! As far as I know there is no formula to count the number of unique entries. The ways I do these things is to either use a pivot table to get the number of unique ideas by putting them in a list or using another column and using a formula like if(A2=A1;0;1) and then summing up that column. Of course the second version only works if it has been properly sorted in an ascending fashion!
|
|
#
?
Jun 3, 2014 08:24
|
|
|
Armchair Calvinist posted:Another question! This is easy to do if you split it out. Assuming that the "ID" is in cell A1: in D2 enter the formula '=1/COUNTIFS($A$2:$A$7,A2)' and drag down. Then in cell D8 '=SUMPRODUCT($C$2:$C$7,D2:D7)/SUM(D2:D7)'
|
|
#
?
Jun 3, 2014 08:46
|
|
|
I've got a file containing the following type of data. Each element exists in its own cell. I'm trying to extract the first name, last name, suffix, and email address.code:But, the name of the person is always the first line of each block. I'm thinking it may be Create a macro to go row by row, extracting data from the first populated cell after an empty cell. Each element (First Name, Last Name, Suffix, Email Address), should be copied into their own cells, row by row. I'd then need to skip down until an email address is found, and extract that into the end of the same row containing the full name data. Does that sound doable? Might there be a smarter way to go about it? me your dad fucked around with this message at 17:01 on Jun 3, 2014 |
|
#
?
Jun 3, 2014 16:58
|
|
|
|
| # ? May 11, 2024 13:52 |
|
|
if email is always last in the block i'd loop through twice dim a couple of sheets ws1 and ws2 dim rows(1 to more than enough) as double dim hits as double first get all the interesting rownumbers in ws1 pre:
set ws = activesheet
hits = 0
for i in 1 to lastrow
if cell(i,1).value <>"" then
if cell(i-1,1) = "" then
hits = hits + 1
rows(hits) = i
else if cell(i+1,1) = "" then
hits = hits + 1
rows(hits) = i
end if
end if
next
pre:
set ws2 = activeworkbook.sheets.add
for i in 1 to hits
if i-(2*(i\2)) = 1 then
ws2.cell(i,1).value = ws1.cell(rows(i),1).value
ws2.cell(i,2).value = ws1.cell(rows(i),2).value
ws2.cell(i,3).value = ws1.cell(rows(i),3).value
else
ws.cell(i-1,4).value = ws1.cell(rows(i),1).value
end if
next
You need to have at least one empty row at the top of the original sheet for it to work. Edit - mod operator updated sofokles fucked around with this message at 20:07 on Jun 3, 2014 |
|
#
?
Jun 3, 2014 20:00
|
|
