How to calculate offer acceptance rate in Excel

Step 1: An offer log

One row per offer, not per role: a role that needed two offers gets two rows. Record the role, department, date the offer went out, a status (Accepted, Declined or Pending), the date the candidate answered and, for declines, a short reason from a fixed list.

Keep the status words exactly the same every time. COUNTIFS treats "Declined" and "Declined " (with a trailing space) as different values. A data validation drop-down on the status and reason columns stops the drift.

Days to answer in column H is =IF(F2="","",F2-D2), so pending rows stay blank instead of showing a huge negative number.

Step 2: The rate, with pending offers left out

Divide accepted by accepted + declined. Six accepted and five declined gives 6 / 11 = 54.5%.

The two pending offers aren't failures yet, so they stay out of both counts. If you divide by every offer in the log (=COUNTIFS(E2:E14,"Accepted")/COUNTA(E2:E14)) you get 46.2%, and the rate drops every time you send a new offer, then jumps when it's answered.

If the company withdraws an offer, give it its own status such as Withdrawn and leave it out of both counts too. The candidate never had a chance to say yes.

By department, and by month

Add a department condition to both counts. In Excel 365, one LET formula builds the whole table: it lists each department with UNIQUE and works out each rate. Support and Operations accept 66.7% of offers; Finance accepts 40% (2 of 5).

For a month, add a date range on the offer date: =COUNTIFS(E2:E14,"Accepted",D2:D14,">="&K2,D2:D14,"<="&K3) counts October's accepted offers, 3, against 3 declined, so 50%. Put the month's first and last day in K2 and K3 instead of typing dates into the formula.

Decide whether a month means offers sent or offers answered that month. Here both give 3 and 3, but not the same offers: O-105 went out in September and was declined in October, and O-110 went out in October and was declined in November. Pick one and label it.

With five or ten offers a department, one decision moves the rate by 10 or 20 points. Show the counts next to the percentages.

Why offers get declined

The rate tells you how often; the reasons tell you why. Tally the reason column: =LET(r,UNIQUE(FILTER(G2:G14,G2:G14<>"")),HSTACK(r,COUNTIFS(G2:G14,r))) lists each reason once with its count. Salary 2, Other offer 2, Schedule 1.

Salary accounts for 40% of the declines here (2 of 5). Keep the reason list short and fixed, five or six options, or every recruiter writes it differently and the tally falls apart.

Days from offer to answer

AVERAGEIFS on the days column, split by status, shows a pattern recruiters often feel but rarely measure: accepted offers were answered in 1.8 days on average, declines took 5.2 days. A candidate who takes a week is often waiting on another offer.

AVERAGEIFS skips the blank pending rows, so they don't drag the average to zero. The median across all answered offers is 2 days and the slowest took 7.

For pending offers, the age as of today is =TODAY()-D13. On November 6 the two pending offers are 4 and 2 days old.