Excel Help Thread

Discussion in 'The Mainboard' started by Gin Buckets, May 28, 2015.

  1. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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:

    upload_2015-5-28_12-15-34.png
     
  2. NDfanPSUgrad

    NDfanPSUgrad Well-Known Member
    Penn State Nittany LionsNotre Dame Fighting Irish

    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.
     
    #2 NDfanPSUgrad, May 28, 2015
    Last edited: May 28, 2015
    Marty Kaan likes this.
  3. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    Definitely the easiest way to do it.... How about formulaically?
     
  4. Marty Kaan

    Marty Kaan By any means necessary
    Donor

    Depends on the data

    Gin Buckets can post a small section of raw data?
     
  5. Marty Kaan

    Marty Kaan By any means necessary
    Donor

    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
     
    Gin Buckets likes this.
  6. NDfanPSUgrad

    NDfanPSUgrad Well-Known Member
    Penn State Nittany LionsNotre Dame Fighting Irish

    I would probably go SumIfs before index match.
     
  7. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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.
     
  8. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    at my new job (in 2 weeks) I won't be using excel at all hardly to put data in

    :feelsbadman: I've used it for the last 3 years

    everything now will be in #tableau
     
  9. Where Eagles Dare

    Where Eagles Dare The Specialist Show On Earth
    Donor
    Auburn TigersAtlanta BravesWashington Football TeamAtlanta United

    PowerPivot that bitch.

    Takes excel to a while other level
     
    steamengine and Walt Disney like this.
  10. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    yeah PowerPivot is amazing

    especially if you have multiple data sources pulling into excel
     
    Where Eagles Dare likes this.
  11. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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.

    [​IMG]
     
  12. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    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
     
  13. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    F me that isn't right
    this hurts my head
     
  14. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    Screw you for posting that
     
  15. Name P. Redacted

    Name P. Redacted I have no money and I'm also gay
    Donor
    Kansas State WildcatsSeattle Kraken

  16. The Banks

    The Banks TMB's Alaskan
    Donor TMB OG
    Oregon DucksGreen Bay PackersDetroit Red WingsBayern Munich

    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
     
    High Cotton, Gin Buckets and naztay like this.
  17. The Banks

    The Banks TMB's Alaskan
    Donor TMB OG
    Oregon DucksGreen Bay PackersDetroit Red WingsBayern Munich

    Nvm
     
    #17 The Banks, Jul 2, 2015
    Last edited: Jul 2, 2015
  18. Llama

    Llama New Member
    Donor
    Nebraska CornhuskersColorado Avalance

    Who the fuck is C
     
  19. naztay

    naztay Well-Known Member

     
  20. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    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
     
  21. naztay

    naztay Well-Known Member

    B would have known that he had a black hat if the first one had a white hat.
     
    The Banks likes this.
  22. beist

    beist Hyperbolist
    Donor

    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.
     
  23. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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?
     
  24. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    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

    :smugdog:
     
    Where Eagles Dare and goose like this.
  25. Name P. Redacted

    Name P. Redacted I have no money and I'm also gay
    Donor
    Kansas State WildcatsSeattle Kraken

    why would you want to model this in excel?
     
  26. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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? :idk: I haven't seriously tried yet, but was curious if anyone else could figure it out relatively easily.
     
  27. Name P. Redacted

    Name P. Redacted I have no money and I'm also gay
    Donor
    Kansas State WildcatsSeattle Kraken

    Just seems like the wrong tool for the job, IMO
     
    Where Eagles Dare likes this.
  28. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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.
     
  29. DollarBillHokie

    DollarBillHokie Usher is the worst
    Donor
    Virginia Tech HokiesTiger Woods

    Spoiler alert if you want to solve the riddle 100% on your own.
    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.
     
  30. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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.

    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.
     
  31. Tilly

    Tilly Souf Cack
    Donor TMB OG
    South Carolina Gamecocks

    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.
     
  32. Tilly

    Tilly Souf Cack
    Donor TMB OG
    South Carolina Gamecocks

    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.
     
    #32 Tilly, Jul 2, 2015
    Last edited: Jul 2, 2015
  33. kennypowers

    kennypowers Big shit like a dinosaur did it
    Donor TMB OG
    UCF KnightsAtlanta BravesJacksonville Jaguars

    Object oriented programming is for the birds.

    Python is awesome but i just can't quit fortran
     
    goose and Walt Disney like this.
  34. DollarBillHokie

    DollarBillHokie Usher is the worst
    Donor
    Virginia Tech HokiesTiger Woods

    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.
     
  35. NDfanPSUgrad

    NDfanPSUgrad Well-Known Member
    Penn State Nittany LionsNotre Dame Fighting Irish

    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.
     
  36. Tilly

    Tilly Souf Cack
    Donor TMB OG
    South Carolina Gamecocks

    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
     
    #36 Tilly, Jul 3, 2015
    Last edited: Jul 3, 2015
  37. Goose

    Goose Hi
    Donor
    Ohio State BuckeyesCincinnati BearcatsCincinnati RedsCincinnati BengalsReal MadridXavier MusketeersDayton FlyersTiger WoodsFC Cincinnati

    No joke if you could replicate this I think my work would pay you for that same concept
     
    Walt Disney likes this.
  38. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City


    :D

    It is really simple to change

    I can show you and you could buy me amazon presents or sexual favors
     
    goose likes this.
  39. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    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
     
    goose and NDfanPSUgrad like this.
  40. NDfanPSUgrad

    NDfanPSUgrad Well-Known Member
    Penn State Nittany LionsNotre Dame Fighting Irish

    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.
     
  41. NDfanPSUgrad

    NDfanPSUgrad Well-Known Member
    Penn State Nittany LionsNotre Dame Fighting Irish

    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.
     
  42. Walt Disney

    Walt Disney #Dawgzy
    Donor TMB OG
    Georgia BulldogsManchester City

    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.
     
  43. grimreeper46

    grimreeper46 Active Member

    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.
     
    Walt Disney likes this.
  44. BrickTamland

    BrickTamland You're not Ron...
    Donor
    Florida State Seminoles

    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.
    [​IMG]
     
  45. grimreeper46

    grimreeper46 Active Member

    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.
     
    #45 grimreeper46, Aug 1, 2015
    Last edited: Aug 1, 2015
    BrickTamland likes this.
  46. Capstone 88

    Capstone 88 Going hard in the paint
    Donor
    Alabama Crimson TideAtlanta BravesTennessee TitansNashville Predators2pac

    I made this exact scenario a few months ago. It's just a few if statements like grimreeper said
     
  47. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    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):

    upload_2016-3-11_11-36-34.png

    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.
     
  48. Where Eagles Dare

    Where Eagles Dare The Specialist Show On Earth
    Donor
    Auburn TigersAtlanta BravesWashington Football TeamAtlanta United

    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
     
  49. Gin Buckets

    Gin Buckets Well-Known Member
    Donor
    Virginia Tech HokiesIndiana HoosiersAtlanta BravesAtlanta HawksAtlanta Falcons

    That was my thought, or some work around to make it match, but it isn't working.
     
  50. POWESHOW

    POWESHOW Social Critic
    Donor
    Duke Blue DevilsAlabama Crimson TideNew York YankeesNew England PatriotsMontreal CanadiensReal Madrid

    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.