Excel Tips & Tricks ITT

Discussion in 'Totally Off-Topic' started by Fingolfin, Oct 27, 2014.

Help Support TalkBeer Community by donating:

  1. neorunner

    neorunner

    Joined:
    Apr 24, 2013
    Likes Received:
    18,105
    Location:
    Fairfax, VA
    Matlab that shit
     
    Beerontwowheels and mborden like this.
  2. jtmartino

    jtmartino

    Joined:
    Jan 15, 2014
    Likes Received:
    16,481
    Location:
    West
    Put it in Google Spreadsheets. I have a workbook with 10 x 10,000+ round multiple functions and it calculates almost instantly.
     
    beerherder likes this.
  3. beerherder

    beerherder

    Joined:
    Oct 12, 2015
    Likes Received:
    3,078
    Location:
    SF
    Good idea, will try
     
    jtmartino likes this.
  4. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,102
    Location:
    Squat Rack
    Man, if I ever dumped company data into Google sheets they would fire my ass and I'd deserve it.
     
    Last edited: May 23, 2019
    BadJustin, pjs234, Shakedwnst and 3 others like this.
  5. jtmartino

    jtmartino

    Joined:
    Jan 15, 2014
    Likes Received:
    16,481
    Location:
    West
    Why? Access is protected, reg compliant, and a paper and audit trail that beats an Excel file on someone's server.

    Trust me when I say that Google is better in every regard.

    I work with more regulation than most people (pharma) and it's the basis of the entire Genentech/Roche ecosystem. It's better than Microsoft. Period.
     
    capnmike likes this.
  6. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,102
    Location:
    Squat Rack
    That's great. But it's still outside our ecosystem. If we don't support it, I don't use it. Not worth the risk.
     
    capnmike and lambandtunaphish like this.
  7. BadJustin

    BadJustin Premium Supporter Contributor

    Joined:
    Oct 2, 2013
    Likes Received:
    149,062
    Location:
    Former Janitor
    Not Excel but I have recently discovered OneNote, and man, do I fucking love it thus far.
     
  8. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,102
    Location:
    Squat Rack
    Report3d
     
  9. BadJustin

    BadJustin Premium Supporter Contributor

    Joined:
    Oct 2, 2013
    Likes Received:
    149,062
    Location:
    Former Janitor
    [​IMG]
     
    Trady likes this.
  10. Josh

    Josh Premium Non-Supporting Supporter Staff Member Contributor

    Joined:
    Sep 28, 2013
    Likes Received:
    91,237
    Location:
    In a van by a river
    Please take this toxicity elsewhere.
     
    Fingolfin and BadJustin like this.
  11. jtmartino

    jtmartino

    Joined:
    Jan 15, 2014
    Likes Received:
    16,481
    Location:
    West
    Your post implied that it was somehow inferior, but it's clear MS is doing everything possible to stay relevant. Office 365 is just garbage comparatively speaking.

    Don't get me wrong, I use Excel daily because I have to, but every chance I get I build my own workbooks in Google. The learning and function control are substantially better, and the calculation speed cannot matched.
     
  12. Trady

    Trady Moderator Staff Member

    Joined:
    Apr 5, 2013
    Likes Received:
    62,798
    Location:
    South Jersey
    So I have no idea if this is even possible, but when I am processing orders, I have to write up a note at the end of submitting the order that I have to pull a bunch of data from my tracker and write up into the note. The note is standardized, stating that "I created XXXXX order, using XXXXX circuit and XXXXX pathway", and it uses about (15) different cells worth of data from my tracker. It's kind of a PITA to copypasta from the tracker to this note back and forth, when I have to imagine I can setup some sort of VLOOKUP into a template, right?

    Any thoughts on this?
     
  13. BadJustin

    BadJustin Premium Supporter Contributor

    Joined:
    Oct 2, 2013
    Likes Received:
    149,062
    Location:
    Former Janitor


    iI know the long way to do it but one of these homies probably has an easier way
     
  14. jtmartino

    jtmartino

    Joined:
    Jan 15, 2014
    Likes Received:
    16,481
    Location:
    West
    Use index match to pull your values. Add the standard text with quotes and connect all with & symbol. Ampersand can connect functions and reg text in quotes.

    Example:

    =“I created”&index(match())&”order, using”&index(match())&”circuit and” etc.
     
    Trady likes this.
  15. neorunner

    neorunner

    Joined:
    Apr 24, 2013
    Likes Received:
    18,105
    Location:
    Fairfax, VA
    ITAR
     
  16. quirkzoo

    quirkzoo

    Joined:
    Apr 10, 2013
    Likes Received:
    33,944
    Location:
    The Misty Mountains
    Okay, here is the situation.

    2,000 records that have a field "photo last updated". I want to count how many records have photos:

    older than 1 year
    1 yr to 6 months
    6 months or newer

    The tricky is that I am summarizing by location. So my report is

    Child ID, Location ID, Photo Last Updated date

    Then another tab summarizing the data

    Location ID, Old, Medium, New

    I want the summary to count the number of records with an old (or missing photo last updated field), medium, or new photo

    Does that make sense?

    I tried uploading the excel but not an allowed file format, tried to trick it by calling it a gif but that didn't work either
     
  17. Blargimus

    Blargimus

    Joined:
    Sep 29, 2013
    Likes Received:
    6,489
    Location:
    Colorado
    I would make a new column next to the "photo last updated" column with the formula "=TODAY()-(photo last updated column)", and make sure it's formatted as a number. That will give you how many days have passed since that date as an integer.

    Then, it's easy to use countif/countifs to get what you're looking for:
    • (number of new photos)=COUNTIF((column of days passed since last photo update),"<180")

    • (number of medium photos)=COUNTIFS((column of days passed since last photo update),">=180",(column of days passed since last photo update),"<=365")

    • (number of old or missing photos)=COUNTIF((column of days passed since last photo update),">365")+COUNTBLANK(column of days passed since last photo update)
    You do have to watch out that the column referenced by the COUNTBLANK function is the same size as the full data set column, or your count will be too high.

    EDIT: I'm not sure if I understood about the Location ID part of your question, but you can filter those by making them all COUNTIFS functions and adding another criteria and criteria range to the formula for the specific Location ID.
     
  18. quirkzoo

    quirkzoo

    Joined:
    Apr 10, 2013
    Likes Received:
    33,944
    Location:
    The Misty Mountains
    you are the man, I had only used countif before and the power of countifs were the breakthrough, I thought I was going to have to do some kind of crazy vlookup within the countif.