|
Probably not the best way but a quick (slow) and dirty way would be to loop down the rows. Grabing string and comparing them. If you can presort the list its easier. Grab string, split it down or just slice the bits you want to compare. Pop the relevant bits on a new sheet. Find any others with the same start/identifier. Add its value to the new one. Probably a quicker way of doing this but logically itd work.
|
# ? Sep 7, 2014 09:11 |
|
|
# ? May 20, 2024 16:17 |
|
I managed to get it done by sorting the list and using a "Do While cell < 5450" loop down the rows of the column containing the account/subaccount numbers. Slow, but it worked. Thanks!
|
# ? Sep 7, 2014 17:34 |
|
Is it possible to produce a graph with a dynamic rolling year range where the data is entered in columns rather than rows? e.g. A1 = Jan 14, B1 Feb 14 etc I can get it to work fine for a rolling year period by rows with the formula below, adding new months onto the subsequent row but I'd like to do the same where I enter data for the next month in the next column to produce the same results. =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) Most of the guides tell you how to do it by rows (which I can do), but I haven't been able to do it the opposite way..can anyone help?
|
# ? Sep 15, 2014 09:39 |
|
cadmaniak posted:Is it possible to produce a graph with a dynamic rolling year range where the data is entered in columns rather than rows? e.g. A1 = Jan 14, B1 Feb 14 etc Is there a reason you cannot transpose the data down? Excel can be finicky sometimes and there is no easy way jumping out at me. Even if you have to just use a temporary table as an intermediate.
|
# ? Sep 15, 2014 12:57 |
|
cadmaniak posted:Is it possible to produce a graph with a dynamic rolling year range where the data is entered in columns rather than rows? e.g. A1 = Jan 14, B1 Feb 14 etc Try replacing the $A:$A with $1:$1. Are you using that for a dynamic named range? If so, charts require a reference to the sheet and name, such as, =Sheet1!DynamicNamedRange.
|
# ? Sep 16, 2014 05:22 |
|
I made this code =IF(I65="";0;'3.063'!$J$44) and i want to drag/autofill for the cells down, but i want the IF function for the cell below to become like this: =IF(I66="";0;'3.064'!$J$44) I made the logical test look at the cell beneath (I66), but I can't make the value_if_false clause go for worksheet 3.064. Am i making any sense and is there a way to do this?
|
# ? Sep 16, 2014 14:10 |
|
Chutch posted:I made this code You need to use the INDIRECT function. I tested this in Excel, but typed it on my phone, so hopefully it works. =IF(I65="",0,INDIRECT("'"&3+(ROW()-2)/1000&"'!J44")) schmagekie fucked around with this message at 19:25 on Sep 16, 2014 |
# ? Sep 16, 2014 18:22 |
|
schmagekie posted:You need to use the INDIRECT function. I see what you did there. You made some kind of formula for calculating 3.004, right? As far as I understand that is not what I am looking for. Let me try this way, and tell me if I am wrong: Here's the syntax: =IF(logical_test;[value_if_true];[value_if_false]), where logical_test is: I65="" which means "I65 cell is blank" [value_if_true] is: 0 which means if I65 is blank then result is 0 [value_if_false] is '3.064'!$J$44 which means if I65 contains ANYTHING then it should result in the value from cell J44 (locked with $'s) from another worksheet named 3.064 I have a workbook with many worksheets with titles starting from 3.001 to approx 3.114. I can copy paste the above mentioned syntax and change '3.064' to '3.065', but thought there might be a simpler way by being able to drag and fill for all cells.
|
# ? Sep 16, 2014 20:52 |
|
Chutch posted:I see what you did there. You made some kind of formula for calculating 3.004, right? As far as I understand that is not what I am looking for. Let me try this way, and tell me if I am wrong: That code should work as intended if you paste it in row 65, then copy it where needed. For I116, it should grab J44 from sheet 3.114. schmagekie fucked around with this message at 21:23 on Sep 16, 2014 |
# ? Sep 16, 2014 21:18 |
|
Hi folks, wondering if you can clarify something for me because I have less excel experience than I would like. I'm trying to convert the following excel line into code and I really need to understand how it works. code:
What I am struggling to figure out is where the MAX comes into play, as I haven't used it before. How does it effect the calculation and in what order does it actually do things? Does it only use the the maximum value generated by the SUMPRODUCT? A Tartan Tory fucked around with this message at 09:05 on Sep 17, 2014 |
# ? Sep 17, 2014 08:50 |
|
A Tartan Tory posted:Hi folks, wondering if you can clarify something for me because I have less excel experience than I would like. SUMPRODUCT / N14 MAX of above result and 0 Result of above / R14
|
# ? Sep 17, 2014 11:05 |
|
headcas3 posted:The MAX will pick the max/largest number of SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14 or 0. The order it would perform the formula is: Aha, thank you so much! It was the 'or 0' part I couldn't work out, the order has helped a lot as well.
|
# ? Sep 17, 2014 11:29 |
|
Looking for some ideas on how to do the following: Find a matching value in Table 2 from Table 1, then populate a field in the row being checked in Table 1 with data from a field in the matching row in Table 2. Or visually, my inputs and desired output: code:
code:
Tots fucked around with this message at 18:04 on Sep 19, 2014 |
# ? Sep 19, 2014 17:05 |
|
Tots posted:Looking for some ideas on how to do the following: Congratulations, you discovered Index-Match on your own. It is a common solution when VLookup won't work. But if you don't already know VLookup that would have solved this particular issue. http://www.techonthenet.com/excel/formulas/vlookup.php EDIT: On reread that may have sounded sarcastic. The congratulations are real.
|
# ? Sep 19, 2014 18:47 |
|
Tots posted:E: I believed I solved this using: You found one of Excel's two most beautiful function combinations. It's vlookup for cool kids. Vlookup has limitations, such as that the index must be in numerical order. Index + Match gives 0 fucks about that and just throws down like ain't nobody got time for sorting. I really love index + match and it's absolutely how I'd do this.
|
# ? Sep 20, 2014 09:23 |
|
index + match is also faster than vlookup. This is helpful because if you're doing something in Excel where that speed difference actually matters, it's a good indication you're being asked to do something way beyond the toolset your company is willing to invest in and it's time to look for a new job.
|
# ? Sep 21, 2014 05:52 |
|
fosborb posted:index + match is also faster than vlookup. This is helpful because if you're doing something in Excel where that speed difference actually matters, it's a good indication you're being asked to do something way beyond the toolset your company is willing to invest in and it's time to look for a new job. This is exactly what I was thinking. I always use vlookup because years of having to deal with massive garbage spreadsheets have crushed my spirit and I simply cannot bring myself to care about how much better index and match are. Vlookup does not require the index to be sorted unless you use approximate match, as you would expect.
|
# ? Sep 21, 2014 23:00 |
|
Index + match has the advantage that since the array you're selecting from is a cell reference you can drag the formula if you want to get the data from adjacent columns. With vlookup you have to either type in the column number by hand as an integer or use a helper row. Plus the match column can be to the right of the data column. Not really dealbreakers for vlookup but they are a couple nice quality of life things.
|
# ? Sep 22, 2014 04:18 |
|
Isurion posted:Index + match has the advantage that since the array you're selecting from is a cell reference you can drag the formula if you want to get the data from adjacent columns. With vlookup you have to either type in the column number by hand as an integer or use a helper row. Plus the match column can be to the right of the data column. Not really dealbreakers for vlookup but they are a couple nice quality of life things. You can drag vlookup if you use the COLUMN() function, such as: =+VLOOKUP(D1,A:A,COLUMN()-4,0)
|
# ? Sep 22, 2014 05:13 |
|
schmagekie posted:You can drag vlookup if you use the COLUMN() function, such as: =+VLOOKUP(D1,A:A,COLUMN()-4,0) Good point, never thought of doing that.
|
# ? Sep 22, 2014 06:00 |
|
schmagekie posted:You can drag vlookup if you use the COLUMN() function, such as: =+VLOOKUP(D1,A:A,COLUMN()-4,0) That will work as long as you don't insert any columns.
|
# ? Sep 22, 2014 14:31 |
|
Old James posted:That will work as long as you don't insert any columns. Can you name the column and refer to the name? I'm not actually sure, I can't try this right now
|
# ? Sep 23, 2014 14:49 |
|
Nam Taf posted:Can you name the column and refer to the name? You can take the column of what you're trying to grab. =VLOOKUP($A2,Data!$A:$J,COLUMN(Data!B2),0) e: assuming the lookup range starts in col 1, otherwise you'll have to do a little thinking. schmagekie fucked around with this message at 16:41 on Sep 23, 2014 |
# ? Sep 23, 2014 16:33 |
|
schmagekie posted:You can take the column of what you're trying to grab. =VLOOKUP($A2,Data!$A:$J,COLUMN(Data!B2),0) =VLOOKUP($A2,Data!$C:$J,COLUMN(Data!D2)-COLUMN(Data!$C2)+1,FALSE) The +1 is necessary. Or just use Index & Match.
|
# ? Sep 23, 2014 17:54 |
|
So, I have a table with two columns of numbers. I sort by the first column first, and the second column second. I want it to sort like the above example, but it keeps sorting like the below example: Essentially, I want it to recognize that these are series of ID numbers (some multiple sequential, others single) and put them in order. I have everything formatted as text already, is there some other funky formatting thing I have to do?
|
# ? Sep 25, 2014 00:10 |
|
When you sort, make sure you choose the option 'Sort numbers and numbers stored as text separately'. Alternately, as long as your ids are always 3 and 6 digits you could just use new sorting column with: =LEFT(A1,3)&LEFT(B1,6)
|
# ? Sep 25, 2014 00:33 |
|
TheLastManStanding posted:When you sort, make sure you choose the option 'Sort numbers and numbers stored as text separately'. Yeah, I tried sorting with both options, and the "sort separately" option comes out less hosed-up than the other one, but still looked like the above picture. The other weird thing is that if I take one of the hyphenated numbers, paste it into an unhyphenated number's cell, then re-type the unhyphenated number, it will sort properly. If I just do a "paste formatting" it doesn't fix it, though. So, clearly there's some kind of weird formatting-but-not-formatting thing Excel is doing.
|
# ? Sep 25, 2014 00:44 |
|
Nam Taf posted:You found one of Excel's two most beautiful function combinations. It's vlookup for cool kids. I'm curious, what's the other beautiful function combination in your eyes?
|
# ? Sep 29, 2014 12:50 |
|
Byers2142 posted:I'm curious, what's the other beautiful function combination in your eyes? Sumproduct. It's mainly just one but you can use it as the foundation for a bunch of other logic and it's ridiculously powerful. It's basically a sum() that takes a bunch of multiplied terms (hence product). This essentially translates to doing an AND() across those terms except that you can also throw in multiplication factors rather than just binary terms. So for example, say you have 3 columns - A has a shop ID (shop 1, shop 2 etc), B has a type of product (Orange, Apple) and C has the quantity sold in an individual transaction: sumproduct((A1:A5='shop 1')*(b1:b5='apple')*(C1:C5)) will count the number of apples shop 1 sold across all transactions. Except you can just bolt in arbitrary logic in any of those. It just evaluates each set of brackets and multiplies then together. So for a row with 5 apples sold by shop 1, that resolves down to: sumproduct((1)*(1)*(5)) Except that's done for every line and summed together. It's sort of to sumifs what index/match is to vlookup. You can throw all kinds of stuff in the brackets and it'll take it just fine. It basically lets you do pivottables completely manually. I'll solve about 80% of my aggregation problems with it somewhere in the mix.
|
# ? Sep 30, 2014 00:10 |
|
Nam Taf posted:Stuff about =SUMPRODUCT() This is good knowledge, but Excel 2007 added =SUMIFS() which provides the same capability and is easier to understand. Excel 2007 also added =COUNTIFS() and =AVERAGEIFS(). EDIT: Oops, I thought you compared it to =SUMIF(). I need to find the article suggesting =SUMPRODUCT() is more flexible to find out what it can do that =SUMIFS() or =COUNTIFS() can not. Old James fucked around with this message at 16:21 on Sep 30, 2014 |
# ? Sep 30, 2014 03:57 |
|
Old James posted:This is good knowledge, but Excel 2007 added =SUMIFS() which provides the same capability and is easier to understand. I still don't find it as flexible, hence my comparison of it to sumifs as like index/match to vlookup. That may be my inexperience with the "ifs" functions though. E: a cursory Google of "sumifs vs sumproduct" suggests that the latter is indeed more flexible, somewhat due to its implicit array nature. Nam Taf fucked around with this message at 04:11 on Sep 30, 2014 |
# ? Sep 30, 2014 04:05 |
|
How do I count if out of all selected numbers if 3 or more of the exact numbers pop up
|
# ? Oct 23, 2014 22:54 |
|
What are you looking for here? The list of numbers that appear at least 3 times? The number of numbers that appear at least 3 times? The total number of occurences of numbers that appear at least 3 times? Something else?
|
# ? Oct 23, 2014 23:44 |
|
The numbers in that group that occur in that group 3 or more times, a list of the numbers that appear at least 3 times, and how many times they appear. I thought it was pivot tables, or do I have to format the thing into a long column and group them? I'm rusty.
|
# ? Oct 23, 2014 23:57 |
|
By far the easiest would be to put it all in one column and then use a Pivot Table.
|
# ? Oct 24, 2014 00:33 |
|
ZerodotJander posted:By far the easiest would be to put it all in one column and then use a Pivot Table. Any Easier way than CTRL X?
|
# ? Oct 24, 2014 00:59 |
|
Anybody have any good strategies for putting a linear trendline into a bubble chart? Doing it the naive way results in a flat trendline in the middle of my chart regardless of what data are in place. I'd like bigger bubbles to have more "gravity" in the trendline. Here's what my data looks like:code:
|
# ? Oct 30, 2014 17:59 |
|
Build your X and Y columns with a formula similar to this: =IF(C1=0,"",A1) and =IF(C1=0,"",B1) assuming column A is your X values, B is your Y values, and C is your Volume. e: sorry, replace the "" with NA(). so it should be =IF(C1=0,NA(),A1) e2: and honestly, you only need to use the #N/A values for the X column. It'll ignore the rest of the values for that point after that. fosborb fucked around with this message at 18:30 on Oct 30, 2014 |
# ? Oct 30, 2014 18:21 |
At work we typically shut down all systems around 11:30 PM but sometimes it goes past midnight into 2:00 AM. My Excel imports all of these times and now my boss wants to know the average time we shut down for the entire month/year. If I =AVERAGE("11:55 PM","12:02 AM","12:15 AM") for example I get 8:04 AM; not exactly what I'm after. How do I tell excel what I want? Tried putting appending the entire date but it is still confused. Chunjee fucked around with this message at 16:17 on Nov 30, 2014 |
|
# ? Nov 30, 2014 16:12 |
|
|
# ? May 20, 2024 16:17 |
|
Calculate shut down time as the difference between the DAY of turn on time (11/30/14 12:00:00 AM) and shut down day/time (11/31/14 2:04:13 AM). You will get values somewhere between .95 and 1.1. Average those results. Display as time (hh:mm:ss AM/PM) to get your average shut down time.
|
# ? Nov 30, 2014 16:20 |