Month: April 2019

Did you know … you can perform an “unmatched query” in Excel?

I mentioned yesterday that we’re creating groups based on the upper level manager through whom individuals report. Since my groups are based on the upper level managers, I need to be able to identify when a new individual pops into the list of upper level managers. Real upper level management doesn’t change frequently, but unfilled positions create gaps in the reporting structure. I call the manager before the gap the highest-ranking person in that vertical and that individual’s reporting subtree becomes a group.

Determining if values from one list appear in another list is easy in Microsoft Access – it’s an unmatched query. I’d rather not have to switch between the two programs, and I was certain an Excel formula could do the same thing. It can!

The formula is:

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

And it does flag any manager from column H that does not appear in my list of upper level managers.

I am also able to filter my spreadsheet to display only records where the upper level manager does not appear in my summary table.

What is my formula doing? It is a combination of three functions

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

It starts with the IF function – a logical comparison – which is used as if(Test,ResultIfTestIsTrue, ResultIfTestIsFalse).

If the test is true, “Not in Manager Summary” will be put into the cell. If the test is false, nothing (“”) will be put into the cell.

The test itself is two functions. I’ve documented the VLOOKUP function previously, but briefly it searches a range of data for a specific value. If the value is found, it returns something. If the value isn’t found, it returns N/A.

In conjunction with the VLOOKUP, I am using the ISNA function. This function is a logic test – it returns TRUE when the value is N/A and FALSE otherwise.

So my formula says “Look for the value of cell H2 in column A of the SOA6MgrSummary tab. If the result is N/A, put ‘Not in Manager Summary’ in this cell, otherwise leave this cell empty”.

 

Did you know … Excel can create a sum of values where a condition is true?

For a project, we need to divide the entire company into groups. I chose organizational structure because it’s easy – I can determine the reporting structure for any employee or contractor, and I can roll people into groups under which ever level of manager I want.

The point of making groups, though, is to have close to the same number of people in each group. While I can use COUNTIFS to count the number of people who report up through each manager, I need to add those totals for each group of managers to determine how many individuals fall in each group. How many employees are included in Group 0?

This is actually quite easy – just like count has a conditional counterpart, countifs, sum has a conditional counterpart sumifs

The usage is =SUMIFS( Range Of Data To Sum, Range Of Data Where Criterion Needs To Match, Criterion That Needs To Match)

You can use multiple criteria ranges and corresponding criteria in your conditional sum — =SUMIFS(SumRange,CriterionRange1,CriterionMatch1,CriterionRange2,CriterionMatch2,…,CriterionRangeN,CriterionMatchN).

I only have one condition, so with a quick listing of the groups, I can add a column that tells me how many individuals are included in each group.

Bonus did you know – instead of specifying a start and end cell for a range, you can use the entire column. Instead of saying my “Range of data to sum” is B2:B101, I just used B:B to select the entire “B” column.

Viewing the values, I can see that my group size is not consistent.

As I adjust the group to which the manager is assigned, these sums are updated in real-time.

 

Did you know … you decide what appears in your Teams activity feed?

Have you noticed that some new Teams messages show up in your activity feed and others do not? The Teams activity feed is meant to highlight Team messages that are important to you. How does Teams know what is important to you? It doesn’t … Teams needs you to tell it what is important to you.

Normally, Teams conversation activity doesn’t appear in your activity feed. When my test account posts a message into the “General” channel …

I see the channel is bolded, indicating new activity; but I do not have any indicators on my Teams or Activity tiles.

What is included in the Activity feed?

Messages to which you’ve replied:  When you reply in a thread, your reply tells Teams that the thread is important to you (just liking a post doesn’t count … you’ve got to actually reply. There are enhancement requests to include some provision for following a thread without replying and muting notifications on a thread to which you’ve replied).

The test account added a new reply after I’d posted my reply. In addition to seeing the channel name in bold, my Activity tile indicates there is one unread message.

Followed Channels: If you want to receive notifications for all threads posted to a channel, follow the channel. Click the ellipsis next to the channel name and select “Follow this channel”.

Now when the test account posts a new thread …

I have an indicator on my Activity tile.

Team At-Mentions: You’ll also see an activity item when the Team is at-mentioned in a post (that’s why at-mentioning the Team is a really effective way to bring attention to your post).

In addition to the activity indicator, I also see an “@” symbol on the Teams tile indicating that a Team has been at-mentioned. And a little number next to the channel that tells you how many unread Team or Channel mentions are in the channel.

Some Channel At-Mentions: What is the difference between at-mentioning a Team and at-mentioning a Channel? When you at-mention a Channel, only people who have favorited the channel will see activity alerts.

Usage Tip: If you break your Team up into channels that aren’t deeply interconnected, individuals can favorite and follow the channels where they are actively involved and check other channels when they’ve got some time. You can at-mention the channel for important messages without overwhelming the whole team with alerts.

While the channel is marked as bold to indicate unread messages, I don’t get any notification about the at-mention. How do you mark a channel as a favorite? Just click that star to the right of the channel name (favorite channels also appear in the initial channel list so you don’t have to expand “X more channels” to see them).

Now a message which at-mentions the channel …

alerts me. A lot 😊

 

Ohio House Bill 62

Letter sent to my Ohio State Representative and Senator:

I’m writing in reference to House Bill 62.

Pertaining to the definition of “Plug-in electric motor vehicle” and “Hybrid motor vehicle” (Sect 4501.01 DDD and EEE) and their additional respective registration fees, the wording in the bill as I read it leaves some ambiguity to a third segment: the “Plug-in Hybrid Electric Vehicle” or PHEV. These are vehicles that will both plug into an external source to charge for a modest electric range AND use the gasoline engine in a typical hybrid configuration when the charge is depleted. These vehicles are NOT designed to run indefinitely on electricity alone. My concern is that the current wording classifies certain PHEV’s as Plug-in electric motor vehicles. PHEV’s will still pay the gasoline tax, similar to hybrids, when they fill up at the pump *and* be charged a 200$ registration fee. Examples of vehicles in this category are the Chevy Volt, Toyota Prius Prime, Chrysler Pacifica PHEV, Kia Niro PHEV, Honda Clarity, and the Ford Fusion Energi. I would like to see the law amended to ensure PHEV owners, who generally pay some gasoline tax just like “Hybrid motor vehicle” owners, are not subject to the higher registration fee.

Beyond the classification of PHEVs, the gasoline tax is consumption and usage based. Heavier vehicles tend to have lower mpg ratings, thus their drivers accrue more tax. Individuals who drive a lot accrue more tax. The new registration fee is a fixed amount that has no bearing on an individual’s actual vehicle usage. I will be assessed the 200$ fee, and I drive maybe 2,500 miles a year. Someone with a pure electric Tesla who drives 300 miles a day pays the same 200$ fee but drives 75,000 miles in a year. If I convert an F-350 to a plug-in electric motor vehicle, that 7,000 pound truck is going to be assessed the same 200$ fee as my 3,800 pound PHEV.

I don’t have a problem being asked to pay for *my* usage of the roads. I wouldn’t complain about per-mile fee for electric and hybrid vehicles or an additional tax on electrical consumption to fund road repairs. I’d be less upset if the petrol tax were scrapped and everyone charge a registration fee based on the vehicle’s weight so infrequent drivers universally subsidize frequent drivers. But I vehemently object to being uniquely, financially penalized for low-milage usage of a PHEV.