It 100% does not work. I wish it was that easy. What I need is for the statement to update each time I change the data on my table and for that I need subtotal to work and subtotal/sumif do not work together
Sumifs() https://support.office.com/en-us/article/SUMIFS-function-c9e748f5-7ea7-455d-9406-611cebce642b
So what I'm trying to do is get M7 (and K7, I7, G7, E7) to work with my table. Currently M7 simply counts ALL cells that = 0 in M and 0 in O.... but I need those numbers to update each time I change the parameters of the table.
I've had a few beers now, and didn't read this link, but let me know if it helps... Because I think it might? http://www.mrexcel.com/forum/excel-questions/558912-sumif-index-match.html
Ok... I got one that I screwed up. It's basically a bunch of IF rules, and it's been a while since I've written this long of an IF statement, so I may be screwing something up that I'm forgetting within the functionality of IF statements. The columns are all spend % variances of actuals vs plan. Spend is Column I ROI is Column N For this one, I'm getting "Both" when I should be getting "Efficiency" because the spend variance is +12% but the ROI variance is +650% =IF(OR(ABS('SpendTable (Jan)'!I6)>0.2,ABS('SpendTable (Jan)'!N6)>0.2),"Both",IF(AND(ABS('SpendTable (Jan)'!I6)>'SpendTable (Jan)'!N6*2,ABS('SpendTable (Jan)'!I6)>=0.1),"Spend",IF(AND(ABS('SpendTable (Jan)'!N6)>'SpendTable (Jan)'!I6*2,ABS('SpendTable (Jan)'!N6)>=0.1),"Efficiency",IF(OR(ABS('SpendTable (Jan)'!I6)>0.05,ABS('SpendTable (Jan)'!N6)>0.05),"Both",IF(ABS('SpendTable (Jan)'!I6)>ABS('SpendTable (Jan)'!N6)*2,"Spend",IF(ABS('SpendTable (Jan)'!N6)>ABS('SpendTable (Jan)'!I6)*2,"Efficiency","Other")))))) Rules I'm trying to implement (in order, I want it to keep defaulting down if false) 1. Spend and ROI are both above 20% it should say "Both" 2. If Spend % is double ROI and more than 10% it should say "Spend" 3. Same as above only switch Spend and ROI it should say "Efficiency" 4. After these, if both are above 5%, "Both" 5. If ROI is >= 2x Spend then "Efficiency" 6. Vice versa 7. Everything else should be "Other" (FE: +2% and +4% would be "Other") Make sense?
Ok... Thanks man. That worked. Last one. I'm getting -60% ROI and +1000% spend, and a total positive variance, so it should say Spend since that's what drove the positive variance. Any ideas? More of a math question and I'm in a hurry.
Is it saying "Both"? Because you told it to look at Rule #1 first and they fit Rule #1. Did you want your rules in a different order?
Yes, that's why it's saying that. That question wasn't because the equation was wrong. I'm basically trying to automate a process that explains a revenue variance. The revenue variance can be caused by Spend, ROI, or both. Those are the rules I came up with during this training I'm in today, but they are obviously imperfect. Also, it's Spend * ROI = Rev, so that's kind of the relationship to get the revenue variance. This is what it looks like in the presentation, these are $ var, not pcts (which is what I'm using) Spoiler
Gotcha - maybe you just need to rearrange the rules. Put #2 first and that second part will be correct.
But then if it was a positive rev variance, but -30% ROI and +40% Spend, spend would obviously be the driver and rule #2 I don't think would work. ETA: I could just rewrite everything and remove the absolute values.
You might need to do a more nested if statement. Basically if roi is negative...then list rules...else all the other rules.
Here's what it looks like, for example. I have no standard rules, but can and probably should create some with this formula I'm building.... Welcome to any ideas, and anything extreme (like Magazine) is a result of really low numbers in one of the columns. Spoiler
Definitely remove all ABS, I'm not sure why they're there at all. Then see what you have - you may need to adjust your one rule if 19% is considered high in the one example.
Pretty inexperienced in excel and probably use round about methods to accomplish what I want most of the time. Anyway, trying to set up a scoring sheet for my friends and my master's pool to make everything easy on me to score. I have one sheet that is pulling the live scoring updates from espn. On the other sheet I want to track each person in my pool's team score. So, say I have Rory McIlroy on my team and his name in cell B2, I want to be able to say if 'B2' = 'Rory mcIrloy' on the web query sheet then B3= 'score"(fro the web query sheet) Is there an easy way to do this?
Yeah, vlookup. If your Web look up sheet KS set-up as column A golfer, column 2 score just do..... =vlookup (b2,Web Table A:B,2,False) Your golfers score should be in alphabetical order. If that doesn't work you can do a sumif too
It seems to be working without them in alphabetical order? I have it pulling from the espn leader board, so its listed by current position.
Yes.. Like this, and I've never done a fantasy golf thing but if it's total score I'd set it up like this. 1) In my example, Jason is a guy in your league. 2) The names of the golfers need to match what ESPN has exactly
Yeah, Im setting up a table similar that. Each player has 10 golfers and I am taking the lowest 6 scores each day. Have that part set up now to pull from the web query. Now, I am trying to sum up only the lowest 6 of those 10. Is there an easy command for that? Or will I need to just go in a select the 6 each day after the round. say this is "jason's" team below. Is there a quick formula I can plug into the "Player Round Total?" Spoiler
Round 1 is column B =Small(B2:B10,1)+Small(B2:B10,2)+Small(B2:B10,3)+Small(B2:B10,4).... ETC Probably a shorter way using arrays, but that's how I'd do it if it were only 6. I don't use arrays enough and would need to play around with it. Also the row numbers (2:10) were arbitrary, but I'm sure you got it.
Nice, never used the small function before. Like I mentioned in my original post, I frequently use longer work arounds to accomplish what I want in excel, so its pretty fitting for me to use this method rather than arrays
If it was like the smallest 100 out of 1000 you were adding an array would be worth it. Hah. That would be awful doing it the way I said to do it.
I got a doozy... Not sure it's possible, because some dumb moron idiot in marketing made a spreadsheet that looks like this. 253 LINES... They then asked me this... "So I'm trying to separate all the store numbers in column c and remove the duplicates" Columns A and B don't matter, so I delimited the values as separated by commas and then transposed them on another sheet. I tried non-transposed as well, but transposed I think is the most unique values and help for copy pasting speed if that's what it takes. I tried removing duplicates and it said it removed 50 while 50 unique remain, so something clearly didn't work there. I've started cutting and adding to the bottom of column B, and it currently looks like this There's nothing more I can do to automate this, right? The only thing I can think to do is manually cut and add and then add a trim function in column C, pasting those values and then removing duplicates from that. Thoughts?
MID command. Start in column D and maybe insert a row at top of work sheet. this can be a formula where to start. For example in D1 row 1 (start) would be = 1. Your command would be Mid(cell, D1, 4). Where 4 is the characters. The next column in row 1 would be =row1 + 6. This moves it 4 past the 1st record + 1 past the comma and 1 past the blank.
How did you get from the first picture to the second? Here's what I would've done: Copy column C to a word sheet Table Tools>Layout>Convert to Text Find & Replace > change ", " to "^p" Find & Replace > change "," to "^p" Find & Replace > change " " to "" Paste that data back into an Excel sheet At that point, I find the data is usually a lot cleaner than what I would've gotten from any excel formulas.
I've never done that, but will check it out... I've been using Trim() to get rid of silly spaces. Anyway, to get to the first to second I just highlighted the Stores column, then went to Data --> Text to columns --> Separated by comma... That removed the commas. Then I think I highlighted everything and pasted to a new sheet using Paste special --> Transpose.
I got a cheap community college Microsoft book a couple of years ago when I needed that shit for work. Amazon.
I have a workbook opened up. I create a new workbook and it doesn't pop open in a new window. I want one workbook on one computer screen and the new workbook on the other computer screen so I can compare them while they're both open. What's the deal? Edit: Nvm. I have them both opened up within the same excel window. I guess I can just minimize them and put one on the left side and one on the right side of the same window. Still, that's annoying.
I think you have to open Excel application twice to do what you are saying. Not just open specific files by clicking on them. Open the files seperately from within each Excel.
This. I used to have this very problem, but when our company computers upgraded to the new version it was solved. Makes things so much easier now.
One of the very few things I like about Excel 2013 is that they made it easier to look at two workbooks. It crashes so much
I'm currently trying to get this formula to work: =IF(K7=5,VLOOKUP(AI7,FORMULAS!M5:Z156,MATCH(FORMULAS!Z5,FORMULAS!M5:Z5)),IF(K7=5.5,VLOOKUP(AI7,FORMULAS!N5:Z156,MATCH(FORMULAS!Z5,FORMULAS!N5:Z5)))) The formula itself works, the problem is that I need an IF statement for every distance on this table to the right, but excel says that it is too many IF statements, the above equation only has TWO distances, I need 13: What can be done????????????????????????????????????????????
The Powerview add-in is so glitchy. If they fix that I'll have god like powers and could potentially take over country, at least a small-mid sized one.
I can't get Powerpivot to fucking run some of our SQL queries. IT hasn't figured it out, nothing on the internet about. Super frustrating
Using Index Match is probably a better technique here. There is a trick to using Array formula. You have to press CTRL + Shift + Enter. This page is a reference that might help. https://support.microsoft.com/en-us/kb/214142
Guy on reddit is suggeting THIS using something along the lines of: =INDEX((area1,area2,area3),MATCH(a1,row,0),MATCH(b1,column,0),MATCH(c1,area,0)) OR =VLOOKUP(C5,CHOOSE(SUM(N(A1>{0,10,20,30,40})),H5:K10,a5:r10,q1:r10,r5:t10,M5:P10),2) Neither of those relate to my data, but from everything I've read before asking for help these are in fact what I need to do...... but I just have no idea what to do with these formulas.