Blog

submitted by /u/Travi100 [link] [comments]

When you’re calculating the average for a set of values, you’re generally working with values that have the same weight and importance.
 
But what happens if some values weigh more than others? This is where the weighted average formula comes in.

In this article, we will break down how to use this formula in Excel, plus provide some examples.

How to Calculate Weighted Average in Excel

To calculate the weighted average in Excel, you must use the SUMPRODUCT and SUM functions using the following formula:
=SUMPRODUCT(X:X,X:X)/SUM(X:X)
This formula works by multiplying each value by its weight and combining the values. Then, you divide the SUMPRODUCT but the sum of the weights for your weighted average.
Still confused? Let’s go over the steps in the next section.
Using SUMPRODUCT to Calculate Weighted Average in Excel
1. Enter your data into a spreadsheet then add a column containing the weight for each data point.

2. Type =SUMPRODUCT to start the formula and enter the values.
3. Click enter to get your results.

How to Find Weighted Moving Averages in Excel
A weighted moving average is a technique used to keep the time period of the average the same as you add new data or give more weight to certain time periods. This can allow you to identify trends and patterns more easily.
For instance, say you have the number of views your website got in the last five days, you can easily determine the average views in a five-day period.
Now, say the next week, I ask for the five-day average, you would use data from the last five days, not the original five days from the previous week.
As such, you’re still relying on the same time period but updating the data to generate the moving average.
For a weighted moving average, you give more weight to certain time periods than others. You may say that day 5 weights 60% with the remaining percentages decreasing by day.
As such, you’ll need to manually calculate this formula.
WMA = [value 1 x (weight)] + [value 2 x (weight)] + [value 3 x (weight)] + [value 4 x (weight)]
Once you get the hang of it, using the weighted average formula is easy. All it takes is a little practice.

As a marketer, you already know you love Google Sheets for storing data, tracking performance metrics, and creating collaborative reports. But are using the full arsenal of Google Sheets shortcuts available to streamline your workflow and save valuable time?

Whether you’re experienced with Google Sheets or you’re just getting started, you’ll be happy to know there are many simple and time-saving Google Sheets keyboard shortcuts at your disposal.
Keeping track of the numerous keyboard shortcuts may sound daunting but, luckily, I’m here with a list of Google Sheets shortcuts you can bookmark and return to again and again.

30 Google Sheets Keyboard Shortcuts
1. Select column
Ctrl + Space (PC and Mac)
2. Insert columns to the left
Alt + i, then C (PC), Ctrl + Options + i, then C (Mac)
3. Insert columns to the right
Alt + i, then O (PC), Ctrl + Option + i, then O (Mac)
4. Select row
Shift + Space (PC and Mac)
5. Insert rows above
Alt + i, then R (PC), Ctrl + Option + i, then R (Mac)
6. Insert rows below
Alt + i, then W (PC), Ctrl + Option + i, then B (Mac)

7. Select all
Ctrl + A (PC), Command + A (Mac)
8. Fill range
Ctrl + Enter (PC), Command + Enter (Mac)
9. Fill down
Ctrl + D (PC), Command + D (Mac)
10. Fill right
Ctrl + R (PC), Command + R (Mac)

11. Save
Ctrl + S (PC), Command + S (Mac)
12. Open
Ctrl + O (PC), Command + O (Mac)
13. Paste values
Ctrl + Shift + V (PC), Command + Shift + V (Mac)

14. Insert new sheet
Shift + F11(PC), Shift + Fn + F11 (Mac)
15. Insert time
Ctrl + Shift + ; (PC), Command + Shift +; (Mac)
16. Insert date
Ctrl + ; (PC) , Command + ; (Mac)
17. Insert date and time
Ctrl + Alt + Shift + ; (PC), Command + Option + Shift + ; (Mac)

18. Format as decimals
Ctrl + Shift + 1 (PC and Mac)
19. Format as time
Ctrl + Shift + 2 (PC and Mac)
20. Format as date
Ctrl + Shift + 3 (PC and Mac)
21. Format as currency
Ctrl + Shift + 4 (PC and Mac)
22. Format as percentage
Ctrl + Shift + 5 (PC and Mac)
23. Clear formatting
Ctrl + (PC), Command + (Mac)

24. Show all formulas
Ctrl + ~ (PC and Mac)
25. Insert array formula
Ctrl + Shift + Enter (PC), Command + Shift + Enter (Mac)
26. Collapse an expanded array formula
Ctrl + E (PC), Command + E (Mac)
27. Show/Hide Formula Help
Shift + F1 (PC), Shift + Fn + F1 (Mac)
28. Full Compact Formula Help
F1 (PC), Fn + F1 (Mac)
29. Absolute/relative references
F4 (PC), Fn + F4 (Mac)
30. Toggle Formula
F9 (PC), Fn + F9 (Mac)

An Alternative to Google Sheets Custom Keyboard Shortcuts
In the past, Google Sheets custom keyboard shortcuts were an option users could perform to customize their shortcuts in a way that suited them best. However, nowadays custom keyboard shortcuts in Google Sheets are not an option.
But don’t worry! Another feature that can help you further streamline your work in Google Sheets is the ability to use compatible keyboard shortcuts from other digital spreadsheets, like Excel, in Google Sheets.
To do this, press Ctrl + / then click the button next to “Enable compatible spreadsheet shortcuts.”

Afterward, you’ll have more than 100 new keyboard shortcuts you can use to record and sort your data in Google Sheets.
Work Smarter, Not Harder, with Google Sheets Keyboard Shortcuts
Keyboard shortcuts allow you, as a busy marketer, to streamline your workflow and cut down on the time it takes to record and sort data in Google Sheets. Best of all, these shortcuts are easy to implement whether you’re on a Mac or PC.
Not only will these time-saving shortcuts make data recording more convenient, they’ll also allow you to spend more time focusing on other tasks —because, let’s face it, a marketer’s work is never done.

When it comes to business, the old adage “You’ve got to spend money to make money,” couldn’t be more true. Aside from the financial outlay to develop your product and manufacture it, and all the overhead that comes with owning a business, you are responsible for marketing your product so the public knows that your product exists.

You’ve set aside some marketing dollars to spread awareness and attract people to your brand and your website. You’ve looked into a variety of advertising options, and determined that pay-per-click (PPC) advertising is a sound way to spend your marketing budget. After all, you only pay when users click on your ad, so you only pay to advertise to people who are at least mildly interested in what you offer.

PPC advertising can provide a healthy return on investment – unless you become the victim of click fraud. By understanding what click fraud is, and being aware of the tools that are available to protect yourself and your investment, PPC can be an excellent choice to market your business.
What is click fraud?
Click fraud happens when a person or a bot clicks on an ad, button, or hyperlink to trick the platform into thinking there is more interaction than there actually is.
With PPC advertising, click fraud can happen for one of two reasons:

The website owner who is hosting the ad will click on the link repeatedly to increase the amount of money the business must pay them.
A company’s competitor may employ click fraud to divert from the business’s marketing budget.

Interestingly enough, sometimes it’s the business itself that will engage in click fraud. Search engines like Google rely on the click-through rate – how many people have visited a site because of a certain link – to determine search rankings. Sometimes, a business owner will attempt to scam the system, inflating the number of clicks through click fraud and moving up the search engine ranks so that more actual users will find and visit their page.
Any type of click fraud can be damaging to a business which is why you’ll want to protect yourself against this illegal practice.
What is a click bot?
You’ve likely heard of click fraud bots, but do you know how they operate? It’s estimated that bots comprise roughly 52% of all internet traffic. Harmful bots (like fraud bots) make up a large part of this number.
So what are click bots and what are they doing to our websites? Click bots are designed to carry out click fraud. They vary from relatively simple (access webpage and click on the desired link) to advanced (mimic the actions a human would take such as moving the mouse and clicking at uneven intervals). The more advanced a bot is, the more difficult it is to distinguish it from a legitimate user.
Rather than attempt to create hundreds of thousands of clicks from one device with a single IP address (this bot traffic would be immediately suspicious), bots are installed across many devices, often through malware. This means that the device owner is completely unaware that their electronic property is being used for nefarious purposes.
Bots aren’t always to blame for click fraud. Some scammers will employ people to manually perform click fraud. While it’s more difficult to recognize human fraud, it’s considerably less efficient than utilizing fraud bots.
Click Fraud Prevention
Instances of click fraud are not uncommon. A recent study from the University of Baltimore found that in 2020 click fraud cost marketers over $35 billion.
Search engines like Google have put practices in place to protect businesses from click fraud. These include:

Automated detection systems built with machine learning and complex algorithms to keep ad platforms clean.
Manual reviews when invalid activity is suspected or brought to their attention.
Advanced research to uncover sources of invalid traffic and prevent them from entering their networks.

Knowing that Google is looking out for your business is great news, though it’s still important to be proactive to protect your business. The first step is creating ads that are more difficult for scammers to target. You can consider allocating more budget to social media ads that are less keyword-focused and therefore harder to search for.
You also have the option to be more targeted with your desired audience which will lessen your chances of encountering click fraud. Being more specific about the geographic locations your ads target and avoid can also provide an added layer of protection from click fraud.
Next, you can help prevent click fraud by keeping a close eye on your ads and your ad spend. The moment you suspect fraud, it’s time to jump into action. If you’ve detected fraudulent clicks coming from specific IP addresses, Google Ads will allow you to block these IP addresses.
Click Fraud Detection
While preventing click fraud is an admirable goal, it’s not always possible. Scammers have created highly advanced and indiscernible methods for perpetrating click fraud which is sometimes impossible for the average business to protect against.
Thankfully, there are a number of click fraud detection protection services available. Here are a few recommendations.
1. ClickCease
Image Source
ClickCease blocks bot traffic from Google and Facebook ads before these clicks can impact your ad spend budget. Users can also create custom detection rules to provide the level of support their business needs.
Price: Standard Plan starts at $59 per month; Pro Plan starts at $79 per month
2. PPC Shield
Image Source
PPC Shield offers holistic support to protect your Google Ads campaigns from fraudulent clicks. Notable features include instant blocking of suspicious IP addresses, detailed reporting, and a budgeting tool to help you allocate ad spend saved from click fraud prevention.
Price: Basic Plan starts at $39 per month; Standard Plan starts at $55 per month; Professional Plan starts at $119 per month
3. AppsFlyer
Image Source
Looking for protection for your ads against mobile fraud? If so, the AppsFlyer fraud protection tool is worth looking into. This tool provides multi-layered protection against click fraud and has an extensive partner network to keep companies safe from emerging threats.
Price: Free with paid plans starting at 6 cents per conversion
4. Singular
Image Source
Singular provides a comprehensive fraud prevention tool with a suite of features including iOS and Android click prevention, hyper-engagement detection, and geographic outliers to keep your ad placements safe.
Price: Free, with custom paid options available
5. ClickGUARD
Image Source
ClickGUARD’s platform is a powerful click fraud prevention system. Offering a Google Ads shield to protect conversions and prevent invalid clicks and advanced bot detection, this tool identifies and prevents threats from targeting your ads.
Price: ActiveGUARD plan starts at $79 per month; PremiumGUARD plan starts at $79 per month; EliteGUARD plan starts at $99 per month
Unfortunately, click fraud is a very real threat to your PPC advertising campaigns. While it has the potential to drain your budget and leave you short of your marketing goals, with the right protections in place, it’s still a viable option for your business. Investing in education and services to prevent click fraud is a worthwhile expense.