You can match deal calculations to formula fields, linked-record averages to rollups, spreadsheet transfers to condensed results, and historical sales projections to AI. According to Pipedrive, formula fields instantly calculate values such as discounts, commissions or forecast amounts based on numerical or monetary deal fields.
Key takeaways
- Match deal calculations to formula fields. Pipedrive restricts this feature to Premium and higher plans, while Freshsales defines formula fields as auto-calculated fields deriving values from an admin-assigned expression Pipedrive Freshsales.
- Use rollups for linked-record averages. Airtable documents using the
AVERAGE(values)formula to roll up email open rates across connected campaigns, demonstrating linked-record aggregation Airtable. - Transfer pre-calculated spreadsheet results. Google recommends calculating the sum in the source spreadsheet before using IMPORTRANGE to fetch that single number, rather than transferring all data rows Google.
- Apply AI for historical projections. HubSpot describes using Breeze to project future sales based on closed-won deals from the past three months HubSpot.
- Identify the required calculation type. Determine if your weekly number needs a deal formula, a linked-record aggregate, a transferred result, or a historical projection before selecting a tool.
Sales forecast calculation tools: match the method to the number
The weekly forecast number requires a specific calculation mechanism. You must identify whether the number is derived from a single deal's attributes, an aggregate of multiple records, a transferred spreadsheet result, or a historical projection.
Pipedrive documents forecast-amount formulas using monetary or numerical deal inputs.
Freshsales formula fields derive their values from an administrator-assigned expression. The expression must be supplied when creating the field. This method relies on an administrator for the expression logic.
Airtable demonstrates averaging email open rates across connected campaign records with AVERAGE(values). This documents linked-record aggregation rather than a sales forecast. Naming a rollup Values can cause an existing field to be reused. This method calculates a number by combining values from multiple related records.
Google recommends calculating an aggregate in the source spreadsheet before transferring it. IMPORTRANGE receives at most 10MB for each request. This distinguishes moving a pre-calculated result from generating a forecast within the destination tool. The calculation happens in the source, and the result is transferred.
Calculation reference table from the publishers' own help pages
The table below lists the documented calculation mechanism for each of the five tools, using only the specific constraints and inputs described in their respective help pages.
| Publisher | Documented Calculation Mechanism | Key Constraint or Input |
|---|---|---|
| Pipedrive | Formula fields for custom data | Available only on Premium and higher plans Pipedrive |
| Freshsales | Auto-calculated fields from admin expressions | Value derived from an expression assigned by the admin Freshsales |
| Airtable | Rollup aggregation using AVERAGE(values) | Rolls up values from connected email campaign records Airtable |
| IMPORTRANGE for fetching calculated sums | Faster to calculate the sum in the source spreadsheet first Google | |
| HubSpot | AI projections using Breeze | Based on closed won deals from the past three months HubSpot |
Check which mechanism matches your current data structure before selecting a tool.
Pipedrive and Freshsales: formula fields compared
Formula fields in these systems calculate values automatically. The two help pages cover different details: Pipedrive documents the permitted inputs and the plan, while Freshsales documents who assigns the expression.
In Pipedrive, formula fields allow users to instantly calculate values such as discounts, commissions, or forecast amounts based on numerical or monetary deal fields, according to Pipedrive. The system restricts inputs strictly to monetary or numerical deal fields. This limitation means text-based fields cannot serve as inputs for these calculations. Access to this feature is plan-dependent; it is only available on Premium and higher plans. If your current subscription is below the Premium tier, this calculation method is not available for your forecast number.
Freshsales handles formula fields as auto-calculated fields that derive their value from an expression assigned by the admin, according to Freshsales. The creation process requires adding the specific formula used to calculate the field value when setting up the field. In Freshsales, the logic definition sits with the administrator.
When assembling the weekly number, check the platform rules: Pipedrive calculates from monetary or numerical deal fields on Premium and higher plans, and Freshsales derives the value from an admin-assigned formula supplied at creation. If you are using Pipedrive, confirm your plan includes Premium features before attempting to create the field. If you are using Freshsales, ensure the admin has assigned the correct expression to the field you intend to use for the forecast. These checks prevent errors in the calculated values that feed into your final number.
Check your plan eligibility and assigned expressions before the forecast call. For related field choices, see choosing a deal field type.
Airtable and Google Sheets: aggregate records or transfer a calculated result
Linked-record aggregation and spreadsheet transfers solve different parts of the weekly number. One method pulls data from related records into a single field. The other moves a pre-calculated result from one spreadsheet to another.
Airtable rollup fields A rollup field is added in the campaign project types table to roll up the values in the email open rate field, according to Airtable.
We strongly recommend naming your rollup field anything other than "Values," according to Airtable. If you title your field "Values," a new rollup field is created that attempts to create a new field to aggregate data named "Values." But if a field named "Values" already exists, the rollup field will use the existing field instead of creating a new one. Check your existing field names before creating a rollup to avoid unintended reuse.
Google Sheets IMPORTRANGE Sheets must download the entire range to your computer and will be affected by slow network, and is capped at 10MB of received data for each request, according to Google.
Restructure and condense your result before you use IMPORTRANGE, especially if you import from a frequently updated Sheet, according to Google. Calculate the aggregate in the source spreadsheet before transferring it.
Match the method to the need Use a rollup when the number depends on averaging values across connected records. Use a transfer when the number is already calculated in a separate spreadsheet. Do not use IMPORTRANGE to generate a sales forecast; it moves data. Do not use a rollup to transfer a pre-calculated total; it aggregates linked records.
Write down whether your weekly number requires a linked-record average or a transferred spreadsheet result. Then apply the corresponding documented mechanism.
Illustrative example: condense before you transfer
Suppose a source sheet lists 4100 deal rows and your forecast sheet needs only the total. Importing every row would mean downloading the entire range just to add it up. Instead, calculate the sum in the source sheet, then use IMPORTRANGE to fetch that single number. The forecast sheet receives 1 value instead of 4100 rows, which is the approach Google describes as faster.
HubSpot: historical inputs and scheduled projection checkpoints
The AI projection feature described in "Improve forecasting with AI projections" uses Breeze, HubSpot's AI, to project future sales based on closed won deals in the past three months, according to HubSpot. This mechanism relies on historical inputs. The documentation identifies specific checkpoints where these projections are provided. The 7-day projection is the AI projection provided on day 7 of the month. The 14-day projection is the AI projection provided on day 14 of the month. The 21-day projection is the AI projection provided on day 21 of the month. The 28-day projection is the AI projection provided on day 28 of the month.
These scheduled checkpoints allow for tracking accuracy over time. Average accuracy for a month is measured by calculating the average forecast accuracies of the five projections provided in a month (the 1-day, 7-day, 14-day, 21-day, and 28-day projections), according to HubSpot. This calculation includes the 1-day projection alongside the four later checkpoints mentioned above. When assembling the weekly number, this method provides a historical baseline. This structure supports a review of how the AI projection evolves as the month progresses. This method fits a forecast requirement that relies on historical sales patterns.
Which tool fits the calculation your forecast requires
Match the specific calculation required by your weekly forecast number to the documented mechanism in your chosen system. If your number relies on averaging values from connected records, use a rollup aggregation. The rollup aggregation formula is AVERAGE(values) so that all of the email open rates for every connected email campaign are rolled up, according to Airtable.
If your number requires moving a calculated total from a source spreadsheet, calculate the aggregate before transferring it. It’s faster to calculate the sum on that spreadsheet and then use IMPORTRANGE to fetch that single-number sum than to use IMPORTRANGE to directly transfer all 1,000,000 rows of data and calculate the sum in your spreadsheet, according to Google.
Write down whether today's forecast number requires a deal formula, a linked-record aggregate, a transferred spreadsheet result, or a historical projection. Then match that requirement to the documented mechanism. For deal calculations, check if your plan includes formula fields. For linked-record averages, verify the rollup formula. For spreadsheet transfers, confirm the aggregate is calculated in the source. For historical projections, identify the checkpoint schedule. For more on deal arithmetic, see these deal arithmetic options, and read the forecast period rules before you finalise the number.
Next step: do this today
Check the plan eligibility for formula fields. Confirm the rollup formula is correct. Verify the spreadsheet aggregate is calculated before transfer. Identify the projection checkpoint. These steps ensure the calculation method matches the documented mechanism.
FAQ: choosing a forecast calculation method
Which tools support different sales forecast calculation methods?
Each of the five tools covers a different calculation method. Pipedrive uses formula fields on numerical or monetary deal fields, and Freshsales uses formula fields built on an admin-assigned expression. Airtable aggregates values from connected records using rollup fields, Google Sheets transfers pre-calculated results with IMPORTRANGE, and HubSpot generates historical sales projections based on past closed-won deals.
Does my Pipedrive plan include formula fields?
Only if you are on a Premium or higher plan. Formula fields are available only on those plans, according to Pipedrive. These fields allow you to instantly calculate values such as discounts, commissions, or forecast amounts based on numerical or monetary deal fields.
How does Airtable average values from connected records?
A rollup field uses the AVERAGE(values) formula to aggregate values from connected records, according to Airtable. This mechanism is demonstrated by rolling up email open rates for every connected email campaign.
How should I transfer a calculated spreadsheet total with IMPORTRANGE?
Calculate the sum in the source spreadsheet, then use IMPORTRANGE to fetch that single number instead of transferring all 1,000,000 rows, according to Google. Restructure and condense the result first, especially if you import from a frequently updated Sheet. Keep in mind that the received data is capped at 10MB for each request, and Sheets must download the entire range to your computer.
How does HubSpot calculate average monthly projection accuracy?
It averages the forecast accuracies of the five projections provided in a month, according to HubSpot. These projections are the 1-day, 7-day, 14-day, 21-day, and 28-day projections.