Excel Tips & Tricks ITT

Help Support Talkbeer:

Joined
Apr 24, 2013
Messages
3,456
Location
Fairfax, VA
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
 
Joined
Jan 15, 2014
Messages
3,098
Location
West
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.
 
Joined
Jan 15, 2014
Messages
3,098
Location
West
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.
 
Joined
Apr 10, 2013
Messages
8,944
Location
Squat Rack
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.
 
Joined
Jan 15, 2014
Messages
3,098
Location
West
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

Moderator
Staff member
Joined
Apr 5, 2013
Messages
6,058
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?
 

BadJustin

Moderator
Staff member
Contributor
Joined
Oct 2, 2013
Messages
16,764
Location
Former Janitor
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
 
Joined
Jan 15, 2014
Messages
3,098
Location
West
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.
 
Joined
Apr 24, 2013
Messages
3,456
Location
Fairfax, VA
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
 
Joined
Apr 10, 2013
Messages
4,296
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
 
Joined
Sep 29, 2013
Messages
542
Location
Colorado
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.
 
Joined
Apr 10, 2013
Messages
4,296
Location
The Misty Mountains
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.
 
Group Builder
Top