AI Prompts for Google Sheets Formulas

Write a Google Sheets formula to calculate the total sales for the specified time period of [TIME PERIOD] from the sales data in the range [RANGE]. Include any necessary conditions to filter the data by [PRODUCT CATEGORY] or [REGION], and provide the final formula in a format that can be directly copied into Google Sheets.

Generate a Google Sheets formula that calculates the average customer rating for [PRODUCT NAME] using the ratings in the range [RANGE]. Ensure the formula excludes any ratings below [MIN RATING] and provide the exact formula that can be directly used in a Google Sheets cell.

Construct a COUNTIF formula for Google Sheets to count the number of leads in the range [RANGE] that meet the criteria of [CONDITION]. Clearly specify the criteria, such as counting leads from [SOURCE] or leads marked as [STATUS]. Ensure that the output is a functional formula that can be directly used in a Google Sheets cell.

Write a Google Sheets formula that concatenates first names from the range [FIRST NAME RANGE] and last names from the range [LAST NAME RANGE] into a full name format. Ensure that there is a space between the first and last names in the output. Provide the exact formula that can be directly pasted into Google Sheets for use.

Draft a Google Sheets VLOOKUP formula to retrieve the price of [PRODUCT NAME] from the data range [RANGE]. Specify the column index number that contains the price information, and ensure that the formula includes an exact match condition. Provide the final output as a complete, ready-to-use formula.

Refine the following SUMIF formula to total sales in the range [RANGE] based on the criteria of [CONDITION]. Provide specific examples of how to specify the criteria, such as filtering sales from [REGION] or [PRODUCT TYPE]. The output should be a precise and correctly formatted formula that can be directly used in Google Sheets.

Modify the AVERAGEIF formula to calculate the average rating for [PRODUCT NAME] within the range [RANGE], using the criteria of [CONDITION]. Provide the complete formula in a clear format, and include a detailed explanation of how each component of the formula operates, particularly emphasizing the criteria used for filtering the ratings. Additionally, suggest ways to adapt the formula for various scenarios to enhance its usability.

Draft a nested IF formula to assign scores to leads based on the criteria specified in the range [RANGE]. Clearly define the scoring rules for [CRITERIA 1], [CRITERIA 2], and [CRITERIA 3], and provide the complete formula that can be directly used in Google Sheets.

Create a complex VLOOKUP formula that incorporates the IFERROR function to handle cases where [PRODUCT NAME] is not found within the specified range [RANGE]. Ensure that the formula returns a default value, such as 'Not Found', when an error occurs. Present the final formula clearly, and provide a brief explanation of how each component works, detailing the purpose of the IFERROR function and the logic behind the VLOOKUP implementation.

Write a Google Sheets formula that utilizes TEXTJOIN to dynamically concatenate values from the range [RANGE] based on the criteria specified in [CONDITION]. Include a delimiter of your choice, such as a comma or space, and ensure that the final output is presented as a clear and functional formula.

Revise the existing SUMPRODUCT formula used to calculate total sales from [RANGE]. Focus on optimizing its efficiency by analyzing the current formula to identify areas for improvement, such as minimizing the range size or simplifying criteria. Provide the optimized formula in a clear format, along with a brief explanation of each modification made and the expected impact on performance. Additionally, include specific examples demonstrating how these changes enhance calculation speed with larger datasets.

Refine a complex ARRAYFORMULA for bulk calculations in Google Sheets. Analyze the existing formula applied to the range [RANGE] for the specific calculation [SPECIFIC CALCULATION]. Optimize the formula to enhance performance for large datasets by reducing calculation time and improving efficiency. Present the final optimized ARRAYFORMULA and provide a brief explanation of the changes made, highlighting how each adjustment contributes to better performance.

Revise the COUNTIFS formula to efficiently count entries in the range [RANGE] based on multiple criteria, including [CRITERIA 1] and [CRITERIA 2]. Ensure the formula is structured to enhance performance by minimizing calculation time. Provide the optimized formula in a clear format, and include a brief explanation of the changes made along with the reasoning behind each adjustment.

Revise the existing FILTER formula in Google Sheets to create a dynamic range that adjusts based on the criteria of [CONDITION]. Provide a step-by-step explanation of how to set up the formula, including any necessary functions or references. Include the optimized formula itself, a brief description of how it updates automatically as data changes, and any assumptions made during the optimization process.

Draft a dynamic INDEX-MATCH formula to replace VLOOKUP for improved performance when searching for [PRODUCT NAME] in the range [RANGE]. Explain how to structure the formula to ensure it efficiently returns the corresponding value, and provide the exact formula as the output.

Validate the accuracy of a SUM formula in the range [RANGE]. Create a step-by-step testing guide that includes: 1) calculating the total using the formula, 2) comparing the result against the expected total of [EXPECTED TOTAL], and 3) identifying any discrepancies. Additionally, provide clear instructions for troubleshooting common errors, ensuring data integrity, and summarizing findings that highlight any issues discovered during the validation process.

Validate an AVERAGE formula in Google Sheets using sample data from the range [RANGE]. First, input the sample data into the specified range and calculate the average using the AVERAGE function. Next, compare the calculated average to the expected result of [EXPECTED AVERAGE]. Document the validation process in a structured report that includes sections for the input data, calculated average, expected average, and any discrepancies found. Additionally, provide a brief explanation for each step taken in the validation process to ensure clarity and accuracy.

Check the following VLOOKUP formula for errors: [VLOOKUP FORMULA]. Validate it by identifying common issues such as #N/A or incorrect references in the range [RANGE]. Provide a detailed report that includes the original formula, a list of identified errors, the corrections made, and clear explanations for each adjustment to enhance understanding of how the formula works.

Validate the nested IF formula located in the range [RANGE] by testing all possible conditions. For each condition, specify the expected output as [EXPECTED OUTPUT] and describe the steps to ensure that the formula returns the correct results. Provide a detailed testing process that includes examples of inputs and the corresponding outputs for verification.

Validate the following COUNTIF formula: [FORMULA] by comparing its results against the known data in the range [RANGE]. Ensure that the count matches the expected value of [EXPECTED COUNT] by detailing the steps to check the formula's accuracy, including any necessary adjustments or troubleshooting tips to resolve discrepancies.