Excel Tips & Tricks ITT

[neorunner]

I'm doing an analysis of inventory management performance over time. the workbook I have uses ~150,000 COUNTIFS() formulas w/2 conditions in each, evaluating across a 10,000 row table. I knew it was going to be ugly, so I turned off automatic calculations.

After I got everything in order, I started the workbook calculation, walked to a store a few blocks away to get a four pack, came back, and workbook was only about 60% computed . I long for the day Excel on Mac supports Data Model & Power Query - would have been so much easier.

Matlab that shit

 

[jtmartino]

I'm doing an analysis of inventory management performance over time. the workbook I have uses ~150,000 COUNTIFS() formulas w/2 conditions in each, evaluating across a 10,000 row table. I knew it was going to be ugly, so I turned off automatic calculations.

After I got everything in order, I started the workbook calculation, walked to a store a few blocks away to get a four pack, came back, and workbook was only about 60% computed . I long for the day Excel on Mac supports Data Model & Power Query - would have been so much easier.

Put it in Google Spreadsheets. I have a workbook with 10 x 10,000+ round multiple functions and it calculates almost instantly.

 

[beerherder]

Put it in Google Spreadsheets. I have a workbook with 10 x 10,000+ round multiple functions and it calculates almost instantly.

Good idea, will try

 

[Fingolfin]

Man, if I ever dumped company data into Google sheets they would fire my ass and I'd deserve it.

 

[jtmartino]

Man, if I ever dumped company data into Google sheets they would fire my ass and I'd deserve it.

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.

 

[Fingolfin]

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.

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.

 

[BadJustin]

Not Excel but I have recently discovered OneNote, and man, do I fucking love it thus far.

 

[Fingolfin]

Not Excel but I have recently discovered OneNote, and man, do I fucking love it thus far.

Report3d

 

[BadJustin]

 

[Josh]

Not Excel but I have recently discovered OneNote, and man, do I fucking love it thus far.

Please take this toxicity elsewhere.

 

[jtmartino]

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.

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.

 

[Trady]

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?

 

[BadJustin]

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?

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

 

[jtmartino]

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?

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.

 

[neorunner]

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.

ITAR

 

[quirkzoo]

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

 

[Blargimus]

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

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.

 

[quirkzoo]

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.

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.

 

[Sage]

I am bumping up this ancient thread because I just discovered xlookup and jesus christ this is what I’ve always wanted.