How to Calculate Your Online Reputation Score (Formula + Spreadsheet)

Before you build this, it's worth knowing what the number is actually for. We cover that separately in what an online reputation score is — this article assumes you already want the number and just need the math.

The three ingredients of a reputation score formula

A reputation score formula worth trusting needs three things working together. Leave any one out and the number gets easy to game or easy to misread.

1. A weight per platform. Not every review source matters equally to a customer deciding whether to buy. A Google rating tied to local search results should carry more weight than a rating on a directory almost nobody browses before making a decision.
2. A volume factor. A platform with 300 reviews should influence the final number more than a platform with 4 reviews, even if the 4-review platform has a higher average. Otherwise a brand-new, barely-reviewed profile can outscore a well-established one on a lucky streak.
3. A recency adjustment. Older reviews should count for less than recent ones, because they describe the business as it was, not as it is now.

Step 1: Pull your raw numbers per platform

Start by listing every platform where you collect reviews — typically Google, Yelp, Facebook, Tripadvisor, Trustpilot, the App Store, Google Play, and Apple Maps, depending on your business. For each one, record:

• Total number of reviews
• Current average star rating

That's your raw data. Nothing clever yet — just the numbers each platform already shows you on your profile.

Most of this is faster to find than it looks. Google and Facebook show both numbers right on your business profile. Yelp, Tripadvisor, and Trustpilot show them on your public page without logging in. The App Store and Google Play show them in your developer or publisher dashboard. Apple Maps pulls its rating from Apple Business Connect if you've claimed that listing. Budget ten minutes to gather all of it the first time; after that, updating the sheet monthly takes a couple of minutes per platform.

Step 2: Assign a weight per platform

Decide how much each platform should count toward the final number. There's no universal "correct" weighting — it depends on where your customers actually look before buying — but a reasonable starting point is to weight more heavily toward whichever platform shows up most prominently in your local search results and on your Google Business Profile, since that's usually what a new customer sees first.

Pick weights that sum to something easy to work with, like 10, so the math stays simple. For a typical local business, Google often ends up carrying roughly a third to half of the total weight, with the rest split across whatever other platforms matter to that category — Tripadvisor for hospitality, the App Store and Google Play for a mobile product, and so on.

A few category-specific starting points, to adjust from rather than copy exactly:

• Local service businesses (contractors, clinics, salons) — Google tends to dominate, since it's what shows up in the local search results and map pack a customer sees first. Facebook and Yelp usually trail behind it.
• Hospitality and travel — Tripadvisor often deserves weight close to Google's, since travelers specifically seek it out before booking, in addition to whatever they see in search.
• Apps and software — the App Store and Google Play ratings usually matter more than any review site, since they sit directly in the purchase or install flow itself.
• E-commerce and services sold online — Trustpilot frequently carries real weight here, since it's the platform many online shoppers check specifically before trusting a site they haven't bought from before.

None of this is a formula you can look up — it's a judgment call based on where your own customers actually say they found you, which is worth asking new customers directly if you're not sure.

Step 3: Build a volume factor

The simplest honest way to account for volume is to treat each platform's review count as a multiplier on how much that platform's average actually counts, relative to the other platforms — rather than letting every platform's average carry the same influence regardless of how many reviews back it up.

A workable approach: use each platform's review count directly inside the weighted average as part of the weighting, so a platform with a deep review history naturally pulls more of the final number toward its average than a platform with only a handful of reviews. That's the same mechanic behind a basic weighted average in statistics — more data points, more influence on the total.

Step 4: Apply a recency discount

Recency is the hardest piece to do cleanly by hand, so keep it simple. One workable approach: split each platform's reviews into "last 12 months" and "older than 12 months," calculate the average separately for each bucket, and weight the recent bucket two to three times as heavily as the older one when you combine them. It's a rough approximation of decay, not a precise curve, but it's enough to stop a few stale five-star reviews from propping up a number that should be falling.

If you want something closer to true decay, you can apply a simple multiplier that shrinks with age — for example, treating a review from this month at full weight and a review from two years ago at roughly half weight, scaling linearly in between. Either version solves the same problem: an old review shouldn't count the same as a new one.

Worked example (illustrative numbers only)

The numbers below are made up for the sake of showing the math — they don't describe any real business. Swap in your own review counts and averages to use this for real.

To get the weighted average rating: multiply each platform's rating by its weight, add those up, then divide by the total weight (5 + 2 + 2 + 1 = 10).

(4.6 × 5) + (4.1 × 2) + (4.4 × 2) + (4.9 × 1) = 23.0 + 8.2 + 8.8 + 4.9 = 44.9

44.9 ÷ 10 = 4.49 weighted average rating, on a 5-point scale.

Notice that Google — fewer reviews than you'd see on a mature profile in this example, but the heaviest weight — pulls the result closer to its own 4.6 than a flat, unweighted average of all four platforms would (which would land at 4.5). That gap is the entire point of weighting: it reflects where customers are actually looking, not just averaging every number you happen to have.

If you wanted to convert that 4.49-of-5 into a 0-100 scale to match what most reputation scoring tools display, you'd scale it proportionally (4.49 ÷ 5 × 100 ≈ 90) and then adjust down slightly for recency if a chunk of those reviews are old — this illustrative example skips that step to keep the table readable.

The formula in a spreadsheet

You can build the weighted average above in Google Sheets with SUMPRODUCT, which multiplies two ranges element by element and sums the result — exactly what you need for a weighted average without writing out each multiplication by hand.

If your ratings are in column B (B2:B5) and your weights are in column C (C2:C5), the formula looks like this:

=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)

That single formula recalculates your weighted average every time you update a rating or a weight — useful if you're checking this monthly and don't want to redo the arithmetic by hand each time. To layer in a basic recency split, add two more columns (recent average, older average) and run the same formula twice, then combine the two results with whatever recent-vs-older weighting you chose in step 4.

Mistakes that quietly wreck this calculation

A few ways this goes wrong, all easy to avoid once you know to look for them:

• Forgetting a platform entirely. If you only track Google and Yelp but your customers are also leaving reviews on Facebook or Trustpilot, your number is missing real signal — and probably missing real reviews you haven't responded to.
• Weighting every platform equally "to be fair." That feels neutral, but it isn't — it quietly overweights whichever platform has the fewest reviews or the least relevance to your customers, since it gets the same say as your highest-traffic one.
• Skipping the recency step because it's annoying. This is the step most people drop first when doing this by hand, and it's also the one that causes the most misleading results — a static score that hasn't moved in a year usually means nobody updated the recency split, not that nothing changed.
• Mixing star scales. Not every platform uses a 5-star scale consistently in how it displays rounding (some show one decimal, some round to the nearest half-star on the public page). Pull the precise average from the platform's dashboard where you can, rather than the rounded number shown on the public profile.

What this by-hand version can't do

This gets you a defensible, explainable number — which is exactly the value of doing it by hand once, so you understand what's actually inside the number you're looking at. But it has real limits compared to a tool built to do this continuously:

• It's a snapshot, not a live number. The moment a new review lands on any platform, your spreadsheet is out of date until you manually update it.
• Recency decay by hand is crude. A clean two-bucket split is a rough approximation; a proper decay curve needs to track the actual date of every review, which isn't practical in a spreadsheet you're updating by hand.
• It doesn't tell you which review moved the number. If your score drops three points, a manual spreadsheet tells you the new total — not which specific review caused the move or what to do about it.
• Pulling the raw data itself takes real time. Logging into eight platforms every month to copy review counts and averages is the part most people quietly stop doing after the second or third month.

This is the gap a tool like Tailwind Score is built to close. It connects directly to Google, Yelp, Trustpilot, Tripadvisor, the App Store, Google Play, Facebook, and Apple Maps, and automates every step covered above: platform weighting based on how much influence each one actually has on buying decisions, a volume factor so high-review platforms count for more than thinly-reviewed ones, and a recency factor so old reviews fade out instead of propping the number up indefinitely. The score is clickable down into the math, so you can see exactly which review moved it and by how much, and it surfaces the top three actions worth taking to move it further — instead of leaving you to guess from a static spreadsheet number what to actually do next.

Doing the calculation by hand once is a genuinely useful exercise — it demystifies the number and makes you a harder person to fool with a vague "proprietary algorithm" pitch from some other tool. But if you want the number to stay current without logging into eight dashboards every month, connect your profiles to Tailwind Score and let it do the pulling, weighting, and tracking automatically.