Excel Power Users: Don’t Be Scared - Use Arrays!

Standard

array

 

As a frequent user of excel, at what I would consider advanced-level expertise, I spend my days flying through data connections, pivots, all kinds of complex formulas, and even a dash of VBA. But there was still one hurdle I hadn’t jumped. For some odd reason those squiggly brackets { } (technically I believe they’re called “braces”) had intimidated me from dabbling in the world of arrays. Something felt unnatural about hitting ctrl + shift + enter before exiting a formula. What magic would happen behind the scenes if I pushed those buttons simultaneously? I understood the logic and language of Excel, so asking it to “work differently” just seemed bizarre. I found myself working around actually using arrays by means of  extra columns, pivots, and very elaborate lookups and formulas.

This past week, however, I faced my fear and jumped in… three keys at a time!

Game changer!

Imagine you have a list of contact dates and clients. You want to figure out how recently each person has been contacted by running a quick summary on the data. Sure, you could throw a pivot on the data, but what if you needed it in the table format? There is not a “MAXIF” formula to perform this action. In fact, for all its strengths, there is a gap on available “IF” formulas in Excel. Enter-in arrays! Arrays give you the power to combine formulas that analyze data in tabular form without having to pivot the data.

Here’s how it works:

Let’s say you have a list of sales, and you want to be able to reach out to clients with whom you haven’t spoken in a while. Knowing the last contact date alongside their YTD sales will help you make sure that you are staying on top of communication with your best customers. Here’s a list of sales with contact dates. We can easily throw a “SUMIF” formula in to calculate the running total, per customer, per line. But finding out the most recent (or “max”) date is not so easy, because you cannot make a “MAXIF” formula. Instead, we can “nest” them with an array.

image1

In everyday language, we need the formula to perform the following tasks:

=MAX(number1, number2,…)

where the numbers are all in column A, “Contact Date.” If we just do MAX, it won’t take the customer into account. We need to add a criterion to also look for the max date of that customer.

image2

In theory it should be this:

=MAX(IF(Customer Name = This Row’s Customer Name, THEN return the Max date from column A, OTHERWISE return a 0)

image3

Unfortunately it doesn’t wrap the IF with the MAX and it produces a result that is the max overall. So let’s jump into those scary squiggly braces and see what we can do.

When you use the exact same formula — but before hitting enter at the end — instead, hold down CTRL + SHIFT + ENTER. You’ll see that Excel adds braces { } around the formula. When you copy this down, the formula magically evaluates both conditions across all the data you’ve selected. Voila! You have now added analytics to your table.

image4

NOTE:  You cannot simply add braces to your formulas to make this happen. You have to hit ctrl + shift + enter to make Excel perform the array formula.

So… What’s the takeaway? What can this do for you? By performing this array formula and quickly sorting my list… looks like I better reach out to Examples R Us. They’ve spent the most and it’s been the longest since they’ve been contacted.

image5

Imagine what arrays can do to inform your business!

How Do I Know If I’ve Found “The One?” Finding the right IT provider

Standard

 

Finding an IT provider with the technical chops to support your business is essential—of course it is. But a good IT services firm is not just in the business of delivering technology management and support. Their role as a partner to your organization can be the difference in having the capability to grow, in the productivity of your staff, and in the protection you have for your most critical asset: data. So how do you know if you’ve found the one? A good starting place is to first understand your business’s needs in the following areas:

  • Threshold for downtime. Knowing whether your company can tolerate downtime of email, shared drive, websites, internet connectivity, file access, etc. is a critical starting point to finding your IT provider. A business that can keep functioning in the absence of email and internet while awaiting technical assistance may be more apt to employ an hourly consultant and take the gamble of their system going down. However, a business where downtime equates to a loss of productivity and revenue is a better fit for a full time IT service provider who both monitors and manages their network.
  • Cash Flow/Budget. Understanding the importance of both predictability and overall IT budget inside your organization is critical. For instance, a company with volatile cash flow month to month may think they should sign up for hourly services with little-to-no set, monthly charges. While this may seem more affordable on the surface, and can accommodate a smaller budget, it can backfire when you need support. If systems go down and the company has no threshold for downtime, they’ll more than pay for it in immediate hourly support, which can financially strain a business that already has no buffer in its monthly cash flow. Some businesses favor predictability over affordability because of the inherent stability. We tend to think of this type of plan as ‘IT insurance.’ Businesses pay the same, flat rate each month so their bills don’t change, and whether they need support one time or every day for hours on end, they can call without hesitation or worrying about the minutes adding up. These plans pay for themselves in the long run for both the customer and the IT provider. Rather than troubleshoot isolated incidents as they occur, a technician has more time to investigate and stabilize the overall infrastructure, reducing future incidents.
  • curt and jesseAvailability of Live Engineers. The time it takes to connect with a live engineer is often a deciding factor when selecting the IT provider. It’s important to know a provider’s average hold times and how often calls are routed to a live engineer when calling a help desk, rather than having to leave voicemails and wait for call backs. Understanding the frequency of how often your business may utilize the service, compared to the time it takes to connect, is imperative in finding the right solution and setting the right expectation of service.
  • Bench-strength. How complex is your IT landscape? Some businesses are 2 PCs, email, and some cloud storage, while others may have multi-site locations, multiple networks, servers, hosted email, and onsite backup solutions. Understanding the capabilities and depth of coverage in your IT solution is key. While an independent consultant may provide reliable onsite support, there’s often more benefit to having the skillset of an entire IT firm, and it’s generally close to the same cost. Finding a provider that has a large enough team to ensure high skillset, quality service, effective communication, and availability in line with the needs of your business is critical. A “two guys and a garage” IT shop may be affordable and offer fantastic hands-on service, but when someone is sick, busy, or on vacation it’s the same as having no IT at all. A firm that is too large, on the other hand, could have a watered-down approach or less familiarity with your infrastructure.
  • Customer Management. How important is it to you and your business that your IT provider knows your business and employees? A good IT services firm is virtually an extension of your business, so knowing how closely your account is proactively managed and the capabilities of the IT firm’s account management staff is critical. Find out how often you will communicate with an account manager and whether you will have a dedicated manager or just a general customer service account team. The partnership between your business and the account manager can be essential to managing your IT as your business grows and evolves.
  • Flexibility. Not one IT solution is right for all businesses. When vetting IT vendors it’s important that your IT provider work closely with your business to tailor a solution specific to your business’s needs. There is no one-size-fits-all IT plan, and it’s in your best interest to find an IT provider who carries out the recommendation of particular solutions via a conversation, rather than a directive. A sign of a capable IT provider is their ability to flex and adapt to the needs of your particular business, rather than force all of their customers into one specified set of technologies.
  • Stability. Small IT consultants and management firms have a great deal of instability. Knowing the track record or your provider is important. Engaging a new IT provider is not a small undertaking, and not one that most businesses care to experience frequently. Before partnering with an IT provider ask questions as to their history, future, stability as an organization, and the staff. This should give you a gauge of how likely they are to be there for you for years to come.
  • Customer Service. Sure, this one seems straightforward; unfortunately, that is not always the case. Hiring an IT staff, firm, or consultant is not only a question of technical skillset, but also of customer service soft skills. These individuals will be dealing directly with your non-technical staff during frustrating and stressful situations. Finding a solution where the technicians are not only technically capable but also patient, helpful, and personable through the experience can greatly improve the morale of your staff when they have IT troubles. Important questions to ask are: how do you measure customer service levels? …what qualities do you look for in your employees? …what is the work environment like? Happy employees who enjoy working with people not only make for a more pleasant environment but also a better experience for the end user on the phone. We find that the more we can do to encourage and support a cohesive and happy team, the better our service levels and the quality of employees we attract. 

  mountain flowersI hope these topics help guide you on the path to selecting the right IT provider for your business’s needs. To discuss how Everon may be a good fit for your organization and to walk through your technology needs, please feel free to contact me at [email protected]. Or, you may claim a free technology assessment at www.everonit.com/technologyassessment.

The Shocking Cost of Data Loss

Standard

The cost of data loss

Think about these facts revealed in our new infographic:

  • A hard drive in the US dies every 15 seconds
  • SMBs experience an average of 6 outages a year
  • 70% of SMBs experiencing a data loss shut down within 2 years

All true. And a little bit scary considering that we rely on computer systems to perform vital business functions and to store years and years of customer and financial data. Do you think your business could survive a data loss? Check out our infographic to understand what that could mean to your business.

When it comes to protecting your data and your technology, we can help. Interested in learning how?  Register for a free backup assessment at: http://www.everonit.com/data-loss-infographic/

 

 

Are you ready for adverse weather? Quick checklist to prepare your office for Hurricane Sandy

Standard
Hurricane Sandy, a category 1 hurricane is expected to make landfall on the East Coast this weekend.  We would like to make sure our clients in all areas of their country know how to keep their systems safe during adverse weather.
Here’s a list of things you can do in your office to prepare for major storms and outages:
•Unplug all computer hardware and if necessary move to the middle of the room, if possible move critical equipment to a secure and windowless room.
•Cover any terminal stations or printers with plastic bags.
oBefore removing the bags after the storm make sure to touch something metal to eliminate any static electricity prior to touching the equipment.
•Move any critical software, accessories, drives, etc. to a place where they will not get wet or damaged.
•Always make sure you have your information backed up off-site in case of damage to your equipment.
•Make sure you have an email continuity system in place so email communications can remain uninterrupted in the event of an outage
If you’d like help to make sure your data and email are safe and backed up off-site please contact us today at [email protected]
To track Hurricane Sandy’s path click here.  To see more tips on how to prepare for a hurricane and flooding please click here.
Thank you and stay safe,
The Everon Team
1.888.244.1748

sandy

Hurricane Sandy, a category 1 hurricane is expected to make landfall on the East Coast this weekend.  We would like to make sure our clients in all areas of their country know how to keep their systems safe during adverse weather. Here’s a list of things you can do in your office to prepare for major storms and outages:

  • Unplug all computer hardware and if necessary move to the middle of the room, if possible move critical equipment to a secure and windowless room.
  • Cover any terminal stations or printers with plastic bags.
  • Before removing the bags after the storm make sure to touch something metal to eliminate any static electricity prior to touching the equipment.
  • Move any critical software, accessories, drives, etc. to a place where they will not get wet or damaged.
  • Always make sure you have your information backed up off-site in case of damage to your equipment.
  • Make sure you have an email continuity system in place so email communications can remain uninterrupted in the event of an outage.

If you’d like help to make sure your data and email are safe and backed up off-site please contact us today at [email protected] To track Hurricane Sandy’s path click here.  To see more tips on how to prepare for a hurricane and flooding please click here. Thank you and stay safe, The Everon Team 1.888.244.1748 [email protected]

Do you backup? Join us for a special event!

Standard

Protect Your Data. Protect Your Business.
Total Data Protection™ with Everon Technology.

TDP banner header

Did you know…?

  • 70% of businesses who suffer a serious data loss are out of business within 2 years!
  • The average small business experiences an average of 6 outages a year.

lockCan your business afford costly downtime?
Your business data is too valuable to leave its safekeeping to chance. That’s why the team at Everon has created the only data protection plan that exclusively meets the needs of small businesses, and is backed by our commitment to Remarkable Service™.

Join us for an informative webinar to learn how to protect your business from both everyday and catastrophic loss of data. We’ll cover…

  • Why small businesses should have a disaster recovery and data protection plan
  • 3 key features every data protection strategy should have
  • How to protect your business against natural disasters, hardware failure, and human error.

Register today at: www.everonitsupport.com/tdpwebinar

Protect Your Data. Protect Your Business.
Total Data Protection™ with Everon Technology

Speaker: Chris Mackin
Wednesday, September 12, 2012
1:00 P.M. EDT

Reserve your spot today and receive a valuable offer worth up to $2000!!

Mackin headshotJoin Chris Mackin, Everon’s Director of Sales for this informative webinar. Chris has been with Everon since the company was founded in 2003. With over 15 years of telecom, data security and small business technology expertise Chris is an expert in helping small to medium size businesses select and implement the right IT solutions to increase productivity and protect their most important asset– their data!

Thank you,

Everon Technology Services