Here it is... So we're running 8 scenarios that'll be across the top... There will be a data tab that has thousands of rows and multiple values for each of the tactics for each scenario... So basically I think I need an index match match with a sumif. Thoughts? Here's what it looks like:
Pivot table If your data has 3 columns. Tactic, value, and scenarios. Highlight all data and select pivot table from insert ribbon. Click ok to or choose a specific location to create Pivot table. Drag tactic from field list to row labels, drag scenario to column labels and value to values. You may have to change values to a sum instead of count.
index match for sure. Most effective other than pivoting I would index match the data and then pivot on the newly created columns to get your totals
I'll probably just pivot as it's in the format to do it. The raw data essentially looks similar to the table I posted only with like 200k rows, so there are a lot of repeats.
at my new job (in 2 weeks) I won't be using excel at all hardly to put data in I've used it for the last 3 years everything now will be in #tableau
Who can model this? Pulled from the Riddles thread. I'm having trouble figuring it out. This is the kind of shit we would do in B-School.
Black right? Albert could see two hats, so if both ahead of him were white he would know his was black So since Albert didn't speak up then the last guy would know he had a black hat
Barry knows that both him and C can't have white caps else Albert would know that his is black. Barry can see C and if C was white then he would have to be black. Since Barry didn't know, C couldn't have white, so he must be black
what if they all had black hats? nobody would know? Albert could see 2 black hats above and couldn't be confident Barry couldn't be sure
B knows that he and C both can't have white on. B can see C, so if he saw C was wearing white, he would know his is black. Since B doesn't know, C isn't wearing white so C then knows he is wearing black.
All you need to know is what color your hat is... We can not say with 100% certainty what Albert's or Barry's are given the limitations. Anyway, how do you model this shit in Excel?
I made a python script that will run a SQL Query, read the results into a dataframe, write it to an excel file, format the data, and then sends it out through your outlook
Mainly because I'm a nerd, and am entertained by dumb shit like this. It would probably help with my Excel learning as well -- teaching or reminding me of a functionality that just might help with a work problem in the future. We would do things similar in B-School for class. I think it's possible with the tools available, but I'm not exactly sure how. A goal seek wouldn't work, or would it? I haven't seriously tried yet, but was curious if anyone else could figure it out relatively easily.
It definitely is, and it'd be harder than just figuring it out. Honestly, it's more about the puzzle/challenge than anything else. The functionality argument I said is really more of a ancillary benefit.
Spoiler alert if you want to solve the riddle 100% on your own. Spoiler After googling the solution, excel won't help. There is an additional step beyond basic logic that is required to solve the riddle. Frankly, I think the Wikipedia solution is bullshit and there is no way you can be 100% sure.
I've got the riddle solved. What I don't have is how to build it in Excel. I'm cool with the solution. Here's how I did it in my head... I haven't looked at Wiki, so I have no idea if that solve is written similarly to mine. Spoiler First, you have to assume the 2 behind you know what they are doing. The only way for Albert to know his hat color is if Barry and my hat is white. Since he obviously didn't see 2 whites in front of him, he had to say that he didn't know. Barry knows that Albert 100% did not see 2 white hats. However, if you have a white hat, Barry will know that his hat is black, because Albert didn't see 2 white hats (since there are only 2 color options). Since Barry can't say definitively the color of his hat, then my hat must be black since it's definitely not white. However; you have no idea what color Albert or Barry's hats are, because they can still be white or black.
I don't see a way this works tbh. C's success is dependent upon B. If A sees two black hats, everyone is fucked If A sees two white hats, everyone leaves. If A sees a black hat on C, everyone is fucked. If A sees a white hat on C, B and C leave. Someone please explain this.
Spoiler Dammit. If neither of them know, C must be wearing a black hat. Fuckers. ABC BWW - everyone leaves WBB - no one leaves BBB - no one leaves WBW - B & C leave BBW - B & C leave BWB - no one leaves WBB - no one leaves The only ones where no one knows is where C is wearing a black hat. If C is wearing a white hat, B and/or A will be able to tip him off.
Object oriented programming is for the birds. Spoiler Python is awesome but i just can't quit fortran
Spoiler Wikipedia says the answer is dependent on how long it takes B to answer. If B answers quickly, C is wearing one color, if he answers slowly he is wearing a different color. That logic is complete bullshit because it assumes so many things about the character of B and B's confidence that he is 100% correct.
First off, I'm answering some guard with 100% certainty whether I know or not. Even though I'm in probably in a white collar prison with Albert and Barry...I'm a gambling man and taking some bullshit guard up on his bet to let me go. Worst case without ever hearing these other two, I'm 40% right that I'm wearing a white hat which is better than 0% chance I'm telling him I'm not certain and staying in that shit hole one more night. There is no extra punishment for guessing wrong. If everyone says they are wearing a black hat, somebody will be right and everyone goes home. I call bullshit on Albert and Barry being honest and intelligent. Chances are one of those fuckers is the reason the 3 of us are locked up in the first place.
Spoiler Well that's a shitty illogical answer from wiki. Feel like mine is much more logically based. ABC BWW - A sees two white hats and knows he must be wearing black. B & C can then deduce they must both be wearing white. Everyone leaves. WBB - A sees two black hats so he can't know. B sees a black hat so he can't know either. BBB - same as wbb WBW - A sees black and white hat. He can't know what hat he has. B sees a white hat. Because A couldn't know, B can't be wearing a white hat too, so he must be wearing black. C can then deduce that he must be wearing white. BBW - same as wbw BWB - A sees white and black so can't guess correctly. B sees a black hat on C so he can't know either. In each scenario where C has on a white hat, either A or B will be able to logically deduce what hat they have on and C will know he has on white. In each scenario where C has on black, no one can figure out what color their hat is. However, since all scenarios where C has on white someone knows, and all scenarios where C has on black, no one knows, C can deduce that if no one knows he must be wearing black. Iron Mickey check my work pls
:D It is really simple to change I can show you and you could buy me amazon presents or sexual favors
but srs I have always wanted to see if there was a market for consulting for excel and doing SQL and/or excel automation for smaller companies
Same here. I was able to get some work doing VBA and customization. Also where I work we typically always use Excel as a "front end" to our solutions in another software. A few of my customers have asked for specific training or excel work that is not exactly the strength of my company. I have one job lined up to teach VBA from that which should start in August.
More of a viso request but does anyone know how I can import a list from excel to Visio to be the names of the shapes? It's not quite linking data to a shape I actually want to rename the shape to the list I have.
say I had for example 2 columns in an excel sheet. Column A had States, Column B has 10 cities (But all the cities are in one cell; so A2 has Tennessee, B2 has 10 cities in Tennessee) Like Below How would i get this in the second format? so its one line for state and one city -----Original Format------ State_________________City Tennessee_____________Brentwood, Nashville, Franklin, etc Florida________________Destin, Panama City, Orlando, etc Georgia_______________Griffin, Peachtree City, Atlanta, etc ------How I want it------ State________City Tennessee____Brentwood Tennessee____Nashville Tennessee____Franklin Florida_______Destin Florida_______Panama City Florida_______Orlando Georgia______Griffin Georgia______Peachtree City Georgia______Atlanta edit - meant to put this in the programming thread, but if this can be done in excel that would be great too. I ended up manually typing it out because Text to column didn't give me what I wanted.
The first thing you could do to separate all the cities is hit Text to Columns setting the delimiter to comma. Now that all the cities are in their own cells, I can't think of an easy way to re-format it besides just doing a copy/paste special (select transpose) to get all the cities in a single row. If you have a huge set of data then maybe a macro could work, but I'm not too good with macros. After you get all the cities pasted it should look like this: Tennesee____Brentwood _____Nashville _____Franklin Florida_______Destin _______Panama City etc. In order to populate all the states I'd create a new State column and use an if formula. Say Tennessee is located in cell B2 and Brentwood is in C2 Title column A with the word State in A1 then in A2 just do a c/p of Tennessee. In A3 use the formula "=if(B3="",A2,B3) You should be able to fill this through the whole data set. After that just copy/paste values only of column A, then you can delete column B.
This is more an algebra question than an excel question I suppose, but I didn't know wher else to ask for help. How do I illustrate the following bonus model based off of monthly account sales: no bonus for first $50,000 (0% commission) $50,001 to $75,000 compensated at 5% $75,001 to $100,000 compensated at 7.5% $100,001+ compensated at $10,000 It would be a simple linear equation like 0.05(X-50000) but I don't know how to factor in the tier'd commission. I'd like to generate a graph that is an easy reference with total sales as the independent variable and commission as the dependent variable. Spoiler
On mobile so apologies for typos. It'd be pretty easy to use an "if" function to do the calc. Have a set of values for x (perhaps in intervals of $5000 starting at $40,000. Use the "if" function for your y. You'll need to combine stack the scenarios then define the answer.=if(scenario 1, outcome 1, if( scenario 2, outcome 2, etc)). So for your example I think =if(x<50,000,0,if(x<75,000,x*.05),if(x<=100,000,x*.075,10,000))). You won't need an if statement for the last condition since it will be the only one left if their sales numbers don't meet any of the other scenarios.
New question here.... I'm having a Friday moment. Ok... I have a form that I've built into Excel. I'm going to have folks fill it out and send it back to me. I also have to make a tracking page, basically tracking the basics of each form (Name, Date, # of forms sent etc). I want this tracking worksheet to update automatically. My plan as of now, is to just copy the form into a another spreadsheet that will contain all of the forms as individual worksheets. To which I'll name "OptimizationForm1", "OptimizationForm2", etc. Here's how I set it up for now (open to other ideas): I can drag these formulas down, but it'll always be looking up the same worksheet.... Any idea how to drag it down to represent the incoming worksheet names? Or another way entirely to do this? My first thought is utilizing Column A with the numbers, but I don't think that'll work. I have no idea how to code, so I may be SOL. I'm up for other ideas of building, but the main thing is minimal steps from receiving an Excel form filled out and keeping tracking updated without having to even glance at the sheet. If I don't figure it out, I'll probably do this the same way, but just do a Search + Replace every line.
Can you concatenate optimization and the row on the left that signifies the sheet number to get a new sheet name? I don't think that works,but maybe
I need to subtotal two columns based upon a sumif statement. I'm pretty sure it's impossible but I will give somebody a small reward if they can figure it out/give me the proper equation.