Excel Intermediate: Cleaning up formulas using Named Ranges

Standard

 

NOTE: All samples will be shown using Excel 2010. All examples and references will work in Excel 2003 – 2013, just the look and feel will be different.

One of the most difficult parts of understanding a complex Excel formula is determining what data is being referenced in the various ranges. For example, this formula seems more complex than it really is. You have no way of determining what is being compared in the IF statements without analyzing the ranges.

{=SUM(IF(Data!F2:F501='Dash Board'!C3,IF(Data!H2:H501>='Dash Board'!C4,IF(Data!I2:I501>='Dash Board'!C5,1,0),0),0))}

NOTE: This is an Array Function. If you are not familiar with them please read this blog.

All you can say for sure is that you are getting the total of rows on the Data page that meets three separate criteria.

Here is the same formula using Named Ranges:

{=SUM(IF(State=Project_State,IF(Employees>=Techs_Required,IF(Budget>=Project_Budget,1,0),0),0))}

That’s better. Now we know that we are testing the following criteria:

  • Is the State the same as the Project State?
  • Is the number of Employees greater than or equal to the Techs Required?
  • Is the Budget greater than or equal to the Project Budget?

All rows that meet these criteria will be counted.

Now let’s see how we set this up. In the upper left hand corner is a box that shows the current cell / range that is selected. This is called the Name Box.

Named Range 1

In the image above, you can see that cell E8 is currently active, and this is reflected in the Name Box. But did you know that you can change the Name of any cell or range of cells just by typing in the Name Box? Let’s give this a try. Click in cell C3 to make this the active Cell. Now click in the Name Box and type in “Project_State” (remember this is the name used in our second formula above). Once you press <Enter> the cell C3 will also be known as Project_State and can be referred to as such in any formula or reference.

Named Range 2

Now we need to create the references to cells C4 and C5 for our formula. Make cell C4 the active cell and name it “Techs_Required.” Finally, make cell C5 the active cell and name it “Projected_Budget” (the reason I gave it this name and not the name shown in B5 is because we will have another field named “Budget” on the Data tab).

That should cover everything on the “Dash Board” tab; now let’s work on the “Data” tab. There are two ways to name a range. Let’s try both methods.

First method:

Click in the cell A2 to make this the active cell. Now press <Ctrl><Shift><Down Arrow>. This will select all the cells under “First Name”. Your screen should look something like this:

Named Range 3

Now, in the Name Box type in “First_Name.” Once you press <Enter>, the range A2:A501 will also be known as Project_State and can be used in any formula or reference.

Second method:

Click in cell A1 to make this the active cell. Now press <Ctrl><Shift><End>. This will select the entire data table. Now, from the menu bar, select Formulas and click on Create from Selection.

Named Range 4

You will get a pop-up asking where the titles can be found. Select only Top Row.

Named Range 5

Ta-da! We now have named ranges for each column. To check this, press the dropdown arrow to the left of the Name Box.

Named Range 6

As you can see, all spaces in the title will be replaced with underscores (“_”).

So now we can create our formulas in a more sentence-like style. Here are a few examples that can be created quickly and will be easy to understand by anyone reading them.

  • =SUM(Budget)
  • =Countif(State, “=CA”)
  • =Average(Budget)
  • {=SUM(IF(State=Project_State,IF(Employees>=Techs_Required,IF(Budget>=Projected_Budget,1,0),0),0))}

A couple of thing to keep in mind about named ranges:

  • If a name already exists on the tab, it will automatically be overwritten without prompting
  • If a name already exists on another tab, you must specify the tab or it will default to the current tab’s name
    • For example Data!Budget will reference the range Budget on the Data tab
    • Adding new cells to the end of a range does not automatically extend the range
    • Inserting new cells in the middle of a range does automatically extend the range
    • Do not use reserved words when naming a range (select, if, sum…)

As you can see, using named ranges can not only make reviewing formulas easier, but you can also create new formulas more quickly.

Tech Tips for Techs: Is obvious spam still making it into your Office 365 inbox?

Standard

 

In this TechTip I want to go over an issue that I’ve seen pop up periodically that might save you a support call to Microsoft.

In the Office 365 platform Microsoft uses a scoring system when it comes to dealing with spam. It’s scaled from -1 to 9 but, interestingly enough, they only use -1, 0, 1, 5, 6, and 9. The other numbers in the scale aren’t used at all by the platform. (Weird, huh?) When a message lands in 365, the content filters will scan the message and apply a score to it based on a combination of variables that include Microsoft’s own spam definitions as well as those set by the system administrator.

These scores are referred to as SCL – Spam Confidence Level. An SCL of -1 gets applied to messages that are explicitly defined as safe, e.g., whitelisted email addresses, IP addresses, or domains. These “bypass” the spam filter. An SCL of 0 or 1 gets applied to messages that the filter has scanned and determined to be safe, and will get dropped into the recipient’s inbox. An SCL of 5 or 6 is given to messages that are ‘quite likely’ to be spam, and those get dropped into the recipient’s junk email folder. An SCL of 9 is given to messages that are ‘most certainly’ spam, and these get dropped into the recipient’s junk email folder as well.

It should be noted that the behavior described above is what the Exchange Online platform does by default. A system administrator can define their own spam/content filtering policies that treat these messages differently, e.g., deleting a message that gets scored with a 9 instead of putting it in the junk email folder. The default content filter policy (which, by the way, cannot be deleted) sits at the lowest priority, allowing a sysadmin to apply a higher priority to their own custom rulesets if so desired.

Now to the meat and potatoes – the issue we’ve seen crop up with some frequency is where a user reports that a lot of [obviously] spam messages are making it through to their inbox. In looking through the message headers, we see that said messages are getting scored with an SCL of 5 or 6, but for some reason they’re not going to junk mail. We check the content filter, and everything looks good there. So why are these messages not going to junk mail?

Simple.

One of the things you should check is to look at the Block/Allow settings in that user’s OWA. From the gear in the top right corner, click Options. From there, click Block or Allow on the left. More than likely, you’re going to see that the “Don’t move email…” option is selected. To enforce the spam filter to do what it’s supposed to do, choose the “Automatically filter…” option, and then click Save.

 

14-0709_1

14-0709_2

 

Voila!

 

Tech Tips for Techs (Intermediate Level): Analyzing Questionable Emails

Standard

 

CAUTION: Some of the following steps are above the level of beginners. If you are unsure about anything you read here, please call us at Everon (888-244-1748) and we can help you through it.

Sooner or later everyone receives an email that looks legitimate, but you have a feeling it could be a scam or a virus waiting to attack. Here are a few examples of questionable emails:

  1. From someone you know, but off topic.
  2. From a Company/Vendor warning of a problem with your account.
  3. Announcing you have a voicemail/fax waiting for you.
  4. Official-looking email from your bank requesting you to login.

It is always a difficult choice. If this is a legitimate email I need to follow through, but if it is a hoax it can cause a lot of problems I don’t need right now. What to do…?

First, do not open the any attachments or click on any links in the email. Now, if your Company has any procedures in place to address questionable emails, follow the recommended steps. Otherwise I recommend that you contact the sender and question the email.

  • From an individual: create a new email to them expressing your concern.
  • From a known Company/Vendor: call them and question the email.
  • From a new/unknown source, notify you supervisor – do not open, do not click inside the email, and do not forward.
  • From a Company/Bank regarding a personal matter: call them or login to the site AS YOU NORMALLY WOULD – do not use the link in the email.

I know, I know, none of this is new to you. That being the case, let’s take a questionable email and break it down.

NOTE: It is important you are familiar with the Windows feature “Hovering.” When you place the mouse over an object WITHOUT CLICKING, a popup will show you additional details. This is known as Hovering. Do not click when Hovering, as this will initiate the code associated with the object.

ATT1

Here is an email I recently received in my Hotmail account. At first glance everything seems okay. But let’s take a closer look at a few problems:

 

 

 

Wrong “From” email address

At the top of this email you can see that “From” is A T & T <hc6DqrJv.yiTuN.com>. Because I have configured my email to display both the label and actual address, I can see the actual email address.

You may only see the A T & T if you received this email. In that case, just hover over the A T & T and the popup will show you <hc6DqrJv.yiTuN.com>.

When you send an email, you can display your name in the “From” field (instead of displaying your email address). This is a very common and useful feature, but in this case it is being used to hide things from you. So now we know this is not really from AT&T but from some cryptic email address. This alone is enough to let you know to just delete this email.

Embedded links do not take you to AT&T

Now let’s take a look at the actual email. Really, everything looks fine: a known AT&T image, standard formatting and wording – nothing to lead you to believe this is a scam. That is, nothing until you hover over the links.

ATT2

 

The hover-text tells us that if we click on this link we will NOT be sent to an AT&T website. We will end up at  http://masefieldsaidelqd…. All the links in this email go to the same location. Again, this alone is enough to let you know to just delete this email.

Email Header Analyzer

Every email that is sent has embedded information that describes the path it took from the sender to you. This is called the Email Header and it’s not very easy to read. Here is the actual Email Header for our sample.

x-store-info:4r51+eLowCe79NzwdU2kR3P+ctWZsO+J
Authentication-Results: hotmail.com; spf=pass (sender IP is 87.124.110.208)
mtp.mailfrom=wwbjj@masefieldsaidelqd.com; dkim=none header.d=yiTuN.com; x-hmca=none
header.id=hc6DqrJv@yiTuN.com
X-SID-PRA: hc6DqrJv@yiTuN.com
X-AUTH-Result: NONE
X-SID-Result: NONE
X-Message-Status: n:n
X-Message-Delivery: Vj0xLjE7dXM9MDtsPTA7YT0wO0Q9MjtHRD0yO1NDTD02
X-Message-Info:
c21WZ1hAltI9DuizMAEE2QECpxSZUGZG4j2P0KvnFQ5Oq/wSiAiPSbOCWW7QmZMDONMEcOBjWMXYV9Dk2G3eyZRiTxZAdBpO5E1Xr5SqiAWdiuxGlA3k5kj+R//OvPfE4Jw5jOmv8EAwIUCNmc79xJKcP4737N1Q+CskaetIvY9RRY9PhyoYAHA+325kAM7Fj2b6LXibNlbSbtyWUAyW2QNDR/0bZpc
Received: from lzZGq2WEGeQ.com ([87.124.110.208]) by COL004-MC3F55.hotmail.com with Microsoft SMTPSVC(7.5.7601.22712);
Thu, 10 Jul 2014 01:25:28 -0700
From: =?utf-8?b?QSBUICYgVA==?=  <hc6DqrJv@yiTuN.com>
Message-ID: <RNUB4e5@S87Q1Jcgi.com>
Subject: XXXXXXXX, =?utf-8?b?TGltaXRlZC10aW1lIG9ubHk6IFNlZSBvdXIgRiBSIEUgRSBwaG9uZXMh?=
Reply-To: <uAKeDhIsr@FPHOSr.com>
MIME-Version: 1.0
Content-Type: text/html; charset=”utf-8″
Content-Transfer-Encoding: 7bit
Bcc:
Return-Path: wwbjj@masefieldsaidelqd.com
X-OriginalArrivalTime: 10 Jul 2014 08:25:28.0568 (UTC) FILETIME=[81AB7780:01CF9C18]
Date: 10 Jul 2014 01:25:28 -0700

That’s a lot of very obscure data. But don’t worry, there are free online tools that will break this down for you. One of my favorites is http://www.iptrackeronline.com/email-header-analysis.php. Just paste the header into the box and press submit header for analysis. Scroll down and you will see the following:

ATT3

Very interesting, the email originated from somewhere in the United Kingdom. Once more, this alone is enough to let you know to just delete this email.

Blacklisted Domains and URLs

If you are still not sure, you can use another free service call Blacklist check. One of my favorites is http://mxtoolbox.com/blacklists.aspx (which also has an Email header Analyzer). Just enter the first part of the email address we got from hovering (http://masefieldsaidelqd.com/) and press Blacklist Check.

ATT4

No surprise, the address is blacklisted (identified as a known source of spam and malicious emails). So now we have more than enough evidence to delete the email and notify our supervisor.

Always keep in mind that the best thing to do is alert you supervisor and / or your IT team immediately when you suspect you have a malicious email. It is always better to be safe than sorry.

What are the differences in all the wireless security types?

Standard

 

WPA image

Hello again! Today I am going to talk about wireless security and authentication types. You may have seen different security types in regards to wireless. The major types I am speaking of are WPA, WPA2, WEP, and 802.11X.  This all may seem overwhelming, but don’t worry. I will give an overview of these types to clear things up. Also, hopefully you have a administrator over your network that knows all about it. (If by chance you do not have a network admin on payroll, Everon’s services may suit you nicely.)

WPA and WPA2

These are fairly new security methods, so keep that in mind if you have older devices on your network. WPA (Wi-Fi Protected access) encrypts the information and utilizes verification checks to make sure that the security key has not been changed or modified. Also, WPA and WPA2 check to make sure that anyone accessing the network has authorization to do so. Being two different types, WPA2 is more secure than WPA, but WPA2 will not work on all devices — especially older ones. WPA can be used across a wider range of devices, however some OLDER devices might not be able to use this authentication method at all.

Diggin’ a little more in depth into WPA and WPA2, you have the ability to set up a pre-shared key (psk) so that ALL users utilize the same key. This is called WPA-Personal and WPA2-Personal. This is as opposed to WPA-Enterprise and WPA2-Enterprise, which hand out unique keys to each user. Utilizing the enterprise levels are obviously going to be more secure. These works alongside an 802.11X server and by handing out unique keys to each user, it makes it that much harder to crack.

WEP

Now moving on to WEP (Wired Equivalent Privacy). When you use WEP, you setup a network security key that everyone uses to connect. That key that gets created encrypts the data that users send to another user on the same network or device to device. WEP has since been deemed “old” and “outdated” and is not recommended for use. It is very easy to crack and anyone with a cell phone and an app is able to crack it in a 20 minute time frame, therefore WPA and WPA2 protocols are recommended.

802.11X

To enhance the security on the wireless network, 802.11X can be used along side WPA, WPA2, and WEP keys. It is mainly used in workplace environments for users to connect to the company network. As I briefly mentioned above, 802.11X utilizes a server to authenticate the users.

This all may seem like a foreign language to some, but that’s why we are here. Everon techs have a plethora of networking knowledge and would be more than happy to help you in your networking needs. Reach out to us via Facebook, Twitter, www.everon.com, or give us a call at (888)244-1748.

Tech Tips for Techs: Checking license and provisioning status in Office 365

Standard

 

In this TechTip, we’ll talk about the Powershell command you can use to check on license provisioning status for your users in Office 365. (Yes, I said Powershell. Unfortunately, Microsoft doesn’t expose a lot of information through the GUI, and this is one of those scenarios where we have to go to a CLI.)

Why is this important, you might ask? A common scenario that pops up where I leverage this command is when I have a user who is unable to access or use certain features in 365. Let’s say you have a user who is assigned an E3 seat and everything is working beautifully except for OneDrive (it’s grayed out in the bar across the top of the Microsoft portal). First thing I would personally check is to make sure that she has Sharepoint assigned and in good standing.

In order to do that, fire up Powershell, connect to 365 using a Global Administrator account, and run the following command:

(Get-MsolUser -UserPrincipalName user@domain.com).Licenses[0].ServiceStatus

 

An E3 license is going to give you output that looks like this:

14-0626_1

 

Using my OneDrive scenario, we can see why this particular user doesn’t have access. Because OneDrive leverages the Sharepoint platform, and the license is stuck in PendingInput status, there has been no provisioning and therefore, no access. At this point, unless this user is actively using their account or has litigation hold enabled, you could potentially remove the license, wait for it to disappear, and then reassign it. However, if this user has any data in the account at all or if lit hold is enabled, DO NOT unassign the license. In that case, you will want to open a ticket with your support provider, if it’s been stuck in PendingInput for over 24 hours.