Write Your Weekly Numbers Formulas in Google Sheets with Gemini

What This Does

Every week you have the same handful of totals: leads, booked jobs, revenue, labor hours and marketing spend. This guide sets up one tab that turns them into four ratios (close rate, average ticket, labor hours per job and cost per lead) and shows how to ask Gemini to explain a formula or draw a chart. The totals come from your books or your field service app's reports. The sheet does the arithmetic, and Gemini only helps you write and understand the formulas.

This is the same layout the Level 3 data-analysis guide checks, so keep the column order as written.

Before You Start

• Google Sheets is open in a browser on a computer
• Ask Gemini appears at the top right. Google's help page says Gemini in Sheets requires an eligible Google Workspace or Google AI plan, so ask whoever manages your Google account if the button is missing. Plan details are at workspace.google.com
• You have last week's totals from your books or your field service app's reports

Data caution. This tab holds weekly business totals only. Leave customer names, addresses and job notes out of the file. Check your Google account's Gemini settings for whether chats are kept or used to improve models, and ask your franchisor whether an AI tool is approved for anything beyond totals.

Steps

1. Set up the headings

On a new tab named Weekly Numbers, type these headings in row 1:

Columns A to F are typed in each week. Columns G to J are formulas. Format G as a percent and leave the others as plain numbers. Revenue and spend need no currency symbol.

2. Ask Gemini for the formulas

1. At the top right of the sheet, click Ask Gemini.
2. In the side panel, type a prompt that uses your headings, then press Enter:

In row 2 of the Weekly Numbers tab, write formulas for Close rate (Booked jobs divided by Leads), Average ticket (Revenue divided by Booked jobs), Labor hours per job (Labor hours divided by Booked jobs) and Cost per lead (Marketing spend divided by Leads). Each formula should return a blank, not an error, when the number it divides by is empty or zero.

1. Gemini proposes formulas. Click Insert to put one in a cell, or Retry for a different version. Google's page lists creating formulas, and an option to start from any cell by typing =, then a Gemini shortcut.

3. Check Gemini's formulas against these

Your formulas should behave the same as these. Paste them over Gemini's version if they differ, or if you simply prefer to type them:

G2: =IF(OR(B2="",B2=0),"",C2/B2)
H2: =IF(OR(C2="",C2=0),"",D2/C2)
I2: =IF(OR(C2="",C2=0),"",E2/C2)
J2: =IF(OR(B2="",B2=0),"",F2/B2)

Copy row 2 down as far as you need. Each one returns a blank instead of an error when the divisor is empty or zero.

If a formula cell shows an error, hover over the cell and click Fix. Google's page says Gemini then reviews the formula and analyzes the issue. Read the fix before you accept it.

4. Test with two invented weeks

Type these into rows 2 and 3. Both weeks are invented.

What you should see:

• W5: close rate about 42.9 percent (18 divided by 42), average ticket 240, labor hours per job 5, cost per lead about 14.29.
• W6: a closed storm week. Close rate 0 percent, cost per lead about 16.67, and Average ticket and Labor hours per job stay blank, not an error, because there were no booked jobs.

If your results differ, compare your formula to the ones above.

5. Ask Gemini to explain or chart

Try these prompts in the side panel:

• "Explain the formula in H2 in plain words."
• "Make a line chart of Close rate by Week."

Google's page says a chart can be previewed first. When you click Insert, the chart goes into a new tab with its underlying data. Google's page also notes that the generated chart does not link to changes in the original data, so ask for it again after new weeks go in.

Real Example

Scenario: Your franchise business consultant call is tomorrow and you want this week's ratios in one place.

What you do: Type the six totals for the week into A to F. The four ratios fill in from the formulas.

What you get: For the invented W5 row, a close rate near 42.9 percent and an average ticket of 240. Read these from the sheet. Do not ask a chatbot to work them out.

Tips

• The numbers come from your books or your field service app's reports. This sheet states no benchmark and no "good" number for any ratio.
• Keep every calculation in the sheet's formulas. Gemini explains and writes formulas, but the sheet is what you rely on.
• Read each formula Gemini writes before you copy it down.
• For your call prep, the Level 4 prompt chain uses the ratios from this tab.

Tool interfaces change. If a button has moved, look for similar AI options in the same menu area.